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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
adrpoCommented:
// 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.