bestprice variables

<?php
$absolute = '/home/bodypro/public_html/';
require_once($absolute . 'wp-config.php');
$connection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    exit();
}

$start_from = 0;
$batch_size = 50;

$xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<webstore>
    <date>[todays_date({Backorders})]</date>
    <products>
        <!-- BEGIN LOOP -->
        <!-- END LOOP -->
    </products>
</webstore>";

$firstiteration = 50;

while (true) {
    // Retrieve data from SQL query
    $sql = "SELECT
        t2.meta_value as mpn,
        t1.ID as productId,
        t1.post_title as name,
        t1.guid as link,
        (SELECT wp_posts.guid
        FROM wp_posts
        INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.meta_value
        WHERE wp_posts.post_type = 'attachment'
        AND wp_postmeta.meta_key = '_thumbnail_id'
        AND wp_postmeta.post_id = t1.post_parent
        ) AS img_url_1,
        (SELECT TRIM(TRAILING '|' FROM IFNULL(REPLACE(GROUP_CONCAT(wp_postmeta.meta_value), ',', '|'), ''))
            FROM wp_postmeta
            WHERE wp_postmeta.post_id = t1.post_parent
            AND wp_postmeta.meta_key LIKE '%\_gallery%'
        ) AS additionalimage,
        ( SELECT CONCAT_WS(' > ', wp_terms4.name, wp_terms3.name, wp_terms2.name, wp_terms1.name) AS breadcrumb
            FROM wp_term_relationships
            INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id AND wp_term_taxonomy.taxonomy = 'product_cat'
            INNER JOIN wp_terms wp_terms1 ON wp_terms1.term_id = wp_term_taxonomy.term_id
            LEFT JOIN wp_term_taxonomy wp_term_taxonomy2 ON wp_term_taxonomy2.term_taxonomy_id = wp_term_taxonomy.parent
            LEFT JOIN wp_terms wp_terms2 ON wp_terms2.term_id = wp_term_taxonomy2.term_id
            LEFT JOIN wp_term_taxonomy wp_term_taxonomy3 ON wp_term_taxonomy3.term_taxonomy_id = wp_term_taxonomy2.parent
            LEFT JOIN wp_terms wp_terms3 ON wp_terms3.term_id = wp_term_taxonomy3.term_id
            LEFT JOIN wp_term_taxonomy wp_term_taxonomy4 ON wp_term_taxonomy4.term_taxonomy_id = wp_term_taxonomy3.parent
            LEFT JOIN wp_terms wp_terms4 ON wp_terms4.term_id = wp_term_taxonomy4.term_id
            WHERE wp_term_relationships.object_id = t1.post_parent
            AND (wp_terms4.name IS NOT NULL OR wp_terms3.name IS NOT NULL OR wp_terms2.name IS NOT NULL OR wp_term_taxonomy.parent = 0)
            ORDER BY wp_term_taxonomy4.term_taxonomy_id DESC, wp_term_taxonomy3.term_taxonomy_id DESC, wp_term_taxonomy2.term_taxonomy_id DESC, wp_term_taxonomy.term_taxonomy_id DESC
            LIMIT 1
        ) AS categoryPath,
        t4.meta_value as price,
        t3.post_content as description,
        REPLACE(REPLACE(t5.meta_value, 'instock', 'Y'), 'outofstock', 'N') as instock,
        REPLACE(REPLACE(t5.meta_value, 'instock', 'Παράδοση σε 3–9 ημέρες'), 'outofstock', 'Μη διαθέσιμο') as availability,
        (SELECT CONCAT(
            wp_postmeta2.meta_value,
            ' x ',
            wp_postmeta3.meta_value,
            ' x ',
            wp_postmeta1.meta_value
            ) AS dimensions
            FROM wp_postmeta wp_postmeta1
            INNER JOIN wp_posts ON wp_postmeta1.post_id = wp_posts.ID
            LEFT JOIN wp_postmeta wp_postmeta2 ON wp_postmeta2.post_id = wp_posts.ID AND wp_postmeta2.meta_key = '_length'
            LEFT JOIN wp_postmeta wp_postmeta3 ON wp_postmeta3.post_id = wp_posts.ID AND wp_postmeta3.meta_key = '_width'
            WHERE wp_postmeta1.meta_key = '_height'
            AND wp_posts.ID = t1.ID
        ) as size,
        (SELECT wp_terms.name
            FROM wp_terms
            LEFT JOIN wp_wc_product_attributes_lookup ON wp_terms.term_id = wp_wc_product_attributes_lookup.term_id
            WHERE wp_wc_product_attributes_lookup.taxonomy = 'pa_marka'
            AND wp_wc_product_attributes_lookup.product_id = t1.post_parent
        ) as manufacturer,
        t6.meta_value as weight,
        (SELECT wp_terms.name
            FROM wp_terms
            LEFT JOIN wp_wc_product_attributes_lookup ON wp_terms.term_id = wp_wc_product_attributes_lookup.term_id
            WHERE wp_wc_product_attributes_lookup.taxonomy = 'pa_color'
            AND wp_wc_product_attributes_lookup.product_id = t1.post_parent
        ) as color
        FROM
            wp_posts as t1
            INNER JOIN wp_postmeta as t2 ON t1.post_parent = t2.post_id AND t2.meta_key = '_sku'
            INNER JOIN wp_postmeta as t4 ON t1.post_parent = t4.post_id AND t4.meta_key = '_price'
            INNER JOIN wp_postmeta as t5 ON t1.post_parent = t5.post_id AND t5.meta_key = '_stock_status'
            INNER JOIN wp_postmeta as t6 ON t1.post_parent = t6.post_id AND t6.meta_key = '_weight'
            INNER JOIN wp_posts as t3 ON t1.post_parent = t3.ID
        WHERE
            t1.post_type = 'product_variation'
            AND t1.post_status = 'publish'
            AND t1.post_parent NOT IN (
                SELECT tr.object_id
                FROM wp_term_relationships AS tr
                INNER JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                WHERE tt.taxonomy = 'product_cat' AND tr.term_taxonomy_id IN (3949, 6117, 6140, 6098, 6078, 6137, 6113, 6128, 6152, 6074, 6122, 3943, 3757, 4982, 3907, 3886, 3732, 3768, 6078, 3733, 6137, 3812, 3868, 6113, 3890, 684, 6128, 3741, 6152, 6074, 6122, 3755, 1259, 3912)
            )
            LIMIT $start_from, $batch_size";

    $result = mysqli_query($connection, $sql);

    if (!$result) {
        echo "Error: " . mysqli_error($connection);
        exit();
    }

    $products = '';
    while ($row = mysqli_fetch_assoc($result)) {
        $product = "<product>
                        <mpn><![CDATA[" . $row['mpn'] . "]]></mpn>
                        <productId><![CDATA[" . $row['productId'] . "]]></productId>
                        <name><![CDATA[" . $row['name'] . "]]></name>
                        <link><![CDATA[" . $row['link'] . "]]></link>
                        <img_url_1><![CDATA[" . $row['img_url_1'] . "]]></img_url_1>
                        "./*<additionalimage><![CDATA[" . str_replace('|', ']]></additionalimage><additionalimage><![CDATA[', $row['additionalimage']) . "]]></additionalimage>*/"
                        <categoryPath><![CDATA[" . $row['categoryPath'] . "]]></categoryPath>
                        <price><![CDATA[" . $row['price'] . "]]></price>
                        <description><![CDATA[" . $row['description'] . "]]></description>
                        <instock><![CDATA[" . $row['instock'] . "]]></instock>
                        <availability><![CDATA[" . $row['availability'] . "]]></availability>
                        <size><![CDATA[" . $row['size'] . "]]></size>
                        <manufacturer><![CDATA[" . $row['manufacturer'] . "]]></manufacturer>
                        <weight><![CDATA[" . $row['weight'] . " kg]]></weight>
                        <color><![CDATA[" . $row['color'] . "]]></color>
                        <features>
                            <brand><![CDATA[" . $row['manufacturer'] . "]]></brand>
                            <color><![CDATA[" . $row['color'] . "]]></color>
                            <material></material>
                        </features>
                    </product>";

        $products .= $product;
    }

    // Replace the placeholder with the product elements
    //$xml = str_replace('<!-- END LOOP -->', $products, $xml);
    $xml = str_replace('<!-- END LOOP -->', $products . PHP_EOL . '        <!-- END LOOP -->', $xml);

    // Save the modified XML to the file
    file_put_contents($absolute . 'API/bestprice/variables.xml', $xml);

    $start_from += $batch_size;

    if ($result->num_rows < $batch_size) {
        break;
    }
    $output = "Totally added " . $firstiteration . " child products to xml" . PHP_EOL;
    fwrite(STDOUT, $output);
    $firstiteration += 50;
}

mysqli_close($connection);

copy($absolute . 'API/bestprice/variables.xml', $absolute . 'API/bestprice/bestprice2.xml');
unlink($absolute . 'API/bestprice/variables.xml');

echo "done!!!";