Consumption Calculation

[JS=Freak]

Hey everyone,

I'm pulling information from two tables into one. This is done to match up the searched reference number from the devices table to its channel in the datalog table. This is my latest query:


$sql = "SELECT Log_Date, Log_Reading, Channel, Device_Code, Zone_Code FROM datalog
WHERE Zone_Code = '".$_REQUEST['zone']."'
AND Log_Date BETWEEN '".$_REQUEST['day_from']."' AND '".$_REQUEST['day_to']."'
AND (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan1_Reference='".$_REQUEST['reference']."') AND Channel=1)
OR (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan2_Reference='".$_REQUEST['reference']."') AND Channel=2)
OR (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan3_Reference='".$_REQUEST['reference']."') AND Channel=3)
OR (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan4_Reference='".$_REQUEST['reference']."') AND Channel=4)
ORDER BY Channel, Log_Date";
$result = mysql_query($sql);
$rs = mysql_fetch_array($result);


This returns data that I put into an HTML table using php and it looks like this:

Log_Date Log_Reading Channel
12 Jan 39 1
13 Jan 54 1
14 Jan 59 1
15 Jan 63 1
16 Jan 83 1

My client wants to know how much water was consumed from one reading to the next, ie. I want another field for consumption so the above HTML table must look like this:

Log_Date Log_Reading Channel Consumption
12 Jan 39 1 ----
13 Jan 54 1 15
14 Jan 59 1 5
15 Jan 63 1 4
16 Jan 83 1 20

I have been able to do this using PHP and a while loop but I'm having problems now using a PHP graph because I can't have it within the while loop. I need the graph to pull it's information from the SQL query.

Would it be possible to work out the consumption using mySQL? Is there anybody else who can help?


Thanks!

Karen

[Scriptage]

Yes depending on the database... here's a MySQL example: http://www.java2s.com/Code/SQL/Math/DocalculationwithSUM.htm

[JS=Freak]

Hi Scriptage,

Thanks for the reply. I had a look at the page but this is not quite what I'm looking for. To explain a bit further, the consumption is not a calculation as such. I need the last value in the array and then the previous value in that same array must be subtracted from it. Then the previous value and it's previous value must be substracted, ie.

ID Reading
1 35
2 42
3 52
4 58

So, 58 - 52 equals 6 which is the consumption for ID 4. Then 52 - 42 equals 10 for the consumption of ID 3, etc.

I know somehow this must be done with the array but I don't know how. Can you help me any further?


Thanks!

Karen

[JS=Freak]

Can anyone please help??

[Scriptage]

You would need to use a programming language for this... is that PHP you are using?

[Scriptage]

Maybe do something like this:


$consumption = array();

for($i=sizeof($rs)-1; $i>0; $i--){

$consumption[$i] = $rs[$i] - $rs[$i-1];

}

$consumption[0] = "However you work that out";

[JS=Freak]

Hey Scriptage,

Thanks for the reply. I can't seem to figure out how to use your coding. I found the following code on the web and just adapted it to my query. This just returns 0:

// Using MYSQL_FETCH_ROW()
// =======================

// we want to build an array of data from the CAT_TITLE column in
// our TBL_CATEGORY table.
$sql_test = "SELECT Log_Date, Log_Reading, Channel, Device_Code, Zone_Code FROM datalog
WHERE Zone_Code = '".$_REQUEST['zone']."'
AND Log_Date BETWEEN '".$_REQUEST['day_from']."' AND '".$_REQUEST['day_to']."'
AND (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan1_Reference='".$_REQUEST['reference']."') AND Channel=1)
OR (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan2_Reference='".$_REQUEST['reference']."') AND Channel=2)
OR (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan3_Reference='".$_REQUEST['reference']."') AND Channel=3)
OR (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan4_Reference='".$_REQUEST['reference']."') AND Channel=4)
ORDER BY Channel, Log_Date";
$result_test = mysql_query( $sql_test);
while( $row=mysql_fetch_assoc($result_test) )
{
$readings[] = $row['Log_Reading'];
// do stuff with other column
// data if we want
}
mysql_free_result( $result_test );



// Using our $CAT_TITLES array
// ===========================
$prev = 0;
echo "<p>\n";
foreach( $readings as $cur )
{

$cur = $prev;
$cur = $cur - $prev;


echo $cur."<br />\n";

}
echo "</p>\n";

What am I doing wrong here? This is still in an attempt to work out consumption, ie. row 5 - row 4, row 4 - row 3, etc.



Thanks!

Karen

[Scriptage]

$sql = "SELECT Log_Date, Log_Reading, Channel, Device_Code, Zone_Code FROM datalog
WHERE Zone_Code = '".$_REQUEST['zone']."'
AND Log_Date BETWEEN '".$_REQUEST['day_from']."' AND '".$_REQUEST['day_to']."'
AND (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan1_Reference='".$_REQUEST['reference']."') AND Channel=1)
OR (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan2_Reference='".$_REQUEST['reference']."') AND Channel=2)
OR (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan3_Reference='".$_REQUEST['reference']."') AND Channel=3)
OR (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan4_Reference='".$_REQUEST['reference']."') AND Channel=4)
ORDER BY Channel, Log_Date";
$result = mysql_query($sql);
$rs = mysql_fetch_array($result);

$consumption = array();

for($i=sizeof($rs)-1; $i>0; $i--){

$consumption[$i] = $rs[$i] - $rs[$i-1];

}

$consumption[0] = "However you work that out";

// The $consumption array now contains the consumption
echo $consumption[3]; // Print the consumption of device three