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

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)

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

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!

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

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.