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...

Re: Using result in query as variable for WHERE 80 80

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)

Re: Using result in query as variable for WHERE 80 80

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

Re: Using result in query as variable for WHERE 80 80

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!

Re: Using result in query as variable for WHERE 80 80

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

Re: Using result in query as variable for WHERE 80 80

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!!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.