[RESOLVED] Sending time from form input field to MySQL

Dereksdontrun

Im having problems with MySQL time....the expected format for a time field is 00:00:00...However in my form, if the time field sends it in this format it causes a SQL error

in order to insert it to the database i have to send it as a 4 or 6 digit integer, if I look in the database the integer is stored in 00:00:00 format, if I return this time back to prepopulate an update form it is returned in 00:00:00 format, but again will not let me resubmit it in that format.

How do I submit time from an input form in hh:mm format rather than an integer.???

New to this so appreciate the help..thanks

bradgrafelman

What is the exact value you attempted to store in that column, and what was the MySQL error message?

Dereksdontrun

Thanks for responding...

heres an example

if i input 123456 it is successfully stored in the database as 12:34:56

However if i input 12:34:56 (the format MySQL should be expecting) i get an error

'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':34:56 WHERE noteid = 43' at line 1'

it happens on insert and on update. I have no problems with the date.
the SQL statement i am executing for update is

     		$UpdateSQL1 = sprintf("UPDATE notes SET note_date = %s, note_time = %s WHERE noteid = %s",
		        GetSQLValueString($_POST['note_date'], "date"),	
				GetSQLValueString($_POST['note_time'], "time"),		
				GetSQLValueString($noteid_selectNotes, "int"));

The field note_time is a time type

ideally i want to be able to enter 12:34 i dont need to enter seconds

Thanks

bradgrafelman

Can you show us the definition of the GetSQLValueString() function? I'm betting it doesn't have a case for the second parameter to be "time"; you'll probably just need to use "sting" instead.

Notice in the SQL error message that there is no single quote after the ':56' in the example value, thus you've likely got string data that hasn't been surrounded with quotes. This should be apparent if you also output/log the SQL query string itself when an error occurs.

Dereksdontrun

GetSQLValueString() function is a custom Dreamweaver function.....you are right it doesnt recognise time only date.....well that gets me a little closer....Many thanks for your help will mark as resolved🙂