Write value into correct month

Question

How can I write a value into correct month which is located in an html table? Sorry for my bad English.

$mesi = array("January", "February", "March", "April", "May", "June",
               "July", "August", "September", "October", "November", "December");

$array = $class->startEndDate('12'); // chiave arrays
echo '<table class="table table-bordered">';
echo '<thead>';
echo '<th class="text-center">Country</th>';
foreach ($mesi as $mese) {
    echo '<th class="text-center">' . $mese . '</th>';
}
echo '</thead>';
echo '<tbody>';
echo '<tr>';
$query = "SELECT * FROM countries";
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
    echo '<td class = "text-center">' . $row['country_name'] . '</td>';

    $sql = "select sum(quantity), country_id, 
                   concat(year(date), '_', month(date)) as meseAnno 
            from subscription 
            where country_id = " . $row['country_id'] . 
            " group by meseAnno";

    $res = mysql_query($sql);
    while ($rows = mysql_fetch_assoc($res)) {
        //the code here!
    }
    echo '</tr>';
}
echo '</tbody>';
echo '</table>';
?>

With the second query I retrieve the subscription for country with id country_id.

Result of second query:

The column meseAnno of the first row says 2015_4, so April, but the value 7 is inserted in January. Why?

Thanks in advanced.

Answer 1

The problem is that months are 12, but your query result can be have less than 12 rows. You can change the SQL query to obtain a coherent result, or — without modify the query — try with something like this:

(...)
$res = mysql_query( $sql );

$tableLine = array_fill( 1, 12, '<td>&nbsp;</td>' );
while( $row = mysql_fetch_assoc( $res ) )
{
    $month = preg_replace( '/^\d+_(\d+)$/', '\1', $row['meseAnno'] );
    $tableLine[ $month ] = "<td>{ YOUR MONTH VALUE HERE }</td>";
}
echo implode( '', $tableLine );

echo '</tr>';
(...)

First, you fill an array with 12 empty <td>, then — inside your while() loop — replace only matched months. At the end, you echo imploded array.

Answer 2

Using Fusion3k's answer and applying the idea that if you SELECT the Year and Month from the database as individual columns you will actualy have the index to his tableLine array given to you in an easily usable way, you could do this.

$sql = "select sum(quantity), country_id, 
               YEAR(`date`) as yr, MONTH(`date`) as mth,
               concat(year(date), '_', month(date)) as meseAnno 
        from subscription 
        where country_id = {$row['country_id']} 
        group by meseAnno 
        ORDER BY mth";

$res = mysql_query( $sql );

$tableLine = array_fill( 1, 12, '<td>&nbsp;</td>' );

while( $row = mysql_fetch_assoc( $res ) ) {
    $t = sprintf('%s_%s', $row['yr'], $row['mth']);
    $tableLine[ $row['mth'] ] = "<td>$t</td>";
}

echo implode( '', $tableLine );

echo '</tr>';

This would work assuming you added a yr = 2016 to the search criteria. If you want this report across multiple years there is a little more to do.

And of course I have to add :

Please dont use the mysql_ database extension, it is deprecated (gone for ever in PHP7) Especially if you are just learning PHP, spend your energies learning the PDO or mysqli_ database extensions, and here is some help to decide which to use