I would like to be able to export as csv or xml, the product database that is created by price tapestry, so that I can have a full datafeed containing all my different merchant affiliate products in the fields that price tapestry puts them in for sorting.
Is this possible?
Cool script
would there be any way of specifying merchants, and also anyway of outputing it to say a .htm file as well, and would there be a way of having the buy url changed to the product url displayed on sitemaps?
hope you understand
having tried the script above, is there anyway of getting by the 30sec timeout?
later
phil
Hi Phil,
The script above is now showing the merchant name as well.
> having tried the script above, is there anyway of getting by the 30sec timeout?
Ah good point - it may need the following code at the top:
set_time_limit(0);(i've updated the original...)
What sort of .htm output are you wanting to generate?
Cheers,
David.
Thanks for the script and Phil with regard to the html pages, have a look at 4W Webmerge.
Thanks clare for that info
in regards to the script above dave, i got this error in the file downloaded:
<br />
<b>Warning</b>: mysql_query() [<a href='function.mysql-query'>function.mysql-query</a>]: Unable to save result set in <b>/home/buy247/public_html/includes/database.php</b> on line <b>17</b><br />
<br />
<b>Warning</b>: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>/home/buy247/public_html/includes/database.php</b> on line <b>21</b><br />
<br />
<b>Warning</b>: mysql_num_rows(): supplied argument is not a valid MySQL result resource in <b>/home/buy247/public_html/includes/database.php</b> on line <b>26</b><br />TO change the Buy URL to the products URL, we use something similar to the following:
<?php
require("includes/common.php");
require("includes/database.php");
header("Content-Disposition: attachment; filename=products.xml");
header("Content-Type: text/xml");
print "<?xml version='1.0' encoding='UTF-8'?>";
print "<products>";
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products`";
if (database_querySelect($sql,$rows))
{
$sitemapBaseHREF = "".$config_baseHREF;
foreach($rows as $row)
{
print "<product>";
{
$sitemapHREF = "products.php?q=".urlencode($row["name"]);
}
print "<merchant>".$row["merchant"]."</merchant>";
print "<productname>".$row["name"]."</productname>";
print "<deeplink>".$sitemapBaseHREF.$sitemapHREF."</deeplink>";
print "<price>".$row["price"]."</price>";
print "<description>".$row["description"]."</description>";
print "<image_url>".$row["image_url"]."</image_url>";
print "<category>".$row["category"]."</category>";
print "<brand>".$row["brand"]."</brand>";
print "</product>";
}
}
print "</products>";
?>This outputs the XML file - if you are using Mod_Rewrite the code as seen in the sitemap.php file will need to be inserted when declaring "$sitemapHREF".
Hi Phil,
Ooops - that's a memory error (too many results). Therefore, it will be necessary to use MySQL's "unbuffered" query mechanism - here's an alternative version:
<?php
require("includes/common.php");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=products.csv");
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products`";
print "Merchant,Name,Description,ImageURL,BuyURL,Price,Category,Brand\n";
$link = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword);
mysql_select_db($config_databaseName,$link);
$result = mysql_unbuffered_query($sql,$link);
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
print $row["merchant"].",";
print $row["name"].",";
print $row["description"].",";
print $row["image_url"].",";
print $row["buy_url"].",";
print $row["price"].",";
print $row["category"].",";
print $row["brand"]."\n";
}
?>
Cheers,
David.
Hi Dave,
Is there away of grabbing the category and ouputing to csv file like price tapestry display categories on the fly.
so if category name begins with Hand Tools then can we export the category
in this format.
Example:
/A/Apples/
/B/Banners/
/H/Hand Tools/
Cheers
Darren
Hi Darren,
Try this:
<?php
require("includes/common.php");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=categories.csv");
$sql = "SELECT DISTINCT(category) AS category FROM `".$config_databaseTablePrefix."products` ORDER BY category";
$link = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword);
mysql_select_db($config_databaseName,$link);
$result = mysql_unbuffered_query($sql,$link);
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
$firstLetter = substr($row["category"],0,1);
print "/".$firstLetter."/".$row["category"]."/\n";
}
?>
Should be close...!
Cheers,
David.
HI Dave,
That was great worked perfect you know your stuff i am impressed every time.
also can we save this server side instead of having the browser downloading it? my database size is over 4 Million records can we save to file on the server.
Many Thanks
Again great support second to none?
Darren
Hi Darren,
The code to do that is easy enough, the only tricky part is making sure that PHP can write to the directory in which you want to export the file to. My advice is to create a folder called "export" in your Price Tapestry folder, and then make this "World Writable". The easiest way to do this is normally through your FTP program. Right-click on the folder in the remote window, and look for "Permissions..." or "Properties..." and then Permissions. You should see a way to set write access for User/Group/World - make sure it is set to World Write access.
Then, the following code should do the trick:
<?php
require("includes/common.php");
$fp = fopen("export/categories.csv","w";
if (!$fp) { print "Could not open output file, check permissions...!";exit(); }
$sql = "SELECT DISTINCT(category) AS category FROM `".$config_databaseTablePrefix."products` ORDER BY category";
$link = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword);
mysql_select_db($config_databaseName,$link);
$result = mysql_unbuffered_query($sql,$link);
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
$firstLetter = substr($row["category"],0,1);
fwrite($fp,"/".$firstLetter."/".$row["category"]."/\n");
}
fclose($fp);
?>
Cheers,
David.
David,
I would like to export the products database to XML, but due to the size of my database, currently 1.2M products, it may need to be split between several files. Can you please write me a script similar to the ones above to do this.
Thanks,
Hello Stephen,
Sure - it's very easy to split the export up; but just so I can post exactly what you want to do; can you describe exactly what sort of export you want (in particular which fields within each record in the XML, i.e. actual Buy URL or the URL to the product on your site), and how you would like the files to be split...
A sample record would be useful - you can post the XML you want to create between <code> tags on the forum...
Cheers,
David.
David,
Thanks for your reply.
I was planning to use the following MySQL query SELECT `merchant`, `name`, `description`, `image_url`, `buy_url`, `price`, `brand` FROM `products` WHERE 1. I was assuming that I would have to use the 'buy url' from the database, but if there is some way to use the product url page from my site that would be preferable.
Thanks,
Stephen
David,
Sorry, I forgot to mention that I had done a trial download, which is located at http://www.online-shopping-for.co.uk/database-backups/online-shopping-for.xml.
Kind regards
Stephen
Hi Stephen,
That's no problem. Creating the product URL on your site is easy - it's the same code that creates the link on the actual site.
Specifically; how do you want the export to be split up into multiple files? 1 per merchant perhaps? I'll make it export to /database-backups/
This is all very quite to write (based mostly on code already in this thread), I just want to make sure it will do what you want. I'll put the code together tomorrow for you...
Cheers,
David.
Hello David
Is it possible to modify this to show obly the cheapest product should there be more than 2 items with the same name?
Thanks
Mally
Hi Mally,
There's quite a bit of code in this thread so to make sure i'm working with what you've got could you perhaps email me the code you want restricting to the cheapest product and i'll take a look for you...
Cheers,
David.
Hi Dave,
Is there a way to export maybe the top 200 most expensive products from our price tapestry site's product page using the following code?
<?php
require("includes/common.php");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=products.csv");
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products`";
print "Merchant,Name,Description,ImageURL,BuyURL,Price,Category,Brand\n";
$link = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword);
mysql_select_db($config_databaseName,$link);
$result = mysql_unbuffered_query($sql,$link);
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
print $row["merchant"].",";
print $row["name"].",";
print $row["description"].",";
print $row["image_url"].",";
print $row["buy_url"].",";
print $row["price"].",";
print $row["category"].",";
print $row["brand"]."\n";
}
?>
I am trying to use only the top 200 for PPC testing.
Hi,
Sure - try modifying the SQL from:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products`";...to:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` ORDER BY price DESC LIMIT 200";Cheers,
David.
I am having problems trying to export my site's datafeed with the product url inserted instead of the buyurl. I have placed the sitemapBaseHREF code from the sitemap php into the export.php file.
Here is the code I am using
<?php
require("includes/common.php");
require("includes/database.php");
header("Content-Disposition: attachment; filename=products.xml");
header("Content-Type: text/xml");
print "<?xml version='1.0' encoding='UTF-8'?>";
print "<products>";
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products`";
if (database_querySelect($sql,$rows))
{
$sitemapBaseHREF = "http://".$_SERVER["HTTP_HOST"].$config_baseHREF;
foreach($rows as $row)
{
print "<product>";
{
$sitemapHREF = "products.php?q=".urlencode($row["name"]);
}
print "<merchant>".$row["merchant"]."</merchant>";
print "<productname>".$row["name"]."</productname>";
print "<deeplink>".$sitemapBaseHREF.$sitemapHREF."</deeplink>";
print "<price>".$row["price"]."</price>";
print "<description>".$row["description"]."</description>";
print "<image_url>".$row["image_url"]."</image_url>";
print "<category>".$row["category"]."</category>";
print "<brand>".$row["brand"]."</brand>";
print "</product>";
}
print "</products>";
}
?>I get the following error when trying to get the export:
Fatal error: Cannot redeclare database_queryselect() (previously declared in /home2/comparev/public_html/themagazinesitedot/includes/database.php:3) in /home2/comparev/public_html/themagazinesitedot/includes/database.php on line 33
Hi,
Sorry for the confusion - the code may have come from a much older post where database.php was separate. It is now included by common.php, which is why you are getting the "cannot redeclare..." error. Just removing require("includes/database.php"); should be all you need to change...
<?php
require("includes/common.php");
header("Content-Disposition: attachment; filename=products.xml");
header("Content-Type: text/xml");
print "<?xml version='1.0' encoding='UTF-8'?>";
print "<products>";
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products`";
if (database_querySelect($sql,$rows))
{
$sitemapBaseHREF = "http://".$_SERVER["HTTP_HOST"].$config_baseHREF;
foreach($rows as $row)
{
print "<product>";
{
$sitemapHREF = "products.php?q=".urlencode($row["name"]);
}
print "<merchant>".$row["merchant"]."</merchant>";
print "<productname>".$row["name"]."</productname>";
print "<deeplink>".$sitemapBaseHREF.$sitemapHREF."</deeplink>";
print "<price>".$row["price"]."</price>";
print "<description>".$row["description"]."</description>";
print "<image_url>".$row["image_url"]."</image_url>";
print "<category>".$row["category"]."</category>";
print "<brand>".$row["brand"]."</brand>";
print "</product>";
}
print "</products>";
}
?>Cheers,
David.
Hello David
Is it possible to do the export, but instead of exporting/downloading a file to be saved, it creates/updates the file into the root folder?
Cheers Mally
Hi Mally,
That's straight forward - just a case of rather than print() each line of output, just to open a file and then fwrite() instead. If you email me the current version of the export script that you are using, i'll modify it to create a file instead...
Cheers,
David.
With the above code, how can I get it to export to an export directory within the Price Tapestry folder?
Also when there are many products within the code above, is there a way to get it not to time out and export the entire feed?
Hi there,
To avoid the timeout, use set_time_limit(0); at the top. To export to a local directory, for example "export/products.xml", first create the "export" directory and then make sure that it is writable by PHP. The easiest way to do this is normally with your FTP program. Right-click on the directory name in the remote window, and then look for "Permissions" or maybe "Properties..." then "Permissions". Then give write access to all users (owner, group, world). Here's the above script with both mods included...
<?php
set_time_limit(0);
require("includes/common.php");
$fp = fopen("export/products.xml","w");
if (!$fp) die("Could not create output file, check permissions!");
fwrite($fp,"<?xml version='1.0' encoding='UTF-8'?>");
print "<products>";
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products`";
if (database_querySelect($sql,$rows))
{
$sitemapBaseHREF = "http://".$_SERVER["HTTP_HOST"].$config_baseHREF;
foreach($rows as $row)
{
fwrite($fp,"<product>";
{
$sitemapHREF = "products.php?q=".urlencode($row["name"]);
}
fwrite($fp,"<merchant>".$row["merchant"]."</merchant>");
fwrite($fp,"<productname>".$row["name"]."</productname>");
fwrite($fp,"<deeplink>".$sitemapBaseHREF.$sitemapHREF."</deeplink>");
fwrite($fp,"<price>".$row["price"]."</price>");
fwrite($fp,"<description>".$row["description"]."</description>");
fwrite($fp,"<image_url>".$row["image_url"]."</image_url>");
fwrite($fp,"<category>".$row["category"]."</category>");
fwrite($fp,"<brand>".$row["brand"]."</brand>");
fwrite($fp,"</product>");
}
fwrite($fp,"</products>");
}
print "Done.";
?>Cheers,
David.
Hi all,
I've just started with PT and finding most of my answers by stategic site: searches of the forum.
I'm experimenting with different versions of the export to CSV feed scripts and all of them create a CSV which seems to work but the contents are all jumbled up. Any advice?
Hi,
This is probably just down to adding an ORDER BY clause to the SQL that you are using in your export script. The basic script above simply selects all products with the following line:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products`";However, if you wanted the products sorted alphabetically, then you could use:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` ORDER BY name";There are various other options (e.g. price, category etc.) so if that's not what you require, let me know what ordering you're after and I'll work out the SQL for you...
Cheers,
David.
Yes - commas in the description would cause a problem with the very basic CSV export script above. Spreadsheets normally handle quote delimitation correctly, in which case you could replace each of the lines that generate the output, e.g:
print $row["description"].",";...with:
print "\"".$row["description"]."\",";Otherwise yes, XML wouldn't have the same problem - but not so easy to import to a spreadsheet of course...
Cheers,
David.
Hello David, I am wondering is there anyway to download my sites database in csv instead of xml with the following script. I tried the xml2csv from the magic parser site. But it does not work with some of the xml downloads. I use the following;
<?php
set_time_limit(0);
require("includes/common.php");
$fp = fopen("export/products.xml","w");
if (!$fp) die("Could not create output file, check permissions!");
fwrite($fp,"<?xml version='1.0' encoding='UTF-8'");
print "<products>";
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products`";
if (database_querySelect($sql,$rows))
{
$sitemapBaseHREF = "http://".$_SERVER["HTTP_HOST"].$config_baseHREF;
foreach($rows as $row)
{
fwrite($fp,"<product>";
{
$sitemapHREF = "products.php?q=".urlencode($row["name"]);
}
fwrite($fp,"<merchant>".$row["merchant"]."</merchant>");
fwrite($fp,"<productname>".$row["name"]."</productname>");
fwrite($fp,"<deeplink>".$sitemapBaseHREF.$sitemapHREF."</deeplink>");
fwrite($fp,"<price>".$row["price"]."</price>");
fwrite($fp,"<description>".$row["description"]."</description>");
fwrite($fp,"<image_url>".$row["image_url"]."</image_url>");
fwrite($fp,"<category>".$row["category"]."</category>");
fwrite($fp,"<brand>".$row["brand"]."</brand>");
fwrite($fp,"</product>");
}
fwrite($fp,"</products>");
}
print "Done.";
?>
Basically I am trying to create a datafeed of my Price Tapestry site with links to the site in csv format.
Hi,
Sure - the first comment in this thread is actually a CSV version rather than XML - that should do the trick...
http://www.pricetapestry.com/node/506#comment-1852
Cheers,
David.
Yes. I tried that export. It exports the original datafeeds buyurl. I would like to get the datafeed to export the pricetapestry's product url for each product in csv format. along with the name, description, image, price.
Hi,
Ah, I see - try the original script modified as follows;
<?php
set_time_limit(0);
require("includes/common.php");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=products.csv");
$sitemapBaseHREF = "http://".$_SERVER["HTTP_HOST"].$config_baseHREF;
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products`";
print "Merchant,Name,Description,ImageURL,BuyURL,Price,Category,Brand\n";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
print $row["merchant"].",";
print $row["name"].",";
print $row["description"].",";
print $row["image_url"].",";
$sitemapHREF = "products.php?q=".urlencode($row["name"]);
print $sitemapBaseHREF.$sitemapHREF.",";
print $row["price"].",";
print $row["category"].",";
print $row["brand"]."\n";
}
}
?>
Cheers,
David.
What has to be changed in order to export in xml format?
Thank You
Hi azizjon,
I'll email you an XML version of the export script...
Cheers,
David.
--
PriceTapestry.com
Hi again,
Is it possible to split the csv file and put it on the server?
export1.csv
export2.csv
etc..
Thx
Henk
Hello Henk,
Sure - here's a version to output files to an export/ folder in the same directory as the Price Tapestry installation. The above is quite an old thread so I've modified slightly to include clean URLs and to make sure that the output description does not contain commas or new line characters that would break the CSV format....
<?php
set_time_limit(0);
require("includes/common.php");
$exportDir = "export/";
$exportProductsPerFile = 100;
if (!is_writable($exportDir)) die("Cannot write to ".$exportDir." - check permissions!");
$csvSafe = array(",","\r","\n");
$baseHREF = "http://".$_SERVER["HTTP_HOST"];
$header = "Merchant,Name,Description,ImageURL,BuyURL,Price,Category,Brand\n";
$c = 0;
function openFile()
{
global $c;
global $fp;
global $exportDir;
global $header;
$c++;
$fp - fopen($exportDir."export".$c.".csv","w");
fwrite($fp,$header);
}
function closeFile()
{
global $fp;
fclose($fp);
}
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products`";
if (database_querySelect($sql,$rows))
{
openFile();
$count = 0;
foreach($rows as $row)
{
$row = "";
$row .= $row["merchant"].",";
$row .= $row["name"].",";
$row .= str_replace($csvSafe,"",$row["description"]).",";
$row .= $row["image_url"].",";
$row .= $baseHREF.tapestry_productHREF($row).",";
$row .= $row["price"].",";
$row .= $row["category"].",";
$row .= $row["brand"]."\n";
fwrite($fp,$row);
$count++;
if ($count == $exportProductsPerFile)
{
closeFile();
if ($count < count($rows)) openFile();
}
}
}
closeFile();
print "Done.";
?>
Cheers,
David.
--
PriceTapestry.com
Hi David,
I have edited the script to only get the top 10000 products.
It is working fine from the browser but i would like to cron it.
When i run it on the commandline it displays everything correct but doesn't generate the file
Any suggestions?
<?php
require("../includes/common.php");
header("Content-Disposition: attachment; filename=products-export.xml");
header("Content-Type: text/xml");
print "<?xml version='1.0' encoding='UTF-8'?>";
print "<products>";
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE ean IS NOT NULL ORDER BY price DESC LIMIT 10000";
if (database_querySelect($sql,$rows))
{
$sitemapBaseHREF = "http://www.".$_SERVER["HTTP_HOST"].$config_baseHREF;
foreach($rows as $row)
{
print "<product>";
{
$sitemapHREF = "products.php?q=".urlencode($row["name"]);
}
print "<productname>".$row["name"]."</productname>";
print "<deeplink>".$sitemapBaseHREF.$sitemapHREF."</deeplink>";
print "<price>".$row["price"]."</price>";
print "<EAN>".$row["ean"]."</EAN>";
print "<image_url>".$row["image_url"]."</image_url>";
print "<brand>".$row["brand"]."</brand>";
print "<price>".$row["price"]."</price>";
print "</product>";
}
print "</products>";
}
?>Hi Alex,
Below version will create products-export.xml in the feeds/ folder. Also;
- uses fixed value for $sitemapBaseHREF since $_SERVER[] variables not available on command line. Edit this at line 10 as required (don't forget to specify http or https correctly)
- uses tapesry_productHREF function for deep link
- uses CDATA tags around all element values to avoid breaking XML
<?php
require("../includes/common.php");
$fp = fopen($config_feedDirectory."products-export.xml","w");
fwrite($fp,"<?xml version='1.0' encoding='UTF-8'?>");
fwrite($fp,"<products>");
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE ean IS NOT NULL ORDER BY price DESC LIMIT 10000";
if (database_querySelect($sql,$rows))
{
$sitemapBaseHREF = "http://www.example.com";
foreach($rows as $row)
{
fwrite($fp,"<product>");
fwrite($fp,"<productname><![CDATA[".$row["name"]."]]></productname>");
fwrite($fp,"<deeplink><![CDATA[".$sitemapBaseHREF.tapestry_productHREF($row)."]]></deeplink>");
fwrite($fp,"<price><![CDATA[".$row["price"]."]]></price>");
fwrite($fp,"<EAN><![CDATA[".$row["ean"]."]]></EAN>");
fwrite($fp,"<image_url><![CDATA[".$row["image_url"]."]]></image_url>");
fwrite($fp,"<brand><![CDATA[".$row["brand"]."]]></brand>");
fwrite($fp,"<price><![CDATA[".$row["price"]."]]></price>");
fwrite($fp,"</product>");
}
fwrite($fp,"</products>");
}
fclose($fp);
?>Cheers,
David.
--
PriceTapestry.com
Hi Clare,
Here's a basic script to export all products as a CSV file. I've used the HTTP headers required to force download (so that your browser does not try to open it!) of a file called products.csv. Run this from the top level directory of your Price Tapestry installation:
export.php:
<?phpset_time_limit(0);
require("includes/common.php");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=products.csv");
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products`";
print "Merchant,Name,Description,ImageURL,BuyURL,Price,Category,Brand\n";
$search = array("\n","\r",",");
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
foreach($row as $k => $v)
{
$row[$k] = str_replace($search," ",$v);
}
print $row["merchant"].",";
print $row["name"].",";
print $row["description"].",";
print $row["image_url"].",";
print $row["buy_url"].",";
print $row["price"].",";
print $row["category"].",";
print $row["brand"]."\n";
}
}
?>
This can be made to do XML just as easily (let me know if you want an XML version), and you can of course change it to use any separator character just by changing the "," as required.
Hope this helps!
Cheers,
David.