[SOLVED] Multiple mysql queries - is there an easier way?

[bwcc]

Ok - I have this code thus far - the first query ($r_m) picks all users where the manage column equals a number.  The WHILE statement puts the array into a dropdown list.  What I want to do is then color-code the user names pending if they have a blank "adate" field.  I hate to run a query in a WHILE statement, but I can't think of another way to run this right now - any help would be appreciated!

[code]
$r_m = @mysql_query("SELECT * FROM users WHERE manage = '{$r_u['account']}'");
while ($row_m = mysql_fetch_array($r_m)) {
    $r_n = @mysql_fetch_array(mysql_query("SELECT adate FROM entries WHERE account='{$row_m['account']}' AND adate=''"));
    if ($r_n) {
        echo '<option value="manage.php?account='.$row_m['account'].'&m=1" style="color:green;">'.$row_m['name'].'</option>';
    }else{
        echo '<option value="manage.php?account='.$row_m['account'].'&m=1">'.$row_m['name'].'</option>';
    }
}
[/code]

[btherl]

Try this:

[code=php:0]SELECT name, adate = '' AS pending FROM users JOIN entries ON (account) WHERE manage = '{$r_u['account']}'[/code]

Your results will be 2 columns, name and pending.  Pending will be true if adate = '' for that user.  The join on account just means "Match up rows where account is matching".

[bwcc]

I had to amend the query to :
[code]SELECT DISTINCT users.name, entries.adate = '' AS pending
FROM users JOIN entries ON entries.account=users.account WHERE users.manage = '05107111'[/code]

inorder to get any sort of result, but it dropped one user - so looks like it's better off - thanks!

[btherl]

Sorry, I messed up the syntax :)

What do you mean by "it dropped one user"?

[bwcc]

NP - it pointed me in the right direction  ;D

It dropped a user because of the entries.account = users.account

The user it dropped did not have an entries.account entry as of yet, so the query works the way it's suppose to.

Thanks again

[btherl]

Aha, I get it.  You're welcome :)

If you want to include the dropped user, use LEFT JOIN instead of JOIN.  That will include rows which exist in the left table but not the right table.  The right table's columns will be filled with nulls for that row.