How To Show Unique Dates When Outputting Data To Charts In Php

Based on a answer I got earlier from @WhiteHat (Thank you) I cannot seem to get the dates to show unique so I made this a new question as its an extension question.. I have a DB th

Solution 1:

I would suggest do the grouping per day like this in your SQL statement:

$json = array();
$sql = "SELECT   date_format(date, '%Y-%m-%d') as day,
                 sum(case when visit = 'Yes' then 1 else 0 end) as visit, 
                 sum(case when sold = 'Yes' then 1 else 0 end) as sold 
        FROM     customers
        WHERE    source = 'website'
        GROUP BY day
        ORDER BY day ASC";
$result = $conn->query($sql) or die('"error in sql"');
while($row = $result->fetch_assoc()) {
    $json[] = array(
        $row['day'],
        $row['visit'],
        $row['sold'],
    );
}
$jsonstring = json_encode($json,  JSON_NUMERIC_CHECK);
if (json_last_error()) $jsonstring = '"' . json_last_error_msg() . '"'; 
// somewhere you need to output the data:
echo $jsonstring;

The dates are generated in the yyyy-mm-dd format, which is the ISO standard. I am not sure if this is acceptable for the graph library you use, although it should, because you defined it as type string. If the date format is an issue, then change it in the SQL statement accordingly.