Request to return the number of visitors according to gender

advertisements

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


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?