Entering time in form MM:SS

[nathanpacker]

Hey, this is kind of a dumb problem, but I'm stumped by it. I wrote a little script to track my morning bike rides, and I have a time field to put in the time that I rode. Mind you, this is not the time of the day that I rode, but rather the number of minutes and seconds that my ride lasted.

But with my MySql back end, the only form I could think of was the "time" format, which forces it to HH:MM:SS. My rides rarely last longer than 45 minutes, so I just want to be able to enter the time in the field as MM:SS. Currently I'm just putting it in as "varchar". But eventually I would like to take the information in here and use it to make some graphs and I figure that would be easier if it was a in a time format.

Any idea how?

And lets say that some time in the future, I do ride for over an hour. Is there a way to make it so that I can enter it as MM:SS if it's under an hour, but have the option of entering HH:MM:SS if my ride is longer than an hour?

[ShawnCplus]

When you output the time use date_parse which breaks the time up into an associative array so you can display it in any way you would like but it is still stored the same in the database.
Quick note: even if you only enter MM:SS the database will add 00 to the hour so 45 minutes 5 seconds would become 00:45:05
Example:

$timeQuery = "SELECT time FROM rides";
$timeResult = mysql_fetch_row($timeQuery);
$parsedTime = date_parse($timeResult['time']);

echo $parsedDate['hour']." hour(s), ".$parsedDate['minute']." minute(s), and ".$parsedDate['second']." second(s).";

[nathanpacker]

Thanks for the reply. That works for the output, however, I tested inputting like you said, and if you simply input 45:05, it will go in the database as 45:05:00, or 45 hours, and 5 minutes. That's my problem.

[ShawnCplus]

Another way to do it would have three separate text boxes for hours, minutes and seconds respectively then when you actually submit it you can concatenate the three and make any empty boxes 00.

[seagram]

Though you say you never ride more than one hour, you should handle the case anyway.
First fill the minutes with your ride's duration, possibly overriding 60. Then split this value into hour(s) and remaining minutes. Then concatenate with seconds, and insert the result into database.

$ride_minutes_total = <some integer value>;
$ride_hours = (int)($ride_minutes_total/60);
$ride_minutes = ($ride_minutes_total % 60); // Using modulo operator to get remaining minutes
$ride_seconds = <some integer value>;
$ride_id = <some id>;
$ride_duration = sprintf('%02d:%02d:%02d', $ride_hour, $ride_minutes, $ride_seconds);

$timeInsertQuery = "insert INTO rides SET id ='$ride_id', duration='$ride_duration' ";
$result = mysql_query($timeInsertQuery) or die("Query failed");

(Note that IMHO, it is not a good idea to name a database column as 'time' since it is a column type keyword - though MySQL manual says it is allowed as unquoted identifiers for compatibility reasons)