www.webdeveloper.com
Results 1 to 7 of 7

Thread: [RESOLVED] checking multiple columns for results

  1. #1
    Join Date
    May 2009
    Posts
    248

    resolved [RESOLVED] checking multiple columns for results

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

    PHP Code:
    <?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

  2. #2
    Join Date
    Apr 2008
    Posts
    190
    I think you want OR instead of AND.

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

  3. #3
    Join Date
    May 2009
    Posts
    248
    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.

  4. #4
    Join Date
    Apr 2008
    Posts
    190
    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.

  5. #5
    Join Date
    May 2009
    Posts
    248
    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

  6. #6
    Join Date
    Apr 2008
    Posts
    190
    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.

  7. #7
    Join Date
    May 2009
    Posts
    248
    thank you - i will try a redesign

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles