Valkyrja Valkyrja -3 years ago 103
PHP Question

SQL/PHP get daily records for the past 30 days

hello i am trying to use line chart (https://developers.google.com/chart/interactive/docs/gallery/linechart) from google chart and i don't know the query to get how many patients went into the hospital each day for the past 30 days so that i can add it to the data.addrow on the linechart
Here is what my table looks like:

pPatient

pName pDate
Sample Name 2017-08-15
Another Name 2017-08-15
Another Name Again 2017-08-15
Name Here 2017-08-14
Name Again 2017-08-13


i need a sql query so that i can dynamically change the line chart

google.charts.load('current', {packages: ['corechart', 'line']});
google.charts.setOnLoadCallback(drawBasic);

function drawBasic() {

var data = new google.visualization.DataTable();
data.addColumn('number', 'Day');
data.addColumn('number', 'Patients');

data.addRows([
"the part where im having a problem"

var options = {
hAxis: {
title: 'Day'
},
vAxis: {
title: 'Number of Patients'
}
};

var chart = new google.visualization.LineChart(document.getElementById('chart_div'));

chart.draw(data, options);
}


i got the code from the google docs and i just edited it a little to fit my situation

Answer Source

from what i can gather, all you need is the query, right? i'm assuming you have a method to send the query and receive the data.

in that case, the best and easiest solution would be a simple COUNT query w/ some grouping.

SELECT COUNT(pName)
FROM pPatient
WHERE DATE(pDate) > CURDATE() - 30

if you want them to be sorted by the month, day, and year, you can add this to the end. it'll list them by matching dates, using pDate as the argument to match them by:

GROUP BY DATE(pDate)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download