Page 1 of 1

Adding times

Posted: Tue Feb 03, 2004 4:17 pm
by Tubby
Hello,
I'm currently writing a script which logs all the tracks I play on winamp into a mysql database, I've got all that working fine, but want to display some stats on a page, and the one I'm stuck on is total playing time.

In the database on each row with the track title, artist, album is the track length in the fashion mins:seconds in a column called length.

For the stats page how can I add together all the track times from the length column to give a total playing time in hours:mins:seconds, or even better, days:hours:mins:seconds?

Thanks.

Posted: Tue Feb 03, 2004 4:38 pm
by markl999
Not sure of your exact db setup etc..etc..but here's an example you could modify for your needs.

Code: Select all

<?php

$foo[] = '6:23';
$foo[] = '10:20';
$foo[] = '3:57';

$totalmins = 0;
$totalsecs = 0;
foreach($foo as $bar){
  list($mins, $secs) = explode(':', $bar);
  $totalmins += $mins;
  $totalsecs += $secs;
}

$total = $totalmins * 60 + $totalsecs;
$db = mysql_connect('host', 'user', 'pass');
$sql = "SELECT SEC_TO_TIME($total) AS totaltime";
$res = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($res);
echo $row['totaltime'];

?>

Posted: Tue Feb 03, 2004 6:34 pm
by pickle
I think the easiest way would be to extract all the song lengths, break them down into seconds, then build the time back up into minutes, hours, and days. markl999's solution seems pretty short though

Posted: Wed Feb 04, 2004 5:14 am
by Tubby
markl999 wrote:Not sure of your exact db setup etc..etc..but here's an example you could modify for your needs.

Code: Select all

<?php

$foo[] = '6:23';
$foo[] = '10:20';
$foo[] = '3:57';

$totalmins = 0;
$totalsecs = 0;
foreach($foo as $bar){
  list($mins, $secs) = explode(':', $bar);
  $totalmins += $mins;
  $totalsecs += $secs;
}

$total = $totalmins * 60 + $totalsecs;
$db = mysql_connect('host', 'user', 'pass');
$sql = "SELECT SEC_TO_TIME($total) AS totaltime";
$res = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($res);
echo $row['totaltime'];

?>
Thanks, that works nicely :D