Using result in query as variable for WHERE

[ShArKsss]

Okay,

So I want an array to get userinformation by the username that is provided. But in the other tables, it only has a ID field to link with...

$sql = "mos_users.id as userid, block, username, email, lastvisitDate, firstname, lastname, avatar, cb_age, userip FROM mos_users, mos_comprofiler, mos_comprofiler_plug_iplog WHERE mos_users.username = '". $user."' AND mos_comprofiler.user_id = (SELECT id FROM mos_users WHERE username = '".$user."') AND mos_comprofiler_plug_iplog.userid  = (SELECT id FROM mos_users WHERE username = '".$user."')";

Below is the query that works, if I give in the ID , I get all the information I want

$sql = "SELECT mos_users.id as userid, block, username, email, lastvisitDate, firstname, lastname, avatar, cb_age, userip FROM mos_users, mos_comprofiler, mos_comprofiler_plug_iplog WHERE mos_users.id = ". $id . " AND mos_comprofiler.user_id = " . $id . " AND mos_comprofiler_plug_iplog.userid = " . $id;

Can someone tell me what I'm doing wrong? I've been at this for several hours now...

[smantscheff]

Join the tables on the field userid and use there WHERE search for the name only on the user table.

$sql = "mos_users.id as userid, block, username, email, lastvisitDate, firstname, lastname, avatar, cb_age, userip FROM mos_users, mos_comprofiler, mos_comprofiler_plug_iplog WHERE mos_users.username = '". $user."' AND mos_comprofiler.user_id = mos_users.userid AND mos_comprofiler_plug_iplog.userid  = mos_users.userid";

(not tested)

[ShArKsss]

Yeah... I was thinking something along these lines too, it did not work unfortunately :)

[ShArKsss]

Actually what smantscheff said worked, he cut out the SELECT in front and I was just a stupid copy/paste person :) putting SELECT in front worked. Thanks bro!

[smantscheff]

So maybe your original code would have worked, too, with the "SELECT" in it? But my version is definitely more efficient. And better readable.

[ShArKsss]

Haha! You're so right, I'm so stupid :| this is what happens when you look at the same code for hours, you don't see the small mistakes :D and yes, yours is very efficient!!! Thanks alot bro!!!