Page 1 of 2

INNER JOIN with manufacturer - What am I doing wrong?

Posted: Tue Sep 04, 2012 10:54 am
by simonmlewis

Code: Select all

$count = 0;
 $result = mysql_query ("SELECT * FROM manufacturers INNER JOIN products ON manufacturers.id=products.manid GROUP BY manufacturer.id");
  while ($row = mysql_fetch_object($result))
  {  
  $count = $count + 1;            
    echo "<div class='manufacturersbox'><a href='/sell/$row->products.id/'>";
    if ($row->manufacturers.image == NULL) { echo "<img src='/images/photounavailable.jpg' width='110px' border='0' />";}
    else { echo "<img src='/images/manufacturers/$row->manufacturers.image' border='0' />";}
         
    echo "<br/><div class='searchresultsbox_manufacturer'><a href='/sell/$row->products.id/'>$row->manufacturer</a></div></div>";
    }
    if ($count == "6" || $count == "12" || $count == "18" || $count == "24" || $count == "30")
      { echo "<div style='clear: both;' /><br/><hr noshade size='1' color='#cccccc' style='width: 950px'/>";}
}
This is producing an error on "while ($row = mysql_fetch_object($result))".

I tried it without the GROUP BY, and it just produced all the product NAMES, not just DISTINCT (or Grouped by) manufacturer names.

Re: INNER JOIN with manufacturer - What am I doing wrong?

Posted: Tue Sep 04, 2012 7:57 pm
by califdon
simonmlewis wrote:This is producing an error on "while ($row = mysql_fetch_object($result))".
When you say that it is producing an error, but fail to show the error message, there is no way we can help you.

Re: INNER JOIN with manufacturer - What am I doing wrong?

Posted: Wed Sep 05, 2012 3:48 am
by simonmlewis
Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in C:\xampp\phpMyAdmin\site\includes\sell.inc on line 46

Re: INNER JOIN with manufacturer - What am I doing wrong?

Posted: Wed Sep 05, 2012 3:53 am
by simonmlewis
And if I do:

Code: Select all

 $result = mysql_query ("SELECT * FROM manufacturers INNER JOIN products ON manufacturers.id=products.manid");
Then it produces all the Manufacturer names from 'Products', duplicating them. Hence why I want GROUP BY manufacturers.id. Which I assumed would work!?!?!

Re: INNER JOIN with manufacturer - What am I doing wrong?

Posted: Wed Sep 05, 2012 4:37 am
by simonmlewis
Sussed it - and I assume because I am saying to SELECT all from Manufacturers... that's why $row->id just picks it from Manufacturers?

Code: Select all

 $result = mysql_query ("SELECT * FROM manufacturers INNER JOIN products ON manufacturers.id=products.manid GROUP BY manufacturers.id");
 
  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->products.id/'>$row->manufacturer</a></div></div>";

Re: INNER JOIN with manufacturer - What am I doing wrong?

Posted: Wed Sep 05, 2012 4:48 am
by simonmlewis
Hold on... how do I get the $row->id to pick the one from 'manufacturers' in the <a href tag?

It's taking it from 'products', whereas the others are coming from Manufacturers I assume because they are unique.

Re: INNER JOIN with manufacturer - What am I doing wrong?

Posted: Wed Sep 05, 2012 12:13 pm
by califdon
If you had followed good practice originally and provided for echoing mysql errors, you would have been notified that your original query wasn't valid. The proper way to do that is:

Code: Select all

$result = mysql_query ("SELECT * FROM manufacturers INNER JOIN products ON manufacturers.id=products.manid GROUP BY manufacturer.id") or die(mysql_error());
Your problem is that when you use GROUP BY, you are creating a single output record for each manufacturer.id, in this case, so you can't then ask for another value, such as products.id, because there could be several values if there were different records for the same manufacturer. GROUP BY is usually used with aggregate functions like SUM, AVG, COUNT, etc. Ref: http://www.w3schools.com/sql/sql_groupby.asp

Re: INNER JOIN with manufacturer - What am I doing wrong?

Posted: Wed Sep 05, 2012 12:58 pm
by simonmlewis
Am I?
I'm asking ONLY for manufacturer.image, manufacturer.id and manufacturer.manufacturer.
Nothing to be echoed from Products - it's purely to show all manufacturers, where there is a matching 'manid' in the products table.

Re: INNER JOIN with manufacturer - What am I doing wrong?

Posted: Wed Sep 05, 2012 1:28 pm
by califdon
simonmlewis wrote:Am I?
I'm asking ONLY for manufacturer.image, manufacturer.id and manufacturer.manufacturer.
Nothing to be echoed from Products - it's purely to show all manufacturers, where there is a matching 'manid' in the products table.
Yes, you are. When you ask for "SELECT *", you are asking for every field in your table.

Re: INNER JOIN with manufacturer - What am I doing wrong?

Posted: Wed Sep 05, 2012 1:46 pm
by simonmlewis
Yes, because I need every field from manufacturers.
So if I said "SELECT id, image, manufacturer FROM...", which is the same isn't it, as "SELECT *..", it would work?
Or are you saying, that "SELECT *" confuses the script to select everything from Manufacturers AND Products?? And it just so happens to get IMAGE frim manufacturers and ID from Products??

Re: INNER JOIN with manufacturer - What am I doing wrong?

Posted: Wed Sep 05, 2012 2:31 pm
by califdon
simonmlewis wrote:Yes, because I need every field from manufacturers.
So if I said "SELECT id, image, manufacturer FROM...", which is the same isn't it, as "SELECT *..", it would work?
Or are you saying, that "SELECT *" confuses the script to select everything from Manufacturers AND Products?? And it just so happens to get IMAGE frim manufacturers and ID from Products??
No, those are quite different. SQL is a very precise language. The asterisk means "all columns". I have no idea what you mean by "confuses the script", but it certainly doesn't mean that. And absolutely nothing in SQL or PHP "just so happens". Whatever happens is entirely due to what you coded it to do. Always. No exceptions. Never. That may be the most important fact of computer programming.

The GROUP BY clause in SQL is admittedly a tricky concept to internalize, but if you're going to use it, you need to understand what it is doing. It is, at least potentially, combining multiple rows of data in the table(s) into single rows in the result. Thus, only the columns that you are grouping by, plus any columns for which you specify an aggregate function (SUM, AVG, MAX, COUNT, etc.) make any logical sense. If you are grouping on column A, then it makes no sense for you to ask to see column B, because there is no single value for that column unless you use an aggregate function. Thus SQL won't even try, it will simply generate an error as soon as it tries to parse your query. That's why you can't use an asterisk in the SELECT statement.

Correct: SELECT a, SUM(b), AVG(c) FROM xyz GROUP BY a

Incorrect: SELECT * FROM xyz GROUP BY a

I haven't taken the time to examine what you are trying to do. It may well be that you shouldn't be using GROUP BY in the first place. If you're not trying to get aggregate data about some field(s), it's likely that you shouldn't, but I would have to take more time than I have available to determine that. If you haven't already done so, I recommend that you read the reference I gave you earlier, or similar.

Re: INNER JOIN with manufacturer - What am I doing wrong?

Posted: Wed Sep 05, 2012 2:35 pm
by simonmlewis
I simply need to know, why ID is coming from PRODUCTS and not MANUFACTURER.
The rest of it does work!

Re: INNER JOIN with manufacturer - What am I doing wrong?

Posted: Wed Sep 05, 2012 6:12 pm
by califdon
Since you continue to ignore my advice, I will let someone else try to help you.

Re: INNER JOIN with manufacturer - What am I doing wrong?

Posted: Wed Sep 05, 2012 7:43 pm
by mikosiko
simonmlewis wrote:I simply need to know, why ID is coming from PRODUCTS and not MANUFACTURER.
The rest of it does work!
if you look this line in your code the answer should be clear like fresh water for you.. or is not?...

Code: Select all

 echo "<br/><div class='searchresultsbox_manufacturer'><a href='/sell/$row->products.id/'>$row->manufacturer</a></div></div>";
your underline problem here is that evidently you don't understand how a JOIN works, neither the impact of using * in the way that you are using it... you need to read a little more about JOINS.

what results do you get if you run your select in phpmyadmin (or whatever tool that you use) ?... just looking the results of your select should make evident for you what is happening....

hint: in your select .. * means "give me ALL the column from table A, AND ALL the columns from table B from the ROWS of both tables that satisfied my JOIN condition and my WHERE clause (if exist)"
therefore if you table A has a column called ID and your table B also has a column called ID how do you identify one from the other?

Re: INNER JOIN with manufacturer - What am I doing wrong?

Posted: Wed Sep 05, 2012 8:27 pm
by Benjamin
Post your schema of the two tables and make a list of the fields you need from each one. I will show you how to write the query.