Mysql multi table select

PHP
1

I’m developing a site where I need to pull data from multiple table at once.

Earlier I was doing fine, but now when I practiced that code in phpmyadmin, it shows multiple table for just one query

Say for example.

I’ve two tables on MYISAM engine and when I was trying executing with the code. below


select users.password, userstatus.securityQ, userstatus.securityA from users, userstatus where users.id='$uid'

then it shows multiple rows, and only the last row is relevant to my query while all others are just irrelevant.

Now when I modified the same code with just one additional parameter then it fetching the correct query


select users.password, userstatus.securityQ, userstatus.securityA from users, userstatus where users.id='$uid' and userstatus.uid='$uid'"

Though I’ve got the flaw here, but I’m no idea why the first code is not working properly. As per my level of knowledge, it should work fine as my userstatus is connected with users table with “uid”.

So here uid is unique in both tables.

Please any body help me out here.:shifty::injured:

2

First off, don’t quote integer values, so change =‘$uid’ to just =$uid.

Secondly, the first select query works fine. It is supposed to select multiple rows.

It joins both tables and then selects all rows from the joined table where users.id equals to $uid. You should write it like this to get only one row:


SELECT * FROM users u 
LEFT INNER JOIN userstatus us ON us.uid = u.id 
WHERE u.id = $uid;

Notice the ON us.uid = u.id part. It tells the db how to join tables, if you don’t specify that the db will join all rows from first table with one row from the second table (the one where id is equal to $uid). I hope that makes sense.

3

Thanx for the help.

I’m a newbie in database coding and structuring, while I tried your code, and when I remove the left word from the code, then it was working fine.

It seems joins are really handy with dealing with multiple tables at one go.

4

Yeah, sorry, it should just be INNER JOIN. I can’t edit my previous post now :slight_smile: