KoalatyCode KoalatyCode - 5 months ago 11
SQL Question

How to correctly format a PHP foreach loop?

I have a PHP PDO query, it selects the year, month and day from the database. It also counts the number of rows that each day has:

SELECT
dateYear, dateMonth, dateDay,
count(dateDay) AS count_days
FROM just_ink
WHERE dateMonth = :month AND dateYear = :year AND deleted = 0
GROUP BY dateYear, dateMonth, dateDay


When echoing the results:

foreach ($result as $subResult) {
foreach ($subResult as $row) {
$year = $row['dateYear'];
$month = $row['dateMonth'];
$day = $row['dateDay'];
$count = $row['count_days'];
echo $month . " " . $day . " " . $year . " " . $count . " lines, ";
}
}


This returns a value like:

June 7 2016 3 lines, June 8 2016 1 lines,


This means, June 7th has 3 forms and June 8th has 1 form.

Now for the formatting, I am using Highcharts basic line chart. The data needs to be formatted in this way:

<script>
$(function () {
$('#container').highcharts({
title: {
text: 'Monthly New Forms',
x: -20 //center
},
xAxis: {
title: {
text: 'Day of the Month'
},
categories: [
'1', '2', '3', '4', '5',
'6', '7', '8', '9', '10',
'11', '12', '13', '14', '15',
'16', '17', '18', '19', '20',
'21', '22', '23', '24', '25',
'26', '27', '28', '29', '30',
'31'
]
},
yAxis: {
title: {
text: 'Month of June'
},
plotLines: [{
value: 0,
width: 1,
color: '#808080'
}]
},
tooltip: {

},
legend: {
layout: 'vertical',
align: 'right',
verticalAlign: 'middle',
borderWidth: 0
},
series: [{
name: 'Number of New Forms Per Day',
data: [1, 2, 3, 4, 5, 6]
}]
});
});
</script>


Where the categories are the x-axis titles, and the series data is the frequency of each day. So If I want to represent

June 7 2016 3 lines, June 8 2016 1 lines,


The series data would need to look like

0, 0, 0, 0, 0, 0, 3, 1, 0, etc.


Any ideas?

Answer

First create an array with elements for every day of the month:

$counts = array_fill(1, 31, 0);

Then in your loop, fill in the entry for the day:

foreach ($subresult as $row) {
    $day = $row['dateDay'];
    $counts[$day] = $row['count_days'];
}
$counts = array_values($counts); // because json_encode treats it as an object if indexes don't start at 0
echo json_encode($counts);

I'm not sure why you're selecting dateYear and dateMonth, since those are the inputs to the query. You don't need to set those variables in the loop, since they'll always be the same thing.