Looping through mysql query using php

[ruraldev]

Apologies if this should be in the mysql forum but hopefully this is the correct one.

 

I am trying to extract the sum of a column from my database for each financial year, at the moment I am doing each year as a separate recordset but I am certain there must be a more automated way!

 

The financial year is 1st April to 31st March each year and I need to create a variable which is a sum of each year, you'll see in my code below what I mean, any help gratefully appreciated as I am going to end up with decades of info in the db and am keen to get the code right now!

 

The relevant recordsets are 2,5,7.  db is readingID, date, reading

 

The code and page does work fine, it's just long winded!

 

<?php
mysql_select_db($database_wind, $wind);
$query_Recordset1 = "SELECT readingID, date_format(date,'%d/%m/%Y') as date, reading FROM solar ORDER BY readingID DESC LIMIT 5";
$Recordset1 = mysql_query($query_Recordset1, $wind) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

mysql_select_db($database_wind, $wind);
$query_Recordset2 = "SELECT SUM(reading) as year1total FROM `solar` WHERE date between '2010-04-01' and '2011-03-31'";
$Recordset2 = mysql_query($query_Recordset2, $wind) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

mysql_select_db($database_wind, $wind);
$query_Recordset3 = "SELECT * FROM solar ORDER BY readingID DESC Limit 29";
$Recordset3 = mysql_query($query_Recordset3) or die(mysql_error());

mysql_select_db($database_wind, $wind);
$query_Recordset4 = "SELECT readingID, YEAR(date) as yeardate, MONTHNAME(date) as monthdate, SUM(reading) as sumreading FROM `solar` Group by yeardate, monthdate Order by readingID ASC";
$Recordset4 = mysql_query($query_Recordset4) or die(mysql_error());

mysql_select_db($database_wind, $wind);
$query_Recordset5 = "SELECT SUM(reading) as year2total FROM `solar` WHERE date between '2011-04-01' and '2012-03-31'";
$Recordset5 = mysql_query($query_Recordset5, $wind) or die(mysql_error());
$row_Recordset5 = mysql_fetch_assoc($Recordset5);
$totalRows_Recordset5 = mysql_num_rows($Recordset5);

mysql_select_db($database_wind, $wind);
$query_Recordset6 = "SELECT datediff( Max(date), Min(date)) as DateDiff, Sum(reading) as LatestReading, date_format(MAX(date),'%d/%m/%Y') as LatestDate FROM solar";
$Recordset6 = mysql_query($query_Recordset6, $wind) or die(mysql_error());
$row_Recordset6 = mysql_fetch_assoc($Recordset6);
$totalRows_Recordset6 = mysql_num_rows($Recordset6);

mysql_select_db($database_wind, $wind);
$query_Recordset7 = "SELECT SUM(reading) as year3total FROM `solar` WHERE date between '2012-04-01' and '2013-03-31'";
$Recordset7 = mysql_query($query_Recordset7, $wind) or die(mysql_error());
$row_Recordset7 = mysql_fetch_assoc($Recordset7);
$totalRows_Recordset7 = mysql_num_rows($Recordset7);

$average = (int)(($row_Recordset6['LatestReading'])/($row_Recordset6['DateDiff']));

$income1 = $row_Recordset2['year1total']*0.428;
$income2 = $income1 + $row_Recordset5['year2total']*0.464;
$income = $income2 + $row_Recordset7['year3total']*0.464;

$saving = $row_Recordset2['year1total']*0.0675;
$saving = $saving + $row_Recordset5['year2total']*0.0675;
$saving = $saving + $row_Recordset7['year3total']*0.0675;

?>