Organise database with MySQL group by 2

Hello everyone, it’s Ray!

Today I’m going to share with you the further usage about how to manipulate database with group by, and inserting refined data into a new table.

First, let’s start from the final image got on yesterday. What if we need the total rainfall of months, or years?


Please take a look on code below:

<?php
// The year(date) and month(date) after select mean what data we want,
// and the second year and month after braces means the name of the
// column on shown data. Sum means to total all the of values of the
// column within braces followed by, and the second rainfall means 
// the name of the column on shown data. Use "group by" to make shown
// data grouped by month and year, and "order by" makes the data
// arranged in ascending order.


$selectQuery = 'SELECT year(date) year, month(date) month,
 sum(rainfall) rainfall from rainfall_by_date group by month(date),
  year(date) order by year(date) asc, month(date) asc;';

// Make a select request to the database
$selectResult = mysqli_query($dbc, $selectQuery);

// Use while loop to repeat select request until all of the arrays
// in $selectResult object are taken
while ($selectRow = mysqli_fetch_array($selectResult))
{
    // Insert the data taken from table rainfall_by_date
    // into the table rainfall_by_month
    $insertQuery = 'INSERT INTO rainfall_by_month (year, month, rainfall) VALUES("' . $selectRow['year'] . '", "' . $selectRow['month'] . '", "' . $selectRow['rainfall'] . '")';

    // Make an insert request.
    $insertResult = mysqli_query($dbc, $insertQuery);
}

After executing the script above, you will be able to get the new table with refined data, as table below:


It’s my sharing today, see you guys tomorrow!