Count array records and find average

[wiggst3r]

Hi

 

I have a large database of approx 50,000 records. There's two fields that are datetime fields. What I've done so far is to get the time between each date into days format and print them to the screen, e.g.

 

In db:

2009-04-03 00:00:00    2009-04-04 00:00:00

2009-04-04 00:00:00    2009-04-05 00:00:00

2009-04-05 00:00:00    2009-04-06 00:00:00

 

In browser:

1

1

1

 

What I want to do is find an average based on the results.

 

So it would do:

1 + 1 + 1 / 3 (loop through each row and add it together -> store in variable and then count how many records there are and divide total difference in days by the total days)

 

So running the script will output:

 

Average time in days is: 1

 

My code is as follows:

 

<?php

require_once('config.php');
global $db;

$result = $db->db_query(" SELECT * FROM messages where server_time between '2008-07-23 00:00:00' and '2008-07-27 00:00:00' and entered_at IS NOT NULL ");

$output = $db->db_fetch($result);

$array = array();

while($one_row = $db->db_fetch($result))
{
        $date_redeemed = strtotime($one_row['entered_at']);
        $time_of_record = strtotime($one_row['server_time']);

        $difference_in_seconds = $date_redeemed - $time_of_record;

        $difference_in_days = $difference_in_seconds / 86400;

        echo round($difference_in_days) . '<br/>';

}

?>

 

Thanks

[rhodesa]

i think you can do this all with SQL (which will be faster)

 

but with PHP, just have 2 variables: $sum and $count:

<?php

require_once('config.php');
global $db;

$result = $db->db_query(" SELECT * FROM messages where server_time between '2008-07-23 00:00:00' and '2008-07-27 00:00:00' and entered_at IS NOT NULL ");

$output = $db->db_fetch($result);

$sum = $count = 0;

while($one_row = $db->db_fetch($result))
{
        $date_redeemed = strtotime($one_row['entered_at']);
        $time_of_record = strtotime($one_row['server_time']);

        $difference_in_seconds = $date_redeemed - $time_of_record;

        $difference_in_days = $difference_in_seconds / 86400;

        $sum += round($difference_in_days);
        $count++;
}
echo "Average: ".($sum/$count)."<br />";
?>

[wiggst3r]

Worked great!!!

 

Thanks!!