wiggst3r Posted April 8, 2009 Share Posted April 8, 2009 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 Quote Link to comment Share on other sites More sharing options...
rhodesa Posted April 8, 2009 Share Posted April 8, 2009 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 />"; ?> Quote Link to comment Share on other sites More sharing options...
wiggst3r Posted April 8, 2009 Author Share Posted April 8, 2009 Worked great!!! Thanks!! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.