Hi David,
Is it possible to make an export only for one merchant with comparing products from others in csv? Based on sku ( ean )
merchant "a" want to know if his products are the cheapest.
Thx
Henk
Hi David,
It doesn't give any output, is it also possible to combine it with this one:
<?php
require("includes/common.php");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=products.csv");
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE merchant='example'";
print "Merchant;Name;Category;Sku;subcat;subcat2;Brand;Price\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["category"].";";
print $row["sku"].";";
print $row["subcat"].";";
print $row["subcat2"].";";
print $row["brand"].";";
print $row["price"]."\n";
}
?>
Then with row minimum price ( prices if it is possible )
And row from merchant ?
Thx
Henk
Hello Henk,
Sure (i'd used `ean` in the original version looks like that should have been `sku`) have a go with:
<?php
require("includes/common.php");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=products.csv");
print "Merchant;Name;Category;Sku;subcat;subcat2;Brand;Price,MinPrice\n";
$link1 = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,TRUE);
mysql_select_db($config_databaseName,$link1);
$link2 = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,TRUE);
mysql_select_db($config_databaseName,$link2);
$sql1 = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE merchant='example'";
$result1 = mysql_unbuffered_query($sql1,$link1);
while($row = mysql_fetch_array($result1,MYSQL_ASSOC))
{
$sql2 = "SELECT MIN(price) AS minPrice FROM `".$config_databaseTablePrefix."products` WHERE sku='".database_safe($row["sku"])."'";
$result2 = mysql_query($sql2,$link2);
$row2 = mysql_fetch_array($result2,MYSQL_ASSOC);
print $row["merchant"].";";
print $row["name"].";";
print $row["category"].";";
print $row["sku"].";";
print $row["subcat"].";";
print $row["subcat2"].";";
print $row["brand"].";";
print $row["price"].";";
print $row2["minPrice"]."\n";
}
?>
Cheers,
David.
--
PriceTapestry.com
Hi David,
This is the result:
Merchant Name Category Sku subcat subcat2 Brand Price,MinPrice
Empty csv file?
Henk
Sorry Henk i'd missed one replacement of $sql to $sql1 - corrected above. Don't forget to set the merchant name required at 10...
Cheers,
David.
--
PriceTapestry.com
Almost:)
Now i get one row:
Merchant;Name;Category;Sku;subcat;subcat2;Brand;Price,MinPrice Yourgamezone;Super Smash Bros Brawl;Nintendo WII;0045496900397;;;;44.00;39.95
Thx
Henk
Hi Henk,
The new_link flag (should be TRUE) was missing from the mysql_connect() calls so only a single database connection was open - corrected above - also as follows:
<?php
require("includes/common.php");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=products.csv");
print "Merchant;Name;Category;Sku;subcat;subcat2;Brand;Price,MinPrice\n";
$link1 = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,TRUE);
mysql_select_db($config_databaseName,$link1);
$link2 = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,TRUE);
mysql_select_db($config_databaseName,$link2);
$sql1 = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE merchant='example'";
$result1 = mysql_unbuffered_query($sql1,$link1);
while($row = mysql_fetch_array($result1,MYSQL_ASSOC))
{
$sql2 = "SELECT MIN(price) AS minPrice FROM `".$config_databaseTablePrefix."products` WHERE sku='".database_safe($row["sku"])."'";
$result2 = mysql_query($sql2,$link2);
$row2 = mysql_fetch_array($result2,MYSQL_ASSOC);
print $row["merchant"].";";
print $row["name"].";";
print $row["category"].";";
print $row["sku"].";";
print $row["subcat"].";";
print $row["subcat2"].";";
print $row["brand"].";";
print $row["price"].";";
print $row2["minPrice"]."\n";
}
?>
Cheers,
David.
--
PriceTapestry.com
Hi David,
Sorry, it gives the result only on the browser page.
Thx
Henk
Hi Henk,
Do you mean it's not offering result for download (content-type headers all look in place) or are you trying to request the feed in another way? Can you let me know the non-browser method you are using I will look at the request and try to see why that is...
The script must run from the top level Price Tapestry installation folder - if you wanted to actually run the script from the script/ folder then line 2 would need to be updated as follows:
require("../includes/common.php");Let me know if you're still not sure of course...
Cheers,
David.
--
PriceTapestry.com
Hi David,
It's very strange, its on top of install:
But this is the result in browser: ( first row )
Merchant;Name;Category;Sku;subcat;subcat2;Brand;Price;MinPrice Yourgamezone;Super Smash Bros Brawl;Nintendo WII;0045496900397;;;;44.00;39.95
The other script is going well.
Thx
Henk
Hi Henk,
Please could you email me your current script, and link to where it is installed and I'll check it out / follow up by email..
Thanks,
David.
--
PriceTapestry.com
How do you add the sitemap or product deeplink from the pricetapestry site to the export?
Hi Rocket32,
It would depend on the script you're using as there are various export scripts covering various requirements above and in other threads, but in all cases, there is a loop generating each row, which each product record in either a $row or $product array variable.
So after adding the required column heading to the header row, it's a case of adding the code to print the value for each record. Assuming, as per the last example above the record is in $row, to print the product page URL of that product on your site, you could use:
print "http://".$_SERVER["HTTP_HOST"].tapestry_productHREF($row)."\n";...or for the merchant's deep link (your affiliate URL), it might be:
print $row["buy_url"]."\n";The above examples follow the field with new line (\n) so if adding this as the last field, don't forget to make the corresponding changes on the line above...
If you're not sure, post the code you're currently using, and let me know which URL you would like to export and I'll show how to modify for you...
Cheers,
David.
--
PriceTapestry.com
That works fine. On some exports the data in some columns is mixed up. Is it possible to export the following script in tab or pipe separated values.
<?php
require("includes/common.php");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=products.csv");
print "Merchant;Name;Category;Sku;subcat;subcat2;Brand;Price,MinPrice\n";
$link1 = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,TRUE);
mysql_select_db($config_databaseName,$link1);
$link2 = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,TRUE);
mysql_select_db($config_databaseName,$link2);
$sql1 = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE merchant='example'";
$result1 = mysql_unbuffered_query($sql1,$link1);
while($row = mysql_fetch_array($result1,MYSQL_ASSOC))
{
$sql2 = "SELECT MIN(price) AS minPrice FROM `".$config_databaseTablePrefix."products` WHERE sku='".database_safe($row["sku"])."'";
$result2 = mysql_query($sql2,$link2);
$row2 = mysql_fetch_array($result2,MYSQL_ASSOC);
print $row["merchant"].";";
print $row["name"].";";
print $row["category"].";";
print $row["sku"].";";
print $row["subcat"].";";
print $row["subcat2"].";";
print $row["brand"].";";
print $row["price"].";";
print $row2["minPrice"]."\n";
}
?>
Hi Rocket,
Sure - The following will use tab separated fields, with a loop added to replace any tabs in fields with spaces which should be perfectly safe...
<?php
require("includes/common.php");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=products.csv");
print "Merchant\tName\tCategory\tSku\tsubcat\tsubcat2\tBrand\tPrice\tMinPrice\n";
$link1 = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,TRUE);
mysql_select_db($config_databaseName,$link1);
$link2 = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,TRUE);
mysql_select_db($config_databaseName,$link2);
$sql1 = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE merchant='example'";
$result1 = mysql_unbuffered_query($sql1,$link1);
while($row = mysql_fetch_array($result1,MYSQL_ASSOC))
{
$sql2 = "SELECT MIN(price) AS minPrice FROM `".$config_databaseTablePrefix."products` WHERE sku='".database_safe($row["sku"])."'";
$result2 = mysql_query($sql2,$link2);
$row2 = mysql_fetch_array($result2,MYSQL_ASSOC);
foreach($row as $k => $v)
{
$row[$k] = str_replace("\t"," ",$v);
}
print $row["merchant"]."\t";
print $row["name"]."\t";
print $row["category"]."\t";
print $row["sku"]."\t";
print $row["subcat"]."\t";
print $row["subcat2"]."\t";
print $row["brand"]."\t";
print $row["price"]."\t";
print $row2["minPrice"]."\n";
}
?>
Cheers,
David.
--
PriceTapestry.com
Hi Henk,
You could do something like this, which will give you for $exportMerchant (set at line 2)
product_name,their_price,cheapest_price
<?php$exportMerchant = "Merchant Name";
require("includes/common.php");
header("Content-Type: text/plain");
header("Content-Disposition: attachment; filename=products.csv");
$sql1 = "SELECT name,ean,price FROM `".$config_databaseTablePrefix."products` WHERE merchant='".database_safe($exportMerchant)."'";
database_querySelect($sql,$rows1);
foreach($rows1 as $row1)
{
$sql2 = "SELECT MIN(price) AS minPrice FROM `".$config_databaseTablePrefix."products` WHERE ean='".database_safe($row1["ean"])."'";
database_querySelect($sql2,$rows2);
print $row1["name"].",".$row1["price"].",".$rows2[0]["minPrice"]."\n";
}
exit();
?>
Cheers!
David.
--
PriceTapestry.com