Help with RSS Feed and SQL query

[Eyals]

Hello,

 

I've installed Kenny Boyd's contribution that generates XML page for a price compare search engine, everything works fine but I have one function missing:

 

I have some duplicated products in more than 1 category but only the parent catgory is showing in the XML output. My question is:

 

How can I list the same product when it's linked to more than 1 category,the product should be listed twice in the xlm outpot while only the "category" value should be different..

 

The code:

 

<?php
require('includes/application_top.php');

echo '<?xml version="1.0" encoding="iso-8859-8" ?>';
echo "<VOLT_ITEMS>";
/*
OSCommerce Product XML Feed
by Kenny Boyd ([email protected])
http://www.theinternetfoundry.com

This hack will provide a basic xml feed which can be polled by
applications, affiliate websites or systems using other languages
such as C++, Perl, .NET etc to display your product line in xml
readable format.

It is rehash of the excellent Catalog Anywhere addon programmed by:
Patrick Veverka ([email protected]) available at:
http://www.oscommerce.com/community/contributions,294

This is a real quick and dirty xml implementation so bugs may exist

Please see the README.TXT before you use this

*/
////////////////////////////////////////////
// THE FOLLOWING OPTIONS MUST BE CONFIGURED!

// 1. Configure DB Connection
$db_server = "*****"; //database server
$db_user = "*****"; //database user
$db_pass = "*****"; //database password
$db_name = "*****"; //database name

// 2. Configure Site Info
//Enter the url to your website
//don't use any slashes or http eg: www.mysite.com
$domain_name = "www.volt.co.il";

//Enter the directory name of your catalog
//don't use any slashes  eg: catalog
$catalog_folder_name = "store/"; //no slashes

// 3. Configure XML Data to be included

// 3A: Include product prices? (1 is on, 0 is off)
$product_price = "1";

// 3B: include link to product_info.php at your website? (1 is on, 0 is off)
$product_info_link = "1";

// END CONFIGURATION
////////////////////////////////////////////

// create connection
$connection = mysql_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD)	or die("Couldn't make connection.");
// select database
$db = mysql_select_db(DB_DATABASE, $connection) or die("Couldn't select database.");
// create SQL statement
$sqlcount = "SELECT * FROM `products` ORDER BY 'products_id'";
$sql = "SELECT * FROM `products` ORDER BY 'products_id'";
// execute SQL query and get result
$sql_result = mysql_query($sql,$connection) or die("Couldn't execute query.");
$sqlcount_result = mysql_query($sqlcount,$connection) or die("Couldn't execute query.");
$num = mysql_numrows($sqlcount_result);
mysql_free_result($sqlcount_result);

// format results by row
while ($row = mysql_fetch_array($sql_result)) {
  $id = $row["products_id"];
  $model = $row["products_model"];
  $image = $row["products_image"];
  $price = $row["products_price"] * 1.17;
  $ship = $row["products_ship_price"];
  $sql2 = "SELECT `products_name` FROM `products_description` WHERE products_id = '$id' LIMIT 1";    $sql2_result = mysql_query($sql2,$connection) or die("Couldn't execute query.");
  $sql2_result = mysql_query($sql2,$connection) or die("Couldn't execute query.");
  $row2 = mysql_fetch_array($sql2_result);
  $name = $row2["0"];
// add extra data here then add the xml tag below

  $sql3 = "SELECT `categories_id` FROM `products_to_categories` WHERE products_id = '$id' LIMIT 1";
  $sql3_result = mysql_query($sql3,$connection) or die("Couldn't execute query 3.");
  $row3 = mysql_fetch_array($sql3_result);
  $catid = $row3["0"];
  $sql4 = "SELECT `categories_name` FROM `categories_description` WHERE categories_id = '$catid' LIMIT 1";
  $sql4_result = mysql_query($sql4,$connection) or die("Couldn't execute query 4.");
  $row4 = mysql_fetch_array($sql4_result);
// Manufacture id by Eyal Shoabi

  $sql5 = "SELECT `manufacturers_id` FROM `products` WHERE products_id = '$id' LIMIT 1";
  $sql5_result = mysql_query($sql5,$connection) or die("Couldn't execute query 5.");
  $row5 = mysql_fetch_array($sql5_result);
  $manid = $row5["0"];
  $sql6 = "SELECT `manufacturers_name` FROM `manufacturers` WHERE manufacturers_id = '$manid' LIMIT 1";
  $sql6_result = mysql_query($sql6,$connection) or die("Couldn't execute query 6.");
  $row6 = mysql_fetch_array($sql6_result);
  
  $catname = $row4["0"];
  $manname = $row6["0"];
  $products_url =  $row2["4"];
  $products_description =  $row2["5"];
// add extra data here

?>
<PRODUCT ITEM='<?php echo $id; ?>'>
<CATEGORY>"<?php echo $catname; ?>"</CATEGORY>
<NAME>"<?php echo $name;?>"</NAME>
<MANUFACTURE>"<?php echo $manname;?>"</MANUFACTURE>
<MODEL>"<?php echo $model;?>"</MODEL>
<IMAGE>"http://www.volt.co.il/store/images/<?php echo $image; ?>"</IMAGE>
<?php
if ($product_price == 1) {
?>
<PRICE>"<?php printf("%.2f", $price);  ?>"</PRICE>
<SHIP_PRICE>"<?php printf("%.2f", $ship);  ?>"</SHIP_PRICE>
<?php
}
if ($product_info_link == 1) {
?>
<PRODUCT_URL>"http://<?php echo $domain_name; ?>/<?php echo $catalog_folder_name; ?>product_info.php?products_id=<?php echo $id; ?>"</PRODUCT_URL>
<?php
}
?>
</PRODUCT>
<?php
}
// free resources and close connection
mysql_free_result($sql_result);
mysql_close($connection);
?>
</VOLT_ITEMS>

 

Any help would be appriciated!

 

Thank you.

[Eyals]

Com on guyz, I really need help with this and I'm stuck, I know I should make the main query by category_to_products but I can't get it to work.

 

Anyone?

 

Thanks.

[Rob123]

With all due respect to Kenny Boyd's work, modifying his contribution would be extremely difficult.

It would be faster (and IMO more efficient) to build a rss feed from scratch.

The problem is that there are way too many "mini-queries" (a term one of my database admins calls it)

 

There are many rss contributions and perhaps one of them does what you want. (I do not want to reinvent the wheel.)

If you can't find an appropiate contribution, PM me or post it in this thread and I'll work with you.

I am willing to do it for you and submit it as a contribution.

 

Just call it my good deed for the day.

 

Robert

[Eyals]

With all due respect to Kenny Boyd's work, modifying his contribution would be extremely difficult.

It would be faster (and IMO more efficient) to build a rss feed from scratch.

The problem is that there are way too many "mini-queries" (a term one of my database admins calls it)

 

There are many rss contributions and perhaps one of them does what you want. (I do not want to reinvent the wheel.)

If you can't find an appropiate contribution, PM me or post it in this thread and I'll work with you.

I am willing to do it for you and submit it as a contribution.

 

Just call it my good deed for the day.

 

Robert

<{POST_SNAPBACK}>

 

Robert,

 

Thank you for the good willing, Kenny's contribution is just what I need except the sorting by categories.

 

I think there is need for this type of contribution, I've searched and haven't found one that is similar to Kenny's contribution an does what I need. If you will be able to raise this contribution I'd appreciate it and it will be definitely a good deed for me... :D

 

If I had the experience I'd do that myself, but unfortunaly, I can't.

 

Thanks,

Eyal.