MySQL query with left Join?

Question

I have two tables: Items:

| Item|
   A
   B
   C

userItems:

| UID |  Item
   1      A
   1      C

This loop shows the data from table Items:

$data = mysql_query("SELECT * FROM `Items`ORDER by `Icons` ASC"); 

while($row = mysql_fetch_array( $data )) 
    {
    echo $row['Item'];
    echo "Unlock";
    }

Basically I need to display Unlocked instead of Unlock if the user with UID = $uid has the item.

This query gets the user items:

$data = mysql_query("SELECT * FROM `userItems` WHERE `UID` = '$uid'");

I believe the solution is an left join, but I don't know how to do it. How can I make it work?

Why do you query from Items table at all? You have Item column in userItems as well. — zerkms
– zerkms, Commented Sep 8, 2011 at 0:42
I could insert all items to userItems on registration and add another column but it's easier to have a different table in case I want to add new items in the future. — lisovaccaro
– lisovaccaro, Commented Sep 8, 2011 at 0:43
@zerkms I think there's more in the Items table than a single column, Icons for example — Phil
– Phil, Commented Sep 8, 2011 at 0:45
@Phil: then it is bad example ;-) programmers solve troubles, not make predictions — zerkms
– zerkms, Commented Sep 8, 2011 at 0:50

Carl F. · Accepted Answer · 2011-09-08 00:50:08Z

1

SELECT Items.*, useritems.UID FROM `Items` 
    left join useritems on Items.Item=useritems.Item 
    ORDER by `Icons` ASC

Then just use logic in your PHP to display Unlocked when user $row["items.UID"] equals $uid

edited Sep 8, 2011 at 0:50

answered Sep 8, 2011 at 0:43

Carl F.

7,0843 gold badges31 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Phil Over a year ago

I'd change the * to Items.*, userItems.UID. Just can't abide by a single * with joined tables. Who knows what you're going to get back

Carl F. Over a year ago

You're right. I have no idea what might be lurking in those tables. ;)

Carl F. Over a year ago

@Liso22 You're kidding! I haven't done SQL in months. I thought for sure I'd have a syntax error or detail lost in there somewhere.

Collectives™ on Stack Overflow

MySQL query with left Join?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related