Can I combine these 2 queries into 1?

[Erwin]

Thanks for helping in advance.

PHP Code:


$top=$DB_site->query_first("SELECT userid,field10 FROM userfield WHERE userid!=0 ORDER BY field10 desc LIMIT 5");

$topname=$DB_site->query_first("SELECT username,userid FROM user WHERE userid=$top[userid]"); 


I tried to use the JOIN command, but I keep ending up making loops. Thanks for helping again.

[Erwin]

Also, the other problem is that both tables have the same variable name 'userid' which gives me an 'ambiguous variable' mysql error.

[Xenon]

try that one (not sure i'm not so good in mysql^^)

PHP Code:

$top=$DB_site->query("SELECT DISTINCT user.username, user.userid, userfield.field10 FROM userfield LEFT JOIN user ON (user.userid=userfield.userid) WHERE NOT userfield.userid=0 ORDER BY userfield.field10 desc LIMIT 5); 


[Erwin]

Thanks... how would you display the result?

$top[user.userid] ? That gives a parse error
$top[user.username]??

[Admin]

PHP Code:

$top = $DB_site->query_first('
    SELECT user.userid, username, field10
    FROM user
    LEFT JOIN userfield USING (userid)
    ORDER BY field10 DESC
'); 


Then use $top[userid] for user ID, $top[username] for username, and $top[field10] for the value of field 10.

(Just so you know, the original code had LIMIT 5 but I removed that since we are using query_first() anyway. If you want to get the top 5, you'll need some different code. But the code above does exactly the same thing your original code did.)

[Erwin]

Firefly, you are a king.

That worked like a charm. I was able to do a Top 5 using an array. Thanks. Your tip is going to help me in more ways than one.

[Admin]

Glad I could help.