Query to return number of visitors based on gender

Question

I have this query to return the number of visitors between 2 dates.

$SQLVisit = "SELECT     
            count(score) as counts,
            date FROM persons  
            WHERE date > '".$_SESSION['data1']."' AND date < '".$_SESSION['data2]."' 
            GROUP BY date 
            ORDER BY date asc"; 

$result = mysql_query($SQLVisit);   
$num = mysql_num_rows($result);

these visitors, some are male and others female.

I already have an array that puts me the number of visits and the corresponding date. Like this: ['2012-1-1 ', 50]. My idea was to have an array that would keep the date, the number of visits, the number of males and number of females. Like this: ['2012-1-1 ', 50, 35.15].

Any idea? ps: Im using PHP.

edit: code for array

$data[0] = array('day','counts');       
    for ($i=1; $i<($num+1); $i++)
    {
        $data[$i] = array(substr(mysql_result($result, $i-1, "date"), 0, 20),
            (int) mysql_result($result, $i-1, "counts"),);
    }   
    echo json_encode($data);

edit2: Yes i have a gender (int) column , 1 for male and 2 for female

Where is the gender stored? Is there an extra field in your database? — Jens, Nov 20 '12 at 14:11
Yes i have a gender (int) column , 1 for male and 2 for female — noneJavaScript, Nov 20 '12 at 14:11

score 1 · Accepted Answer · edited May 23 '17 at 12:18

1

if you have gender column, try

$SQLVisit = "SELECT  date, gender,   
                     count(score) as counts,
            FROM  persons  
            WHERE date > '".$_SESSION['data1']."' AND date < '".$_SESSION['data2]."' 
            GROUP BY date, gender 
            ORDER BY date asc";

and if you want to format the gender that instead of number, you want to display it as male or female

$SQLVisit = "SELECT  date, 
                     IF(gender = 1, 'Male', 'Female') gender,   
                     count(score) as counts,
            FROM  persons  
            WHERE date > '".$_SESSION['data1']."' AND date < '".$_SESSION['data2]."' 
            GROUP BY date, gender 
            ORDER BY date asc";

if you also also want to have format like this,

date    Male      Female
===========================
'date'     5          6

use the following query

SELECT  DATE,
        SUM(CASE WHEN gender = 1 then 1 ELSE 0 END) Male,
        SUM(CASE WHEN gender = 2 then 1 ELSE 0 END) Female
FROM    person
-- WHERE clause
GROUP BY DATE
-- ORDER clause

your query is vulnerable with SQL Injection, please read the article below

How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:18

Community

1
1

answered Nov 20 '12 at 14:12

John Woo

258,903
69
498
492

Yes i have a gender (int) column , with only two kinf of data: 1 for male and 2 for female. That didnt work, because i need to work out with array – noneJavaScript Nov 20 '12 at 14:14
Thanks, but could you help me with array too? :) – noneJavaScript Nov 20 '12 at 14:26
what do you want to do with the array? – John Woo Nov 20 '12 at 14:27
I've already have this: ["day","counts","Male","Female"]["2012-01-20",1,1,0]. New problem now :s Because this array will build a chart and if I have male and female visitors for the same day it doesnt sum, it spread the results repeating the days instead of sum visitors in same day. ill show you in a picture. – noneJavaScript Nov 20 '12 at 14:33
your new problem can be best ask in another question :D *for the same day it doesnt sum it spread the results repeating the days instead of sum* - *unclear* to me :-( – John Woo Nov 20 '12 at 14:36
do your `DATE` column has time? how about this? `SELECT DATE(date), ....GROUP BY DATE(date)` – John Woo Nov 20 '12 at 14:40
glad to hear that :D have fun! – John Woo Nov 20 '12 at 14:52

score 1 · Answer 2 · answered Nov 20 '12 at 14:12

1

You can use SUM(IF()):

$SQLVisit = "SELECT     
            count(score) as counts,
            SUM(IF(gender=1,1,0)) as male,
            SUM(IF(gender=2,1,0)) as female,
            date FROM persons  
            WHERE date > '".$_SESSION['data1']."' AND date < '".$_SESSION['data2]."' 
            GROUP BY date 
            ORDER BY date asc";

answered Nov 20 '12 at 14:12

Jens

2,050
1
14
30

what means (gender=1,1,0) and (gender=2,1,0) ? – noneJavaScript Nov 20 '12 at 14:16
@skdnewbie It's just a shorthand if statement. If gender is 1, then TRUE (1) otherwise FALSE (0). – wesside Nov 20 '12 at 14:18
The IF is important ;) IF(gender=1,1,0) returns 1 if gender is 1 else 0. This is calculated for each row and summed using SUM. – Jens Nov 20 '12 at 14:18

score 0 · Answer 3 · answered Nov 20 '12 at 14:16

0

You can do this:

SELECT     
  SUM(CASE WHEN Gender = 1 THEN 1 ELSE 0) malescounts,
  SUM(CASE WHEN Gender = 2 THEN 1 ELSE 0) femalescounts,
  count(score) as counts
FROM persons  
WHERE date > ...
GROUP BY date;

answered Nov 20 '12 at 14:16

Mahmoud Gamal

78,257
17
139
164

Query to return number of visitors based on gender

3 Answers3