CODEWITHSUNDEEP
phpmysqlsqlarrays
noneJavaScript
noneJavaScript

Reputation: 845

Query to return number of visitors based on gender

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

Upvotes: 1

Views: 407

Answers (3)

John Woo
John Woo

Reputation: 263803

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

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

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;

Upvotes: 0

Jens
Jens

Reputation: 2075

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";

Upvotes: 1

Related Questions