I'm trying to visualize patterns in my database year-over-year. My DB is MySQL, my server-side language is PHP and the data visualizer is Morris.js.
I've exported my data year by year and have been trying to merge this data using PHP, but the order of efficiency is terrible and unintuitive, so I'm looking to merge columns in MySQL rather than after the fact in PHP.
I'm pulling out the data so that within each year January 7 represents Jan 1 + Jan 2 + .. + Jan 7
and December 31 represents Jan 1 + .. + Mar 25 + .. + Dec 31
. This is all within one single year, so that the column 2012 is cumulative only for 2012.
For 2014, the data looks like,
+--------+-------+
| date | books |
+--------+-------+
| Jan-01 | 17 |
| Jan-02 | 40 |
| Jan-03 | 99 |
| Jan-04 | 164 |
| Jan-05 | 307 |
| Jan-06 | 527 |
| Jan-07 | 744 |
| Jan-08 | 866 |
| Jan-09 | 941 |
| Jan-10 | 990 |
| Jan-11 | 1016 |
| Jan-12 | 1030 |
| Jan-13 | 1082 |
+--------+-------+
Right now I'm pulling out the data like so,
<?php
// the actual code includes data for 2010-2014
$sql2014Cumulative = "SELECT DATE_FORMAT(b.`date_added`, '%b-%d') AS date, COUNT(*) AS books
"
. "FROM (SELECT DISTINCT date(`date_added`) `date_added` FROM `books` WHERE YEAR(`date_added`) = 2014) b
"
. "JOIN `books` b2 ON b.`date_added` >= date(b2.`date_added`)
"
. "WHERE YEAR(b2.`date_added`) = 2014
"
. "GROUP BY b.`date_added`
"
. "ORDER BY b.`date_added` ASC";
$sql2013Cumulative = "SELECT DATE_FORMAT(b.`date_added`, '%b-%d') AS date, COUNT(*) AS books
"
. "FROM (SELECT DISTINCT date(`date_added`) `date_added` FROM `books` WHERE YEAR(`date_added`) = 2013) b
"
. "JOIN `books` b2 ON b.`date_added` >= date(b2.`date_added`)
"
. "WHERE YEAR(b2.`date_added`) = 2013
"
. "GROUP BY b.`date_added`
"
. "ORDER BY b.`date_added` ASC";
$result2014Cumulative = $mysqli->query($sql2014Cumulative);
$result2013Cumulative = $mysqli->query($sql2013Cumulative);
while($row2014Cumulative = $result2014Cumulative->fetch_array(MYSQLI_ASSOC))
{
$rows2014Cumulative[] = array('date' => $row2014Cumulative['date'], '2014' => $row2014Cumulative['books']);
}
while($row2013Cumulative = $result2013Cumulative->fetch_array(MYSQLI_ASSOC))
{
$rows2013Cumulative[] = array('date' => $row2013Cumulative['date'], '2013' => $row2013Cumulative['books']);
}
$mergedDataCumulative = array_replace_recursive($rows2013Cumulative, $rows2014Cumulative);
?>
<script>var booksYearOverYearCumulative = <?php echo json_encode($mergedDataCumulative); ?>;</script>
...
On a schema that looks like this,
+---------------+------------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(10) unsigned | NO | MUL | NULL | |
| course_code | varchar(9) | NO | | NULL | |
| for_sale | tinyint(1) | NO | | NULL | |
| date_added | datetime | NO | | NULL | |
| date_removed | datetime | NO | | 0000-00-00 00:00:00 | |
| date_modified | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------+------------------+------+-----+---------------------+-----------------------------+
Then I'm taking each set of data from each year and merging them into an array and exporting into JSON for Morris.js. This is the tedious and messy part.
What would be easier is if this was all done in MySQL and the data was exported in one table and was displayed as such,
+--------+-------+-------+-------+
| date | 2014 | 2013 | 2012 |
+--------+-------+-------+-------+
| Jan-01 | 17 | 12 | 0 |
| Jan-02 | 40 | 40 | 12 |
| Jan-03 | 99 | 102 | 18 |
| Jan-04 | 164 | 136 | 27 |
| Jan-05 | 307 | 144 | 45 |
| Jan-06 | 527 | 504 | 48 |
| Jan-07 | 744 | 893 | 189 |
| Jan-08 | 866 | 1002 | 567 |
| Jan-09 | 941 | 1100 | 890 |
| Jan-10 | 990 | 1430 | 1054 |
| Jan-11 | 1016 | 1435 | 1278 |
| Jan-12 | 1030 | 1545 | 1575 |
| Jan-13 | 1082 | 1604 | 1897 |
+--------+-------+-------+-------+
So rather than merging all the data using PHP, does anyone know how I can group cumulative data by day within each year and export it in one table?