I am using $row->id in my code now, not products.id.
Hence why I am still very confused.
INNER JOIN with manufacturer - What am I doing wrong?
Moderator: General Moderators
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: INNER JOIN with manufacturer - What am I doing wrong?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: INNER JOIN with manufacturer - What am I doing wrong?
This produces everything, and duplicates the manufacturer details because many are used more than once in Products.
Changing it to this, causes an error, even though the field names are correct:
It's the error I reported near the start.
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
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: INNER JOIN with manufacturer - What am I doing wrong?
Sorry meant to add that the one that works has those two issues:
1) echos manufacturers image and name ($row->manufacturer), but uses Products' $row->id.
2) if I add GROUP BY, it fails.
1) echos manufacturers image and name ($row->manufacturer), but uses Products' $row->id.
2) if I add GROUP BY, it fails.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: INNER JOIN with manufacturer - What am I doing wrong?
Califdon was correct... you are not "listening/reading" was has been told to you.
that select should be:
and again... as califdon as told you... you don't need to use GROUP BY if you are not using an aggregation function, otherwise the results will have no meaning at all.
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");
that select should be:
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);-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: INNER JOIN with manufacturer - What am I doing wrong?
"you don't need to use GROUP BY if you are not using an aggregation function, otherwise the results will have no meaning at all."
Pardon?
I have no idea what you mean; only that GROUP BY or DISTINCT is the only way to produce just one of each result when there are many.
As it happens, this *with GROUP BY* works perfectly.
$result = mysql_query ("SELECT manufacturers.id, manufacturers.manufacturer, manufacturers.image FROM manufacturers INNER JOIN products ON manufacturers.id=products.manid GROUP BY manufacturers.id ORDER BY manufacturers.manufacturer");
Pardon?
I have no idea what you mean; only that GROUP BY or DISTINCT is the only way to produce just one of each result when there are many.
As it happens, this *with GROUP BY* works perfectly.
$result = mysql_query ("SELECT manufacturers.id, manufacturers.manufacturer, manufacturers.image FROM manufacturers INNER JOIN products ON manufacturers.id=products.manid GROUP BY manufacturers.id ORDER BY manufacturers.manufacturer");
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: INNER JOIN with manufacturer - What am I doing wrong?
This is a similar question: I need to know if I DO want to extract values from BOTH tables, how do I select them?
Obviously I cannot SELECT *, but I have also tried it as SELECT usercomments.id, products.id..... for example.
I've tried this, renaming the tables for use which I read about.
Cannot see how to do it.
Obviously I cannot SELECT *, but I have also tried it as SELECT usercomments.id, products.id..... for example.
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());Cannot see how to do it.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.