Page 1 of 1

INNER JOIN Query

Posted: Thu Jun 17, 2010 4:27 am
by simonmlewis
I am trying to pull up a list of "user comments" from a database table, where the comment status is LIVE, but also where in the "product" database, the item that has been commented on, the rcstock field says "in stock" and pause field says "off".

I was going to use an embedded query, but that doesn't work, so assumed an INNER JOIN would be better.

So the basics of the code would say:
Select the id, title, category, rcstock and pause from PRODUCTS and JOIN it with USERCOMMENTS where the USERCOMMENTS 'status' field says "live". And then render certain fields from USERCOMMENTS and those given fields from PRODUCTS.

The result is that you'd get a random about of comments (say 16), but they are all for products LIVE And IN STOCK.

Can anyone guide or help me at all?

UPDATE:
I have just tried this but all it did was to find ALL usercomments, and put the .title or .nickname in the list.

Code: Select all

$comments = mysql_query ("SELECT * FROM usercomments LEFT JOIN products ON usercomments.prodid=products.id WHERE products.rcstock = 'in stock' AND products.pause = 'off' AND usercomments.status = 'live'")or die(mysql_error());

while ($row = mysql_fetch_object($comments))
      {
echo "<div onmouseover=\"boxOnHover(this);\" onmouseout=\"boxOffHover(this);\"><a href='index.php?page=product&menu=categ&category=$row->product.category&product=$row->product.id&head=$row->product.title' style='text-decoration: none'>
<b>$row->product.title <font color='#FF7C09'>$row->product.category]</font></b> > nickname: $row->usercomments.nickname<br/>
<i>'$row->usercomments.usercomments'</i><hr noshade size='1' color='#cccccc' /></a></div>";
      }mysql_free_result($comments);
echo "</div>";

Re: INNER JOIN Query

Posted: Thu Jun 17, 2010 7:39 am
by Weirdan
You want INNER JOIN there, not LEFT JOIN.

Re: INNER JOIN Query

Posted: Thu Jun 17, 2010 7:44 am
by simonmlewis
I tried both.

Re: INNER JOIN Query

Posted: Thu Jun 17, 2010 8:44 am
by Weirdan
$row->product.title
To access columns you should refer to them by their short name: $row->title, $row->id, etc.

Re: INNER JOIN Query

Posted: Thu Jun 17, 2010 9:20 am
by simonmlewis
And if i want to access both tables?

Re: INNER JOIN Query

Posted: Thu Jun 17, 2010 9:42 am
by Weirdan
simonmlewis wrote:And if i want to access both tables?
You still use short names. If column names conflict in different tables you would need to alias them in the query itself, like this:

Code: Select all

select product.id as product_id, category.id as category_id from ....
and access as $row->product_id, $row->category_id afterwards.

Re: INNER JOIN Query

Posted: Thu Jun 17, 2010 9:50 am
by simonmlewis
Yes I can see how to add that in the first half of the query: select this as that, this as that...etc... but not after the INNER JOIN part.

Re: INNER JOIN Query

Posted: Thu Jun 17, 2010 10:05 am
by Weirdan
simonmlewis wrote:but not after the INNER JOIN part.
And you don't need column aliases after inner join because column aliases are defined in SELECT clause:

Code: Select all

select
  product.id as product_id,
  category.name as category_name
from product
inner join category on category.id=product.category_id
-- ....