[RESOLVED] checking multiple columns for results

[beylah]

i have a sign up and would like to add up the # of children in each age group

<?php
include("connectdb.php");

mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$query = "SELECT COUNT(*) FROM familyparty WHERE age1='Over18' AND age2='Over18' AND age3='Over18' AND age4='Over18' AND age5='Over18' AND age6='Over18' ";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo "".$row['COUNT(*)']."";
}
?>

the AND is not correct bec it will look for each solumn with the result and come back "0" which is does

so how do i get it to take from all columns

[kurby]

I think you want OR instead of AND.

SELECT COUNT(*) 
FROM familyparty 
WHERE age1='Over18' 
          OR age2='Over18'
          OR age3='Over18' 
          OR age4='Over18' 
          OR age5='Over18' 
          OR age6='Over18'

[beylah]

If I use an OR clause it will stop once one of the column proves true and not select using the other columns as a condition.

[kurby]

I seem to misunderstand what your trying to do. If you want every one of them to be true you should use AND, if you only want one to be true you use OR. Is there something in between those two you are trying to do?

Help me understand.

[beylah]

sorry for being unclear, i will try again

there are 4 ages groups and you can register up to 6 kids (age1, age2, age3, ag4, age5, age6)

the bit of coding is for one of the age groups (over18) i have the same coding for the other age groups

so if i want to get a total of all the kids registered and are over18 then i will need to look in all the age columns (age1, age2, age3, ag4, age5, age6) to get a total

"AND" will only give me a count if the person registers 6 kids all over18 and "OR" will only give me a count until it hits a column with "over18" but i want it to look at all the columns and give me a total

[kurby]

I see now. Unfortunately I don't think there is an elegant solution to do it in SQL. You can of course count the columns that are "over18" using a loop in PHP. However, I would suggest reevaluating your table schema. If you had another table that stored the registered kids you could generate queries much cleaner.

OR

If that column is a simple "over18" and "under18" column then you may want to look at using a numerical flag. Name the column "over18" and set it 1 if its true and 0 if its false. Then you can add up the columns to see how many are over 18.

[beylah]

thank you - i will try a redesign