ansaurus

Question

insert am pm from front side and store in time field of mysql

Answer 1

A:

use mysql function DATE_FORMAT with %p parameter

Haim Evgi 2010-01-18 13:49:23

Answer 2

+3 A:

To insert:

# replace first argument of STR_TO_DATE with value from PHP/frontend
TIME( STR_TO_DATE( '10:00 PM', '%h:%i %p' ) );

To select:

# replace first argument with your time field
TIME_FORMAT( '22:00:00', '%h:%i %p' );

EDIT:
I'll just go ahead and presume you use mysql lib functions.

// first sanitize the $_POST input
// also, make sure you use quotes to identify the $_POST keys
$open = mysql_real_escape_string( $_POST[ 'MondayOpen' ] );
$close = mysql_real_escape_string( $_POST[ 'MondayClose' ] );

// this is the query, which should work just fine.
$sql = '
    INSERT INTO
        `table_lib_hours`
    SET
        `day_name` = "Monday",
        `day_open_time` = TIME( STR_TO_DATE( "' . $open . '", "%h:%i %p" ) ),
        `day_close_time` = TIME( STR_TO_DATE( "' . $close . '", "%h:%i %p" ) )
    ';

$result = mysql_query( $sql );

Then to retrieve the values:

$sql = '
    SELECT
        `day_open_time`,
        `day_close_time`,
        TIME_FORMAT( `day_open_time`, "%h:%i %p" ) as day_open_time_formatted,
        TIME_FORMAT( `day_close_time`, "%h:%i %p" ) as day_close_time_formatted
    FROM
        `table_lib_hours`
    WHERE
        `day_name` = "Monday"
    ';

$resultset = mysql_query( $sql );

This will return a result set where the formatted data is in the *_formatted fields

EDIT:
Adjusted %m (month) to %i (minutes). A thank you to Donny for the well spotted slip up.

fireeyedboy 2010-01-18 14:03:18

`INSERT INTO table_lib_hours SET day_name='Monday', `day_open_time=TIME(STR_TO_DATE( '$_POST[MondayOpen]', '%h:%m %p' )),` `day_close_time=TIME(STR_TO_DATE( '$_POST[MondayClose]', '%h:%m %p' ))`display error`Column 'day_open_time' cannot be null`

diEcho 2010-01-30 08:41:25

First of: you shouldn't use $_POST directly in your queries... really. You should sanitize the values first. But to better help you: can you also show the PHP code where you execute this query? (Please update your question, rather then post a comment, if you will. Because that's easier to read. Thanks).

fireeyedboy 2010-01-30 09:18:58

`TIME( STR_TO_DATE( "' . $open . '", "%h:%m %p" ) )` gives parse error

diEcho 2010-01-30 10:31:52

it should be`TIME( STR_TO_DATE( '" . $open . "', '%h:%m %p' ) )`

diEcho 2010-01-30 10:53:12

when i run above codeit display error `day_close_time can not be null`

diEcho 2010-01-30 11:02:36

@I Like PHP: yes, if you use double quotes as the string delimiter for `$sql`. But as you can see in my example I use single quotes.

fireeyedboy 2010-01-30 11:03:43

@I Like PHP: Make sure you use appropriate quoting. So, If you are using double quotes around the `$sql` string, then you need single quotes as string delimiters around values you insert in MySQL and vice versa.

fireeyedboy 2010-01-30 11:06:52

The reason I use single quotes for PHP strings is that PHP parses variables in double quoted strings. I like to avoid this. I only even explicitly insert variables in PHP string by using the concatenation operator (the dot `.`).

fireeyedboy 2010-01-30 11:11:12

Correction, should be:`TIME_FORMAT( '22:00:00', '%h:%i %p' );` See here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Donny Kurnia 2010-01-31 19:00:46

@Donny: oops, you're right. I'll adjust it. Thank you for the correction.

fireeyedboy 2010-01-31 19:38:35

ansaurus

tags:

views:

answers:

insert am pm from front side and store in time field of mysql

related questions