Hi David,
I am trying to create an export to CSV of alle the unique products (based on "EAN").
It should contain; Name, brand, lowest price, image link (based on imagecache) and ofcourse a link.
Could you help me with this?
Best regards
Thanks for the quick reply (as always! :)
Two additional questions:
-If i only would like to export product above (for example) €50,-, what should i add to the query?
-Can i make a cronjob for this script? (because it now downloads trough the browser)
Best regards
Hi Alex,
Sure - the run the following from scripts/ folder instead of top level, and it will create the export in the feeds/ folder as export_products.csv. For the minimum price restriction, see the HAVING clause in the SQL at line 7...
<?php
set_time_limit(0);
require("../includes/common.php");
$fp = fopen($config_feedDirectory."export_products.csv","w");
$link = mysqli_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,$config_databaseName);
mysqli_set_charset($link,"utf8");
$sql = "SELECT *,MIN(price) AS minPrice FROM `".$config_databaseTablePrefix."products` GROUP BY `ean` HAVING minPrice > '50.00'";
mysqli_real_query($link,$sql);
$result = mysqli_use_result($link);
fwrite($fp,"name,brand,price,imageLink,link\n");
if (isset($_SERVER["HTTP_HOST"]))
{
$baseHREF = "http".(isset($_SERVER["HTTPS"])&&$_SERVER["HTTPS"]?"s":"")."://".$_SERVER["HTTP_HOST"];
}
else
{
$baseHREF = "http://www.example.com";
}
while($row = mysqli_fetch_assoc($result))
{
$imageLink = $baseHREF.$config_baseHREF."imageCache.php?src=".base64_encode($row["image_url"]);
$link = $baseHREF.tapestry_productHREF($row);
fwrite($fp,$row["merchant"].",");
fwrite($fp,$row["name"].",");
fwrite($fp,$row["description"].",");
fwrite($fp,$row["image_url"].",");
fwrite($fp,$row["buy_url"].",");
fwrite($fp,$row["minPrice"].",");
fwrite($fp,$imageLink.",");
fwrite($fp,$link."\n");
}
fclose($fp);
?>
For the cronjob, use the same as your cron.php command line replacing cron.php with export.php e.g.
cd /path/to/scripts/;/usr/bin/php export.php
Or alternatively, if you would like it to run immediately after cron.php as part of the same job, use for example;
cd /path/to/scripts/;/usr/bin/php cron.php;/usr/bin/php export.php
That way you wouldn't have to worry about scheduling export.php to ensure that it is never running as the same time as the import process...
Cheers,
David.
--
PriceTapestry.com
Hi Alex,
Just a follow up to note that when executing from the command line (cronjob), the site URL cannot be derived by the script and must therefore be hardcoded; so in place of;
$baseHREF = "http".(isset($_SERVER["HTTPS"])&&$_SERVER["HTTPS"]?"s":"")."://".$_SERVER["HTTP_HOST"];...now:
if (isset($_SERVER))
{
$baseHREF = "http".(isset($_SERVER["HTTPS"])&&$_SERVER["HTTPS"]?"s":"")."://".$_SERVER["HTTP_HOST"];
}
else
{
$baseHREF = "http://www.example.com";
}(corrected above, edit "http://www.example.com" as required)
Cheers,
David.
--
PriceTapestry.com
Hi David,
have just tried the script trough Cron but i get errors:
{code saved}
Hello Alex,
Regarding the unset warning that would indicate that $_SERVER is set but not $_SERVER["HTTP_HOST"] so the isset() check can use that instead - that and file mode updated above...
Cheers,
David.
--
PriceTapestry.com
Hi David,
There was a file created but it was empty.
Hi Alex,
Please could you email me the script created and I'll check it out further for you...
Thanks,
David.
--
PriceTapestry.com
Hello Alex,
Sure - have a go with something like this, saved as for example export.php (top level)
<?phpset_time_limit(0);
require("includes/common.php");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=products.csv");
$link = mysqli_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,$config_databaseName);
mysqli_set_charset($link,"utf8");
$sql = "SELECT *,MIN(price) AS minPrice FROM `".$config_databaseTablePrefix."products` GROUP BY `ean`";
mysqli_real_query($link,$sql);
$result = mysqli_use_result($link);
print "name,brand,price,imageLink,link\n";
$baseHREF = "http".(isset($_SERVER["HTTPS"])&&$_SERVER["HTTPS"]?"s":"")."://".$_SERVER["HTTP_HOST"];
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
$imageLink = $baseHREF.$config_baseHREF."imageCache.php?src=".base64_encode($row["image_url"]);
$link = $baseHREF.tapestry_productHREF($row);
print $row["name"].",";
print $row["brand"].",";
print $row["minPrice"].",";
print $imageLink.",";
print $link."\n";
}
?>
Hope this helps!
Cheers,
David.
--
PriceTapestry.com