distinct mysql

[neonpie]

I am working on a system that requires notes to be added to a candidate. each note has a status of red, orange and green. what i want to do is display the list of candidates then run a query on each candidate to run through the notes table and check if there are any notes for that candidate, but i want it to check the status of all the notes related to that candidate and if any are red set $flag="red", if there are no status for red then continue and check for orange and then for green. I can then set a flag image next to the candidate so on a quick visual check you can see if the note has been actioned.

I have the following query:
$query_flag = "SELECT DISTINCT `candidate_id` , `status` FROM `notes` WHERE `candidate_id` ='".$candidate_item['id']."'";
$result_flag = mysql_query($query_flag, $db_connect);

Thanks for any help in advance

[cameraman]

You don't need DISTINCT for this. There's at least 3 or 4 ways you could do it, but to get just the red ones, for example:
$query_flag = "SELECT `status` FROM `notes` WHERE `candidate_id` ='".$candidate_item['id']."' AND status='red'";

If there are no rows - you can check with mysql_num_rows($result_flag) - then move on to the next color.

Alternatively you could get all of the note status codes for the candidate:
$query_flag = "SELECT `status` FROM `notes` WHERE `candidate_id` ='".$candidate_item['id']."'";

use one of the mysql_fetch_ functions to put the rows into an array and use in_array [us3.php.net] for each of the colors in turn:
if(in_array('red',$status_array)) {
}
elseif(in_array('orange'. . .

[neonpie]

thanks for replying cameraman.

i have basically got it working, but it is really messy and would prefer it to be not as ugly as it is :-P

here is what ive got so far. is there a better way of writing this to produce the same outcome?

$query_flag = "SELECT `status` FROM `notes` WHERE `candidate_id` ='".$candidate_item['id']."' AND status='red'";
$result_flag = mysql_query($query_flag, $db_connect);
if(mysql_num_rows($result_flag)>0){
$flag="red";
}else{
$query_flag2 = "SELECT `status` FROM `notes` WHERE `candidate_id` ='".$candidate_item['id']."' AND status='orange'";
$result_flag2 = mysql_query($query_flag2, $db_connect);
if(mysql_num_rows($result_flag2)>0){
$flag="orange";
}else{
$query_flag3 = "SELECT `status` FROM `notes` WHERE `candidate_id` ='".$candidate_item['id']."' AND status='green'";
$result_flag3 = mysql_query($query_flag3, $db_connect);
if(mysql_num_rows($result_flag3)>0){
$flag="green";
}
}
}

[cameraman]

No, that's about how it's going to look with separated status queries - well, I suppose you could array the colors and use a control loop like for or foreach; that would make it look a little cleaner. The other way, with one query, would look like:

$query_flag = "SELECT `status` FROM `notes` WHERE `candidate_id` ='".$candidate_item['id']."'";
$result_flag = mysql_query($query_flag, $db_connect);
while($rows[] = mysql_fetch_row($result_flag));
if(in_array('red',$rows))
$flag='red';
elseif(in_array('orange',$rows))
$flag='orange';
elseif(in_array('green',$rows))
$flag='green';