problem driving me mad

[Bald Bouncer]

PHP Code:

  $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><p><b>Team Members Found:</b></p></td>".iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap><p>&nbsp;</p></td>", "").iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap><p>&nbsp;</p></td>", "")."</tr>\n";
    while ($user=$DB_site->fetch_array($users)) {
      echo "<tr><td nowrap><p>$user[username] - $field[field14]</p></td>".iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap><a href=\"user.php?action=viewuser&userid=$user[userid]\"><p>[view user]</a> <a href=\"user.php?action=edituser&userid=$user[userid]\">[Edit User]</a></p></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]

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><p><b>Team Members Found:</b></p></td>".iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap><p>&nbsp;</p></td>", "").iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap><p>&nbsp;</p></td>", "")."</tr>\n";
	while ($user=$DB_site->fetch_array($users)) {
		echo "<tr><td nowrap><p>$user[username] - $field[field14]</p></td>".iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap><a href=\"user.php?action=viewuser&userid=$user[userid]\"><p>[view user]</a> <a href=\"user.php?action=edituser&userid=$user[userid]\">[Edit User]</a></p></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><p>&nbsp;</p></td>", "")

[Bald Bouncer]

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?

PHP Code:

$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]

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]

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

PHP Code:

$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><p><b>Team Members Found:</b></p></td>".iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap><p>&nbsp;</p></td>", "").iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap><p>&nbsp;</p></td>", "")."</tr>\n";
    while ($user=$DB_site->fetch_array($users)) {
      echo "<tr><td nowrap><p>$user[username] - $user[field14]</p></td>".iif($perms[ismoderator] or $bbuserinfo['usergroupid']==25, "<td nowrap><p>EDIT USER</p></td>", "")."</tr>\n";
    }
  } else {
    echo "<td><p>No Team Members found</p></td>";
  }
  echo "</table>";
} 


[Admin]

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

[Bald Bouncer]

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

:bunny: