Ryan D Ryan D - 4 months ago 14
JSON Question

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 that has 3 columns - date, visits, sold.. Visit values are strings yes or no as well as sold = yes or no..I would like to display how many leads and sold for each day of the month. The db has a new row for each lead and sold so there are multiple dates that are duplicated for each lead and sold.

It throws this error - Uncaught Error: Type mismatch. Value 1 does not match type number in column index 1

EDIT ** I updated to this sql and now the data is correct but the error is thrown Uncaught Error: Type mismatch. Value 1 does not match type number in column index 1 still and the chart will not display

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


..

<?php
$json = array();
$sql = "SELECT date, 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 date ORDER BY date ASC";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$dataRow = array(
$row['day'],
$row['visit'],
$row['sold'],
);
array_push($json, $dataRow);
}
}
$jsonstring = json_encode($json);
?>


Here is the chart script

<script src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {
'callback': function () {
var data = google.visualization.arrayToDataTable([
[{type: 'string', label: 'Date'}, {type: 'number', label: 'Leads'}, {type: 'number', label: 'Sold'}]
]);

data.addRows(<?= $jsonstring ?>);

var options = {
title: 'Internet Performance',
curveType: 'function',
legend: { position: 'top' }
};

var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
chart.draw(data, options);
},
'packages': ['corechart']
});
</script>

<div id="curve_chart" style="width: 1900px; height: 500px"></div>

Answer

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.