Creating an array from two columns of a Mysql database

[ajoo]

Hi all !

 

I wish to create a graph from data collected in two columns of a table. The two columns are Date(dd-mm-yyyy), AvgScore.

 

======================

|     Date          |  AvgScore  |

======================

| 26-02-2015   |       40        |

| 28-02-2015   |       45        |

| 01-03-2015   |       45        |
| 04-03-2015   |       46        |

| 05-03-2015   |       45        |      

| 06-03-2015   |       47        |

| 12-03-2015   |       47        |

| 18-03-2015   |       46        |

| 26-03-2015   |       45        |

======================

 

As can be seen there are gaps in the dates when the user came in to play. Those dates have to be accounted for.  The scores on the dates when the user did not come in to play will be counted as zero. Therefore I need to create an array which will have all dates from 26-02-2015 till 26-03-2015 with AvgScore as '0'(zero) on days when the user did not come to play. 

 

Further the data needs to be presented to the charting API as Array("26-02-2015"=>40, "28-02-2015"=>45, "01-03-2015"=>45, ...);

 

I would be glad if someone can tell what would be the simplest and possibly the fastest way to achieve this?  

Thanks !

 

 

 

[Barand]

populate an array with the required dates as keys and zero values. Then you add the values from your data into the the array using the date keys

$dt1 = new DateTime('2015-02-26');
$dt2 = new DateTime('2015-03-27');
$di = new DateInterval('P1D');
$dp = new DatePeriod($dt1, $di, $dt2);

$data = array();
foreach ($dp as $d) {
    $data[$d] = 0;
}

[ajoo]

Hi Guru Barand, 

 

Thanks for the reply. Is there a function to then merge the corresponding values derived from mySql into this array of all date keys with values 0? Or would I need to use a for loop again and replace each key- value pair from the mysql result into this all dates array?

 

Thank you very much.

[Barand]

You would do it as you process the query result

// code from above here, then

while ($row = $res->fetch_assoc()) {
    $data[$row['date']] = $row['value'];
}

[ajoo]

Thanks guru Barand,

 

Great solution !