0

I hope someone can help.

The following piece of php code (see below) is used to pull all the available products from a database and save them locally to a xml file (somefile.xml) so that google can come every day and and pick it to keep our merchant account or also known as google base up to date.

Since my inventory has grown to over 200K products, google will not accept the file any more as it is over 20 meg big and has asked me to split it in smaller files.

So I'm looking for a way to make this query save the products in multiple files, let's say create a brand new file every 10000 rows (or products) with a new file name in sequence (e.g somefile1.xml, somefile2.xml, etc etc...) while maintaining the xml file header and footer in each file.

$fp = fopen(BASE_PATH.'/somefile.xml','w');

    $db->query("
        SELECT ".DB_PREFIX."products.*, 
        WHERE ".DB_PREFIX."products.available='Yes'
        ORDER BY title
        ");

///header
$xmlPacket =
'<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:g="http://base.google.com/ns/1.0">
<channel>
<title>'.$settings["GlobalSiteName"].'</title>
<link>'.$settings["GlobalHttpUrl"].'</link>

    while($db->moveNext()){

            $product_url = $db->col["product_url"];
            $name = $db->col["title"];
            $condition = $db->col["condition"];

///products         
$xmlPacket .=
"\n<item>
  <title>".st_normalize($name)." (SKU ".st_normalize($pid).")</title>
  <link>".st_normalize($product_url)."</link>
  <g:condition>".st_normalize($condition)."</g:condition>
</item>\n";

        }

///footer   
$xmlPacket .=
'</channel>
</rss>';

fwrite($fp,$xmlPacket);
fclose($fp);

Please if someone has any idea how to accomplish this, please share any thoughts.

Thanks and have a nice day!

LuVaGu
  • 5
  • 1
  • 2

1 Answers1

1

This should do what you need:

define(BASE_NAME, 'somefile');
define(MAX_ROWS, 10000);

$db->query("SELECT ".DB_PREFIX."products.*, WHERE ".DB_PREFIX."products.available='Yes' ORDER BY title");

$header = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<rss version=\"2.0\" xmlns:g=\"http://base.google.com/ns/1.0\">\n<channel>\n<title>".$settings["GlobalSiteName"]."</title>\n<link>".$settings["GlobalHttpUrl"]."</link>";
$footer = "</channel>\n</rss>";

$n = 0;
$i = 0;
while($db->moveNext()){
    if (!($i % MAX_ROWS)) {
        if ($n) {
            fwrite($fp, $footer);
            fclose($fp);
        }
        $i = 0;
        $n++;
        $fp = fopen(BASE_PATH.'/'.BASE_NAME.$n.'.xml', 'w');
        fwrite($fp, $header);
    }

    $product_url = $db->col["product_url"];
    $name = $db->col["title"];
    $condition = $db->col["condition"];

    fwrite($fp, "\n<item>\n<title>".st_normalize($name)." (SKU ".st_normalize($pid).")</title>\n<link>".st_normalize($product_url)."</link>\n<g:condition>".st_normalize($condition)."</g:condition>\n</item>\n");
    $i++;
}

fwrite($fp, $footer);
fclose($fp);
EdoDodo
  • 8,220
  • 3
  • 24
  • 30
  • Thanks! for the quick reply EdoDodo. It makes a lot of sense. However when I run the query, I got a Warning: fwrite(): supplied argument is not a valid stream resource on line 161 which points to fwrite($fp, "\n\n etc, etc above); and also a Warning: fwrite(): and fclose(): supplied argument is not a valid stream resource in line 164 and 164 pinting to the last fwrite($fp, $footer); and fclose($fp); I double checked my code and it's OK. Any ideas why I might be getting this? – LuVaGu Jul 03 '11 at 12:15
  • Yes, I think I see what was wrong. Should be fixed now, try running the code again. – EdoDodo Jul 03 '11 at 12:41
  • Thank you you're a star, the query is running now. But it is saving 1 row with the header and footer per file until it reaches the MAX_ROW and after that it saves the last file with the rest of the rows with header and without the footer. Please advise – LuVaGu Jul 03 '11 at 13:18
  • Whoops, it looks like I'd made a mistake in the loop. Apologies. Should be fixed now, try again, please. – EdoDodo Jul 03 '11 at 13:26