counting records containing a set value

dcjones

Hi all.

I have a data table which has 8 fields:

secure_id_1_L
secure_id_2_L
secure_id_3_L
secure_id_4_L
secure_id_5_L
secure_id_6_L
secure_id_7_L
secure_id_8_L

each field hold YES or NO

I want to be able to count the number of "yes" fields in each record. This is what I have so far:

mysql_select_db($database_baggageppin, $baggageppin);
$query_baggage_lost = "SELECT secure_id_1_l,secure_id_2_l,secure_id_3_l,secure_id_4_l,secure_id_5_l,secure_id_6_l,secure_id_7_l,secure_id_8_l FROM amember_baggagemembers WHERE secure_id_1_l = "yes" OR secure_id_2_l = "yes" OR secure_id_3_l = "yes" OR secure_id_4_l = "yes" OR secure_id_5_l = "yes" OR secure_id_6_l = "yes" OR secure_id_7_l = "yes" OR secure_id_8_l = "yes"";
$query_limit_baggage_lost = sprintf("%s LIMIT %d, %d", $query_baggage_lost, $startRow_baggage_lost, $maxRows_baggage_lost);
$baggage_lost = mysql_query($query_limit_baggage_lost, $baggageppin) or die(mysql_error());
$row_baggage_lost = mysql_fetch_assoc($baggage_lost);

if (isset($_GET['totalRows_baggage_lost'])) {
  $totalRows_baggage_lost = $_GET['totalRows_baggage_lost'];
} else {
  $all_baggage_lost = mysql_query($query_baggage_lost);
  $totalRows_baggage_lost = mysql_num_rows($all_baggage_lost);
}
$totalPages_baggage_lost = ceil($totalRows_baggage_lost/$maxRows_baggage_lost)-1;
?>

Can anyone point me in the reight direction.

Piranha

Why do you have 8 columns with values in one table? Normally a better way is to build it with 2 tables.

[b]Table user[/b]
userid
whatever

[b]Table group[/b]
groupid
whatever

[b]Table usergroup[/b]
userid
groupid

dcjones

Hi and thank you for your reply.

I totally agree with you that it would be a better design if I were to use more than one table.

But sadly in this case I am totally restricted to using one for this project.

Do you have any idea how I can get to where I need to be.

Roger_Ramjet

You need to use the control flow functions to return 1 for yes so that you can sum the columns. Pity you are storing 'yes' and 'no', if it was 1 and 0 then just sum those values would do it.

"SELECT IF(col1='YES', 1, 0) + IF(col2='YES', 1, 0) + ..."

dcjones

Hi Roger

I can change to sroring 0 = NO and 1 = YES

Would this change the code in any way?

Thanks again

Dereck

Roger_Ramjet

Just col1 + col2 + col3

Personally I use tinyint and 0,1 in mysql because it does not support a BOOLEAN data type so would have had this solution already. You will have to manage those values when you use them elsewhere in your code, but probably already had to do that with the string values anyway.

To make the results more usable you will probably want to bracket and alias the arithmetic to give the results a column name.