PHP Mysql join problems...

[quasiman]

I can't see what I'm doing wrong...and the error isn't very helpful either.

 

I'm trying to write an xml file that pulls data from two tables.  One table is products, the other categories.  I believe the problem is my sql query, but here's what I'm doing:

 

Here's the error:

[07-Apr-2009 21:05:56] DB Error: Column 'pn_cat_id' in field list is ambiguous

 

And here's the code:

<?php
header("Content-type: text/xml");

$host = "...";
$user = "...";
$pass = "...";
$database = "...";

$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $linkID) or die("Could not find database.");
	/*i.pn_cid is the ID of the categories table  */
$query = "SELECT * FROM cart_items i, cart_categories c ".
		"WHERE i.pn_cid=c.pn_cid";
$resultID = mysql_query($query, $linkID) or die("Data not found.");

$xml_output = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n";
$xml_output .= "<rss version =\"2.0\" xmlns:g=\"http://base.google.com/ns/1.0\">";
$xml_output .= "<channel>\n";
$xml_output .= "<title>Products feed.</title>\n";
$xml_output .= "<description>Products available at my site</description>\n";
$xml_output .= "<link>http://www.mysite.com</link>\n";

for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){
    $row = mysql_fetch_assoc($resultID);
    $xml_output .= "\t<item>\n";
    $xml_output .= "\t\t<guid>" . $row['i.pn_pid'] . "</guid>\n";
        // Escaping illegal characters
        $row['i.pn_name'] = str_replace("&", "&", $row['i.pn_name']);
	/* Escaping all the fields...not needed for this*/
$xml_output .= "\t\t<title>" . $row['i.pn_name'] . "</title>\n";
$xml_output .= "\t\t<description>" . $row['i.pn_desc'] . "</description>\n";
$xml_output .= "\t\t<g:product_type>" . $row['c.pn_name'] . "</g:product_type>\n";	
    $xml_output .= "\t</item>\n";
}

$xml_output .= "</channel>";

$filenamepath = "googleproducts.xml";

$fp = fopen($filenamepath,'w');

            $write = fwrite($fp,$xml_output);

echo $xml_output;  

?>

[revraz]

The error means you have to fields named pn_cat_id and you didn't specify which table it is in.

[Maq]

Try:

 

$query = "SELECT t.*, c.* FROM cart_items i, cart_categories c ".
         "WHERE i.pn_cid=c.pn_cid";