Page 2 of 2

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

Posted: Thu Sep 06, 2012 2:10 am
by simonmlewis
I am using $row->id in my code now, not products.id.

Hence why I am still very confused.

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

Posted: Thu Sep 06, 2012 2:15 am
by simonmlewis
This produces everything, and duplicates the manufacturer details because many are used more than once in Products.

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>";
    }
Changing it to this, causes an error, even though the field names are correct:

Code: Select all

 $result = mysql_query ("SELECT id, manufacturer, image FROM manufacturers INNER JOIN products ON manufacturers.id=products.manid");
It's the error I reported near the start.
Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in C:\xampp\phpMyAdmin\site\includes\sell.inc on line 47

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

Posted: Thu Sep 06, 2012 2:23 am
by simonmlewis
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.

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

Posted: Thu Sep 06, 2012 10:46 am
by mikosiko
Califdon was correct... you are not "listening/reading" was has been told to you.
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");
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.

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);
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.

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

Posted: Mon Sep 10, 2012 1:04 pm
by simonmlewis
"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");

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

Posted: Tue Sep 11, 2012 4:25 am
by simonmlewis
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.

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());
I've tried this, renaming the tables for use which I read about.

Cannot see how to do it.