INNER JOIN Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

INNER JOIN Query

Post 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>";
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: INNER JOIN Query

Post by Weirdan »

You want INNER JOIN there, not LEFT JOIN.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: INNER JOIN Query

Post by simonmlewis »

I tried both.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: INNER JOIN Query

Post by Weirdan »

$row->product.title
To access columns you should refer to them by their short name: $row->title, $row->id, etc.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: INNER JOIN Query

Post by simonmlewis »

And if i want to access both tables?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: INNER JOIN Query

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: INNER JOIN Query

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: INNER JOIN Query

Post 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
-- ....
Post Reply