Besmir Sadiku Besmir Sadiku - 1 month ago 8
MySQL Question

Using associative arrays to display records from mysql into table dynamically

I'm trying to build a table like so dynamically from query

Here is a query


SELECT MONTH(c.pat_added_date) AS month, COUNT(a.ana_id) as total,
YEAR(c.pat_added_date) AS year FROM lab_patient_analysis AS b

LEFT JOIN lab_analysis AS a
ON a.ana_id = b.ana_id

LEFT JOIN lab_patients AS c
ON c.pat_id = b.pat_id

WHERE
a.ana_id = '3' AND
YEAR(c.pat_added_date) BETWEEN '2012' AND '2013'
GROUP BY MONTH(c.pat_added_date)
ORDER BY YEAR(c.pat_added_date), MONTH(c.pat_added_date)



and here is what query returns

month | total | year
1 13 2012
7 9 2012
8 33 2012
3 21 2013
6 8 2013
....


here is a php function I've used for arrays

function array_set(&$a, $path, $value) {
if(!is_array($path))
$path = explode($path[0], substr($path, 1));
$key = array_pop($path);
foreach($path as $k) {
if(!isset($a[$k]))
$a[$k] = array();
$a = &$a[$k];
}
$a[$key ? $key : count($a)] = $value;
}


and the point where I'm stuck

$yr = null;
while($row ... ))
{
if($yr != $row['year']) {
$yr = $row['year'];
array_set($array, array($row['year'], $row['month']), $row['total']);
echo '</tr></tr>';
echo '<th scope="row">'.$row['year'].'</th>';
}

echo '<td>'.$row['total'];
}


arrays returned

Array
(
[2012] => Array
(
[1] => 13
[7] => 9
[8] => 33
)

[2013] => Array
(
[3] => 21
[6] => 8
)

)


And I would like to produce the following table

Jan| Feb| Mar | Apr | May | Jun | Jul | Sep | Oct | Nov | Dec
2012 13 0 0 0 0 0 9 33 0 0 0
2013 0 0 21 0 0 8 0 0 0 0 0


Hope that makes sense

Answer

I've managed to get this out of it:

<?php

// Build source array.. You should already have this layout by the looks of it.

$array = array('2012' => array("1" => 13, "7" => 9, "8" => 33),
                '2013' => array("3" => 21, "6" => 8));

// Loop over every year.

foreach ($array as $year => $values) {

        for ($i = 1; $i <= 12; $i++) { // Loop through months 1 .. 12

                if (!$values[$i]) { // If there is no value for this month, pad it with 0 (Note string "0", else it leaves it empty)
                        $array[$year][$i] = "0";
                }
        }

        // Sort it out, probably won't need this actually, but it doesn't hurt to have it.
        ksort($array[$year], SORT_NATURAL);
}

// Print out the array 
var_dump($array);

I get this output:

array(2) {
  [2012]=>
  array(12) {
    [1]=>
    int(13)
    [2]=>
    string(1) "0"
    [3]=>
    string(1) "0"
    [4]=>
    string(1) "0"
    [5]=>
    string(1) "0"
    [6]=>
    string(1) "0"
    [7]=>
    int(9)
    [8]=>
    int(33)
    [9]=>
    string(1) "0"
    [10]=>
    string(1) "0"
    [11]=>
    string(1) "0"
    [12]=>
    string(1) "0"
  }
  [2013]=>
  array(12) {
    [1]=>
    string(1) "0"
    [2]=>
    string(1) "0"
    [3]=>
    int(21)
    [4]=>
    string(1) "0"
    [5]=>
    string(1) "0"
    [6]=>
    int(8)
    [7]=>
    string(1) "0"
    [8]=>
    string(1) "0"
    [9]=>
    string(1) "0"
    [10]=>
    string(1) "0"
    [11]=>
    string(1) "0"
    [12]=>
    string(1) "0"
  }
}