HTML form input to mysql TIME

Question

Hello,

I am having an add/edit form to update and add to database, and I was not sure what the best way is to input TIME type (HH:MM:SS). Should I use multiple html text inputs for HH, MM, SS? if so, is there a function that prepares the string for database input?

Basically what I'm trying to input is how many hours, minutes, seconds a specific task took to finish.

Can anyone point me in the right direction here?

I'm designing a website using Codeigniter (PHP).

Thanks

Let me go ahead and clarify what needs to happen a bit more... The user is required to enter data specific to sports more in particular to a players minutes and seconds played. I'm thinking of maybe simplifying it to only minutes. Perhaps this way input is only 1 thing. Then again my question is, what method would work to convert this "minute number" to the correct MYSQL TIME format?

Answer 1

just use now() function, if your DB is MYSQL is you want to save the current time

e.g:

UPDATE tbl SET timemodified = NOW()

and make sure that timemodified has a type of "time"

Ow sory miss that point. uhm maybe you need to have a start time in your DB, then after he/.she is finish his/her task. you must query on the DB the start time subtract it to the end time(your current time) then the result would be the time he/she performed the task

Answer 2

I wrote a helper to do something similar in an app I'm working on. Mine generates three dropdowns, hh, mm and am/pm, by calling built in form_dropdown helper. Once I get the data from the drop downs, I convert it 24hr format and then I just concatenate the strings into the right format for MySQL. Since it's a helper I can just call it from any view using form_time(). I can post it here if you think it would help to see it.

Dana

Answer 3

this checks for 2 numbers, then a ":" then 2 numbers, then a ":", then finally 2 numbers again:

$cleanTime = preg_match( '/(\d\d)\:(\d\d)\:(\d\d)/', $_POST[ 'NAME_OF_TIME_INPUT' ] );
if( !$cleanTime ){ /* ... error ... */ }

don't be scared of all the slashes, haha (I was at first when I used regexps).

replace NAME_OF_TIME_INPUT with the content of the name attribute on the <input on the <form page

i.e. if <input name="coolinput" /> then use $_POST[ 'coolinput' ]

Answer 4

This is one of the eternal struggles of (web) UI design, how to input time without driving the users nuts. What works for your specific case is something only you can decide, because it depends on the exact format/circumstances you need and your target audience.

As general guidelines I'd say:

• Don't do a free-form text field that requires a certain format, e.g. "Enter time (HH:MM:SS)", because it's too easy to mess up and will deny the users input or mess up the time if you do no validation.
• Try to avoid [0-23] [0-59] [0-59] dropdowns, since they can be quite a pain (click, scroll, click, click, scroll, click, click, scroll, click).

If ease of use is a high priority, as would be the case for public websites, maybe a Javascript enhanced timepicker is a good idea. Try not to use anything too fancy that nobody gets though (like dragging the hands on a clock).

A free-form, free-format text field might be the best idea. The user can just type in "3pm", "16:34" or "midnight". You may need to provide examples to get users started, otherwise they may feel lost. You can run this through strtotime on your end, but you may need to fill in the blanks and do a lot of validation.

Three short text fields may be a good idea if your audience is very keyboard focused and can be expected to tab through them in rapid order.

As for formatting it for SQL, however you receive the time input from the user, you should assemble it to a UNIX timestamp and format that timestamp for SQL:

date('Y-m-d H:i:s', $timestamp);

Answer 5

I have searched for some alternatives and solutions and I came up with this:

$min = 60;
$time[] = floor($min/60);
$time[] = $min%60;

And I used the following to convert to MySQL TIME format

INSERT INTO table (min) VALUES (MAKETIME($time[0], $time[1], 0))