CODEWITHSUNDEEP
mysqlsql
echo_Me
echo_Me

Reputation: 37243

filtering result by sql

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

Upvotes: 1

Views: 121

Answers (3)

John Woo
John Woo

Reputation: 263943

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.

Upvotes: 4

Vishal Bardoloi
Vishal Bardoloi

Reputation: 652

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.

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44356

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)

Upvotes: 1

Related Questions