filtering result by sql

Question

i have table name is ads like that:

    id    ad_country          ad_gender     ad_birthday
     1     UK                  Male          2012-02-26
     2     sangapor            Female        2011-05-29
     3     UK                  Female        2010-04-12

and i have two drop down select options

   one  is to select country

and

    one is to select year

is it possible to make one sql query which affine the results to get :

how many male in every country selected and by the year selected also. how many female in every country and by the year selected also thx

i have tried this sql but didnt work.

    SELECT COUNT(ad_gender) AS male FROM ads  WHERE '".$_POST['country']."' = ad_country  AND ad_gender = Male AND '".$_POST['year']."' = '".$row2['ye']."'

note that $row2['ye'] is from $sql2 as follows

     SELECT ad_birthday,(substr(ad_birthday , 1, 4)) AS ye FROM ads

EDITED my post as its not much code i gave , here my new question my new post

Yes, it is. What have you tried. – podiluska Sep 11 '12 at 21:05 — podiluska, Sep 11 '12 at 21:05
how would your expected output look like ? – t-clausen.dk Sep 11 '12 at 21:20 — t-clausen.dk, Sep 11 '12 at 21:20

score 4 · Accepted Answer · edited Sep 12 '12 at 18:57

I think the simpliest way to do this with using group by and having is by using case in your query.

SELECT  SUM(CASE WHEN ad_gender = 'Male' THEN 1 ELSE 0 END) TotalMale,
        SUM(CASE WHEN ad_gender = 'Female' THEN 1 ELSE 0 END) TotalFemale
FROM    ads
WHERE   ad_country = 'countryHere' AND
        YEAR(DATE(ad_birthday)) = yearHere

SQLFiddle Demo

One more thing, never ever pass the value from user directly to your sql statement as it may be vulnerable with SQL Injection. Use PDO or MYSQLI

Example of using PDO extension:

<?php

     $query = "    SELECT   SUM(CASE WHEN ad_gender = 'Male' THEN 1 ELSE 0 END) TotalMale,
                           SUM(CASE WHEN ad_gender = 'Female' THEN 1 ELSE 0 END) TotalFemale
                   FROM     ads
                   WHERE    ad_country = ? AND
                            YEAR(DATE(ad_birthday)) = ?"

    $country = 'UK';
    $year = 2012;
    $dbh = new PDO('mysql:host=localhost;dbname=test;charset=utf8', $user, $pass);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $stmt = $dbh->prepare($query);
    $stmt->bindParam(1, $country);
    $stmt->bindParam(2, $year);
    if ($stmt->execute()) 
    {
         while ($row = $stmt->fetch()) 
         {
              print_r($row);
         }
    }

?>

this will allow you to insert records with single quotes.

you explained good and it was mybe me who didnt give whole code so i give it. Edited my post , thx . — echo_Me, Sep 12 '12 at 18:28
**Always** [disable emulated prepared statements](http://stackoverflow.com/a/60496/508666) when using the MySQL driver with PDO!! — PeeHaa, Sep 12 '12 at 18:48
@PeeHaa thank you for this, I didn't know this until now. Much appreciated. :) — John Woo, Sep 12 '12 at 23:46

Vishal Bardoloi · Answer 2 · 2012-09-11T21:23:36.857

This should be a pretty simple query to execute, so I'm wondering why it isn't working for you. What have you tried so far?

select
  ad_country as country,
  YEAR(ad_birthday) as year,
  ad_gender as gender,
  count(id) as count
from ads
where ad_country in ('/* your country params here*/')
group by ad_country, year, ad_gender
having YEAR(ad_birthday) in ('/* your year params here*/')

You shouldn't need to have a second SQL query just to pull the date part out of the table - use the built-in SQL date & time functions for this.

Also, this is going off-topic but the code you have posted looks like inline SQL. Please use parameterized queries instead or you're vulnerable to SQL injection attacks.

score 1 · Answer 3 · answered Sep 11 '12 at 21:16

1

select ad_country, count(case when ad_gender = 'male' then 1 end) male, 
count(case when ad_gender = 'female' then 1 end) female, year(ad_birthday) year
from ads
group by ad_country, year(ad_birthday)

answered Sep 11 '12 at 21:16

t-clausen.dk

43,517
12
59
92

you explained good and it was mybe me who didnt give whole code so i give it. Edited my post , thx . – echo_Me Sep 12 '12 at 18:29

filtering result by sql

3 Answers3

SQLFiddle Demo