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:

// 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!

Why should we use VIM? Organise database with MySQL group by


Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now