CODEWITHSUNDEEP
phpsqlphpmyadmin
TDB
TDB

Reputation: 19

php sql subquery not giving any result, but sql subquery is

Code:

$query  = mysqli_query($conn, "SELECT * FROM zoekkoper WHERE user_id = (SELECT id FROM USERS WHERE email='$session_email')");

while ($record = mysqli_fetch_assoc($query))
{
table output...
}

Have 2 tables:

1) users (id)

2) zoekkopers (id, user_id)

Want to select results from table 'zoekkopers' with a match from table 'users' id. id and user_id have the same value in both tables. The current logged in user should only see his own added values. I tried the sql in my phpadmin and that gives me the correct results but I'm failing making it work in php. If I delete the WHERE part then I get all the results value from all the users in my database, just want it more specific for each user.

$session_email is the current logged in user.

Also tried this sql in PHP:

"SELECT * FROM zoekkoper JOIN users ON (user_id=id) WHERE email='$session_email'");

PHPMYADMIN:

SELECT * 
FROM  `zoekkoper` 
WHERE user_id = ( 
SELECT id
FROM users
WHERE email =  '...' )

Upvotes: 1

Views: 198

Answers (2)

Death-is-the-real-truth
Death-is-the-real-truth

Reputation: 72299

You need to JOIN like below:-

$query  = mysqli_query($conn, "SELECT * FROM zoekkoper JOIN users ON zoekkoper.user_id= users.id WHERE users.email='$session_email'") or die(mysqli_error($conn));

Upvotes: 1

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

I think, you should use IN for where clause, because the subselect may return more than one results;

$query  = mysqli_query($conn, "SELECT * FROM zoekkoper WHERE user_id IN (SELECT id FROM USERS WHERE email='$session_email')");

Upvotes: 0

Related Questions