SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Evangelist Alexandra's Avatar
    Join Date
    Aug 2002
    Location
    Perth, Australia
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Count results that match criteria in database

    I need to count the number of times a particular result appears in a database. How do I structure a query to find out?

    For example: How many males or females have completed a survey?

    The database I am using stores each survey reponse in it's own row, with each field representing each question. Does that make sense?

    I've never done anything like this before and was hoping somebody could point me in the right direction?

    Thanks.
    Alex Graham
    Cadmium Design & Development
    www.cadmium.com.au
    alexandra@cadmium.com.au

  2. #2
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use the MySQl COUNT function to do this. It depends on how your table is set up but something like this should do the trick:

    Code:
    SELECT COUNT( * ) AS TotalFemale
    	FROM `surveytable`
    	WHERE `surveySexField` = 'female';
    That will count the number of records where the respondents sex is female. You will need to use the correct TableName and FieldName.

  3. #3
    SitePoint Evangelist Alexandra's Avatar
    Join Date
    Aug 2002
    Location
    Perth, Australia
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent! Thanks for that.

    I take it "TotalFemale" is the name of the result?
    Alex Graham
    Cadmium Design & Development
    www.cadmium.com.au
    alexandra@cadmium.com.au

  4. #4
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, you can call it whatever you like.

  5. #5
    SitePoint Evangelist Alexandra's Avatar
    Join Date
    Aug 2002
    Location
    Perth, Australia
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, stupid question number 2...How do I output the result? I'm just getting "Resource id #6", etc....What am I doing wrong?
    Alex Graham
    Cadmium Design & Development
    www.cadmium.com.au
    alexandra@cadmium.com.au

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Alexandra
    I'm just getting "Resource id #6", etc....
    i think this is php, right? moving thread to php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist Alexandra's Avatar
    Join Date
    Aug 2002
    Location
    Perth, Australia
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, sorry. It started out as an SQL query questions and evolved into a PHP one...
    Alex Graham
    Cadmium Design & Development
    www.cadmium.com.au
    alexandra@cadmium.com.au

  8. #8
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's how I use a similar statement:

    Code:
    //count live dubai breaks events in DB
    	$eventcount = mysql_query("
    	SELECT COUNT( * ) AS TotalDubai
    	FROM `events` 
    	WHERE `TripDubai` = '1' AND (`EventDate` > CURDATE() OR `EventEndDate` > CURDATE()) 
    	AND `EventShow` = '1'
    	;");
    			  
    	$stats = mysql_fetch_array($eventcount);	
    	$statsdubai	= $stats["TotalDubai"];
    
    //print out the count
            echo "total - ".$statsdubai;
    You should be able to make this fit the earlier example.

  9. #9
    SitePoint Evangelist Alexandra's Avatar
    Join Date
    Aug 2002
    Location
    Perth, Australia
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have the following, but despite there being two values that match, it comes back blank...

    Code:
    $femalecount = mysql_query("SELECT COUNT( * ) AS TotalFemales FROM answers WHERE gender = 'F';");
      				
    $females = mysql_fetch_array($femalecount);	
    $statsfemale	= $stats["TotalFemales"];
      				
    echo "<p>Total Females:" . $statsfemale . "</p>";
    Where have I gone wrong?
    Alex Graham
    Cadmium Design & Development
    www.cadmium.com.au
    alexandra@cadmium.com.au

  10. #10
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    you've used $stats as in elgumbo's example, but should have use $females instead, as this is your variable:

    $statsfemale = $females["TotalFemales"];
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  11. #11
    SitePoint Evangelist Alexandra's Avatar
    Join Date
    Aug 2002
    Location
    Perth, Australia
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahhh, whoops. Thanks. I've been working too long...must be time to call it a day!
    Alex Graham
    Cadmium Design & Development
    www.cadmium.com.au
    alexandra@cadmium.com.au


Bookmarks

Posting Permissions

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