Calculate timecode (00:00:00.00) from mySQL with PHP

Hi,

I am trying to calculate video timecode (00:27:35.01) taken from mySQL with PHP.
Can someone help me.

Example:

(hh:mm:ss.ms)

00:25:25.30
00:24:10.25
etc....
=
00:49:35.55


thanks

- hans
Hans Inge HagenOwner and programmerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
adrpoConnect With a Mentor Commented:
// fetch duration from the DB.

list($hours, $minutes, $seconds,$fractions) =
  sscanf($duration, "%d:%d:%d:%d");

$totalDurationSeconds = $hours * 3600 + $minutes * 60 + $seconds +
                                        doubleval($fractions) / 100.0;
$totalDuratioHours = doubleval($totalDurationSeconds) / 3600.0;

Cheers,
za-k/
0
 
adrpoCommented:

Your question is rather vague.

What do you take out of MySQL? How that looks like?
Is it a timestamp? It is a date? It is a string and if so how does it looks like?
What do you want that to be converted to.

You need to say:
MySQL->read->READ_DATA which looks like (explain how it looks like)
READ_DATA should be translated to video timecode

Cheers,
za-k/
0
 
Hans Inge HagenOwner and programmerAuthor Commented:
Hi,

Thanks. Well, the data is stored as tinytext in mySQL. The label is "duration" (the length of a video) and the value is based on the time code of the video (HH:MM:SS.FF) as in 00:32:45.27

I need to add all of the "duration" values together to get a total time of all the videos.

-hans
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Hans Inge HagenOwner and programmerAuthor Commented:
Some more problems, but thanks for the help! I got closer to a solution. Can you help me with some of the code?



$sql2 =mysql_query("SELECT * FROM video where active = '1' and cid <> '1010' and cid <> '1000'") or die ( mysql_error ()); 
 
$nr2 = $totalDurationSeconds;
 
while($rowG =mysql_fetch_array ($sql2)) { 
 
list($hours, $minutes, $seconds,$fractions) = sscanf($rowG['duration'], "%d:%d:%d:%d");
$totalDurationSeconds = $hours * 3600 + $minutes * 60 + $seconds + doubleval($fractions) / 100.0;
$totalDuratioHours = doubleval($totalDurationSeconds) / 3600.0;
 
 
echo $totalDurationSeconds;
 
}
 
//echo $totalDurationSeconds;
 
//echo "<br>";
 
}
 
//echo "$totalDurationSeconds sek - $totalDuratioHours hours - (".$rowG['duration'].")";
?>

Open in new window

0
 
adrpoCommented:

Hi again,

I don't see any problem with the code.
What errors or problems are you getting?

Cheers,
za-k/
0
 
Hans Inge HagenOwner and programmerAuthor Commented:
It converts the time code to seconds nicely, but I would like to get the total seconds of the query result.

Example:

Now the query result gives:

758
567
3456

..and I need it to calculate the total to be:

4781

Then I can convert it to minuttes or hours ..




0
 
adrpoCommented:

Ahaaa, ok, then have a look below....
Hopefully I haven't made any math mistakes :))
$sql2 =mysql_query("SELECT * FROM video where active = '1' and cid <> '1010' and cid <> '1000'") or die ( mysql_error ()); 
 
$nr2 = $totalDurationSeconds;
$grandTotalSeconds = 0.0;
$grandTotalHours = 0.0;
 
while($rowG =mysql_fetch_array ($sql2)) { 
 
list($hours, $minutes, $seconds,$fractions) = sscanf($rowG['duration'], "%d:%d:%d:%d");
$totalDurationSeconds = $hours * 3600 + $minutes * 60 + $seconds + doubleval($fractions) / 100.0;
$totalDuratioHours = doubleval($totalDurationSeconds) / 3600.0;
 
// cumulate the durations
$grandTotalSeconds = $grandTotalSeconds + $totalDurationSeconds;
$grandTotalHours = $grandTotalHourse + $totalDurationHours;
 
echo $totalDurationSeconds;
 
}
 
echo $gradTotalSeconds;
echo $gradTotalHours;
 
 
//echo "<br>";
 
}
 
// Now try break the $grandTotalSecons into video time code
// Basically take the numbers after the decimal point for every FF, SS, MM, HH and multiply accordingly
$gtFF = intval(100 * ($grandTotalSeconds - intval($grandTotalSeconds))); // grand total FF 
$minutes = doubleval(intval($grandTotalSeconds))/60.0; 
$gtSS = intval(60 * ($minutes - intval($minutes))); // grand total SS
$hours = doubleval(intval($minutes))/60.0;
$gtMM = intval(60 * ($hours - intval($hours))); // grand total MM
$gtHH = intval($hours); // grand total HH
 
echo "$grandTotalSeconds sek - $grandTotalHours hours - ($gtHH:$gtMM:$gtSS:$gtFF)";
?>

Open in new window

0
All Courses

From novice to tech pro — start learning today.