INNER JOIN Query
Posted: Thu Jun 17, 2010 4:27 am
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.
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>";