Having trouble understanding joins

[subnet_rx]

I have two tables.  One has items, one has items ordered.  I want to list all the items, but if the current user ordered one of the items, I'd like to place a check by it.  The output would use several columns from the items table, and basically show a check if the corresponding item is found in the items ordered under the user's id.  I'm unsure how joins work, even after looking at a tutorial here and the SQL book I have on my desk.  Can someone either point me to a good explanation or point me in the right direction? 

 

[madspof]

Can you explain more it is not clear what you objective is

[Barand]

There are two common types of join

 

A JOIN (or INNER JOIN) will return rows only where rows in both tables match on a common key value.

 

In your case though, you need the second type, LEFT JOIN, to list all items whether a match exists or not. Where there isn't a match in the items_ordered table, all its columns in the result set contain NULL values.

 

So

 

SELECT i.item_name, o.user_id

FROM items i LEFT JOIN items_ordered o

ON i.item_id = o.item_id AND o.user_id = '$user_id'

 

When you process the result set, if the user_id col has a non-null value, then show it as checked

[subnet_rx]

Thanks, but how do you access the second table's variables in the array so that you can show a check if the value exists?

[Barand]

By putting it in the SELECT clause as I did with o.user_id

 

while ($row = mysql_fetch_assoc($result)) {
    if ($row['user_id'])
    {
         // user ordered this one
    }
}

[subnet_rx]

ok, thanks.  For some reason, I was trying to figure out how I could access the value if both had the same column name.  But after I think about, I have no idea why I'd want to do that.  That's one of my problems, I get confused about something and start trying to figure out problems that have nothing to do with my current one.  Sometimes it helps me understand, others it just gets me lost.