Page 1 of 1

MySQL/Arrays/Performing Calcs

Posted: Fri Jun 13, 2008 10:51 am
by noctorum
Thus far I've been able to avoid having to do this as most of my involvement with MySQL has been dumps of desired data.

In this case however, I need to calculate and return results from a SQL query, and I'm not sure how to work with the array to get the data I want.

SQL query and fetch;

Code: Select all

 
<?php
$result = mysql_query("SELECT In_Out, Date, Time, Number, Duration, Line, Station, identifier 
FROM $table 
WHERE date>='$start_date' AND date<='$end_date' AND time>='$hours1' AND time<='$hours2' AND Line=6
");
 
$data = mysql_fetch_array($result)
 
?>
 
Now that I have the data in $data, how do I perform calculations on the entire array? Say I wanted to add up the column 'time' for every row as an example.

Thanks for any help

Re: MySQL/Arrays/Performing Calcs

Posted: Fri Jun 13, 2008 11:06 am
by paul_m
If I understand right, you want to add up every row's time that you got back.

You can try:

Code: Select all

 
$result = mysql_query("....")
$totalTime = 0;
while($row as mysql_fetch_array($result)){ //[will go through the every row..I'd assume you could use while ($row as $data) {..} like you have it currently but I'm not sure]
 $totalTime += $row['time'];
}
 
It works because it'll return false when it's done with the array.

Re: MySQL/Arrays/Performing Calcs

Posted: Fri Jun 13, 2008 11:11 am
by noctorum
Throws an error;

code:

Code: Select all

 
$result = mysql_query("SELECT In_Out, Date, Time, Number, Duration, Line, Station, identifier 
FROM $table 
WHERE date>='$start_date' AND date<='$end_date' AND time>='$hours1' AND time<='$hours2' AND Line=6
");
$totalTime = 0;
while($row as mysql_fetch_array($result))
{
 $totalTime += $row['time'];
}
 
echo $totalTime;
 
error:

Code: Select all

 
Parse error: syntax error, unexpected T_AS in phonequerynew.php on line 230
 
230 corresponds to the while loop declaration

Re: MySQL/Arrays/Performing Calcs

Posted: Fri Jun 13, 2008 11:16 am
by Eran
If they are simple calculations it might be better to perform them at the database level. Check out SQL functions - http://oreilly.com/catalog/sqlnut/chapter/ch04.html

Regarding the error - it should be an assignment not an 'as' operator:

Code: Select all

 
while($row = mysql_fetch_array($result)) {
...
}
 
I would also use mysql_fetch_assoc instead, so you could reference the columns by their names.

Re: MySQL/Arrays/Performing Calcs

Posted: Fri Jun 13, 2008 11:18 am
by RobertGonzalez
Do the calculations on the database server.