Re: INNER JOIN with manufacturer - What am I doing wrong?
Posted: Thu Sep 06, 2012 2:10 am
I am using $row->id in my code now, not products.id.
Hence why I am still very confused.
Hence why I am still very confused.
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
Code: Select all
$result = mysql_query ("SELECT * FROM manufacturers INNER JOIN products ON manufacturers.id=products.manid");
while ($row = mysql_fetch_object($result))
{
$count = $count + 1;
echo "<div class='manufacturersbox'><a href='/sell/$row->id/'>";
if ($row->image == NULL) { echo "<img src='/images/photounavailable.jpg' width='110px' border='0' />";}
else { echo "<img src='/images/manufacturers/$row->image' border='0' />";}
echo "<br/><div class='searchresultsbox_manufacturer'><a href='/sell/$row->id/'>$row->manufacturer $row->id</a></div></div>";
}Code: Select all
$result = mysql_query ("SELECT id, manufacturer, image FROM manufacturers INNER JOIN products ON manufacturers.id=products.manid");Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in C:\xampp\phpMyAdmin\site\includes\sell.inc on line 47
and obviously will fail because the SELECT contain AMBIGUOUS reference to columns that exists in BOTH tables (id), if you develop with debugging enabled you should be able see those errors easily.Changing it to this, causes an error, even though the field names are correct:
Syntax: [ Download ] [ Hide ]
$result = mysql_query ("SELECT id, manufacturer, image FROM manufacturers INNER JOIN products ON manufacturers.id=products.manid");
Code: Select all
// Separating your query in a variable allows you better debugging
// Adding the table name as prefix for the columns is necessary for all the ambiguous columns, used here in all of them for code consistency only
$sql = "SELECT manufacturers.id,
manufacturers.manufacturer,
manufacturers.image
FROM manufacturers INNER JOIN products ON manufacturers.id=products.manid";
$result = mysql_query ($sql) or die("Query Error : " . mysql_error() . " In Sql : " . $sql);Code: Select all
$comments = mysql_query ("SELECT * FROM (usercomments AS uc, products AS prod) INNER JOIN prod on uc.prodid=prod.id WHERE uc.status = 'live' AND prod.rcstock = 'in stock' ORDER BY uc.id DESC LIMIT 0, 30" )or die(mysql_error());