Hi David,
Been tinkering with some rss format to build a rss/xml product feed from a database.
I'm getting there, but could use a little of your finese to complete it, if I may ask of you.
Here is the current code I'm at now below:
What I am wanting to do is list the product name, image, with short description (I'd prefer to limit the characters there) and price as recorded into the db.
I think the sql query is my biggest trouble here. Just point me in the right direction and I should be able to figure the rest. I get everything to show, but the image details.
Thanks,
Ray
<?php
set_time_limit(0);
require("includes/common.php");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=products.xml");
$baseHREF = "http://".$_SERVER["HTTP_HOST"].$config_baseHREF;
print "<?xml version='1.0' ?>\n";
print "<rss version='2.0'>\n";
print "<channel>\n";
print "<title>".$config_title."</title>\n";
print "<link>".$baseHREF."</link>\n";
$mainProduct = mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword);
mysql_select_db($config_databaseName,$mainProduct);
$sql = "SELECT DISTINCT(name),normalised_name FROM `".$config_databaseTablePrefix."products` LIMIT 10";
$result = mysql_unbuffered_query($sql,$mainProduct);
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
if ($config_useRewrite)
{
$link = $baseHREF . "product/".tapestry_hyphenate($row["normalised_name"]).".html";
}
else
{
$link = $baseHREF . "products.php?q=".urlencode($row["normalised_name"]);
}
print "<item>\n";
print "<title><![CDATA[".$row["name"]."]]></title>\n";
print "<link><![CDATA[".$link."]]></link>\n";
print "<image>\n";
print "<title><![CDATA[".$row["name"]."]]></title>\n";
print "<url><![CDATA[".$mainProduct["image_url"]."]]></url>\n";
print "<description><![CDATA[".$mainProduct["description"]."]]></description>\n";
print "<price><![CDATA[".$config_currencyHTML.$mainProduct["price"]."]]></price>\n";
print "<link><![CDATA[".$mainProduct["buy_url"]."]]></link>\n";
print "</image>\n";
print "</item>\n";
}
print "</channel>";
print "</rss>";
print "</xml>";
?>Hi David,
Well, after making the sql change I still get the same results in the downloaded file, here is the output of the file (minus the last 9 product listings) below:
<?xml version='1.0' ?>
<rss version='2.0'>
<channel>
<title>xxxxxxxxxx edited here</title>
<link>http://www.xxxxxxxxxx.com/</link>
<item>
<title><![CDATA["Nauti"Cal and Nice Top]]></title>
<link><![CDATA[http://www.xxxxxxxxxx.com/product/NautiCal-and-Nice-Top.html]]></link>
<image>
<title><![CDATA["Nauti"Cal and Nice Top]]></title>
<url><![CDATA[h]]></url>
<description><![CDATA[h]]></description>
<price><![CDATA[$h]]></price>
<link><![CDATA[h]]></link>
</image>
</item>
</channel>
</rss>
</xml>It appears to work fine until it gets to the <url> line then nothing from there.
Hi Ray,
Ah - I can see the problem - $mainProduct is being overwritten where the link is generated, so where you have:
if ($config_useRewrite)
{
$mainProduct = $baseHREF . "product/".tapestry_hyphenate($row["normalised_name"]).".html";
}
else
{
$mainProduct = $baseHREF . "products.php?q=".urlencode($row["normalised_name"]);
}..REPLACE with:
if ($config_useRewrite)
{
$link = $baseHREF . "product/".tapestry_hyphenate($row["normalised_name"]).".html";
}
else
{
$link = $baseHREF . "products.php?q=".urlencode($row["normalised_name"]);
}print "<link><![CDATA[".$mainProduct."]]></link>\n";...which should be:
print "<link><![CDATA[".$link."]]></link>\n";...and finally, for each of the rows where $mainProduct is being used that should actually be $row - that should be all it is...
Cheers,
David.
--
PriceTapestry.com
David,
Yes, that was all it was, thanks a bunch, now I'll have to be careful not to overload my servers using this lol.
Hi Ray,
The code is nearly there but the easiest way to get all fields without having to specify them is to GROUP BY name. Have a go with:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` GROUP BY name LIMIT 10";With that in place, all fields should be available to your output code in the loop.
To tidily truncate the description you could use Price Tapestry's built in tapestry_substr() function which truncates to the nearest space after the specified length. Have a go with:
print "<description><![CDATA[".tapestry_substr($mainProduct["description"],200)."]]></description>\n";Hope this helps!
Cheers,
David.
--
PriceTapestry.com