problem driving me mad [Archive]

View Full Version : problem driving me mad

Bald Bouncer

04-06-2002, 03:42 PM

$users=$DB_site->query("SELECT userid,username,usertitle FROM user WHERE username LIKE '%".addslashes(htmlspecialchars($findname))."%' ORDER BY username");
$field=$DB_site->query("SELECT field14 FROM userfields WHERE userid=$users[userid]");
echo "<table>";
if ($DB_site->num_rows($users)>0) {
echo "<tr><td nowrap>Team Members Found:</td>".iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap> </td>", "").iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap> </td>", "")."</tr>\n";
while ($user=$DB_site->fetch_array($users)) {
echo "<tr><td nowrap>$user[username] - $field[field14]</td>".iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap><a href=\"user.php?action=viewuser&userid=$user[userid]\">[view user]</a> <a href=\"user.php?action=edituser&userid=$user[userid]\">[Edit User]</a></td>", "")."</tr>\n";
}

can anyone fix this code so it will show the contents of profile field 14 next to the username....I think it needs to fetch the rray of $users and $field but Im not sure how its done :(

any help appreciated

Admin

04-07-2002, 10:50 AM

You can join the user and userfield tables right in the first query:
$users = $DB_site->query("
SELECT userid,username,usertitle,field14
FROM user
WHERE username LIKE '%".addslashes(htmlspecialchars($findname))."%'
ORDER BY username
");

echo "<table>";
if ($DB_site->num_rows($users)>0) {
echo "<tr><td nowrap>Team Members Found:</td>".iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap> </td>", "").iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap> </td>", "")."</tr>\n";
while ($user=$DB_site->fetch_array($users)) {
echo "<tr><td nowrap>$user[username] - $field[field14]</td>".iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap><a href=\"user.php?action=viewuser&userid=$user[userid]\">[view user]</a> <a href=\"user.php?action=edituser&userid=$user[userid]\">[Edit User]</a></td>", "")."</tr>\n";
}
}
echo "</table>";
You also have this code twice in there, not sure it's supposed to be there (and if it is, use only one iif()):
iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap> </td>", "")

Bald Bouncer

04-07-2002, 06:38 PM

it will need to pull field14 from userfield not users i think....so how exactly do I use join to stick both the coloms in the same query mate?

$users = $DB_site->query("
SELECT userid,username,usertitle
FROM user
WHERE username LIKE '%".addslashes(htmlspecialchars($findname))."%'
ORDER BY username
");

I need to grab field14 from userfield in the above :dead:

Admin

04-07-2002, 06:46 PM

Of course I forgot the JOIN line... duh.
$users = $DB_site->query("
SELECT userid,username,usertitle,field14
FROM user
LEFT JOIN userfield USING (userid)
WHERE username LIKE '%".addslashes(htmlspecialchars($findname))."%'
ORDER BY username
");

Bald Bouncer

04-07-2002, 07:13 PM

cheers for the help mate but its still not working :( dont happen to know were its going wrong do you mate?

$users = $DB_site->query("SELECT userid,username,usertitle,field14 FROM user LEFT JOIN userfield USING (userid) WHERE username LIKE '%".addslashes(htmlspecialchars($findname))."%' ORDER BY username");
echo "<table>";
if ($DB_site->num_rows($users)>0) {
echo "<tr><td nowrap>Team Members Found:</td>".iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap> </td>", "").iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap> </td>", "")."</tr>\n";
while ($user=$DB_site->fetch_array($users)) {
echo "<tr><td nowrap>$user[username] - $user[field14]</td>".iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap>EDIT USER</td>", "")."</tr>\n";
}
} else {
echo "<td>No Team Members found</td>";
}
echo "</table>";
}

Admin

04-07-2002, 07:20 PM

Ok replace "SELECT userid" with "SELECT user.userid".

Bald Bouncer

04-07-2002, 07:39 PM

thanks a lot mate worked a charm!!
really appreciated!!

:p :bunny: