PHP mysql insert date format

Question

Im using jQuery datepicker the format of the datepicker is this 08/25/2012

i have errors when inserting to my database it insert only 0000-00-00 00 00 00

my codes is

<?php
$id = $_POST['id'];
$name = $_POST['name'];
$date = $_POST['date'];
$sql = mysql_query( "INSERT INTO user_date VALUE( '', '$name', '$date')" ) or die ( mysql_error() );
echo 'insert successful';
?>

im sure my insert is correct....

User · Answer

Try Something like this    echo  The time is     date  2 50 20     d strtotime  3 00pm july 28 2014    echo  Created date is     date  d-m-y h i sa   d

User · Answer

Get a date object from the jquery date picker using

var myDate = $('element').datepicker('getDate')

For mysql the date needs to be in the proper format. One option which handles any timezone issues is to use moment.js

moment(myDate).format('YYYY-MM-DD HH:mm:ss')

User · Answer

You must make sure that the date format is YYYY-MM-DD on your jQuery output. I can see jQuery returns MM-DD-YYYY, which is not the valid MySQL date format and this is why it returns an error.

To convert it to the right one you could do this:

$dateFormated = split('/', $date);
$date = $dateFormated[2].'-'.$dateFormated[0].'-'.$dateFormated[1];

Then you will get formatted date that will be valid MySQL format, which is YYYY-MM-DD, i.e. 2012-08-25

I would also recommend using mysql_real_escape_string as you insert data into database to prevent SQL injections as a quick solution or better use PDO or MySQLi.

Your insert query using mysql_real_escape_string should rather look like this:

$sql = mysql_query( "INSERT INTO user_date VALUE( '', '" .mysql_real_escape_string($name). "', '" .mysql_real_escape_string($date). "'" ) or die ( mysql_error() );

User · Answer

As stated in Date and Time Literals      MySQL recognizes DATE values in these formats          As a string in either  YYYY-MM-DD  or  YY-MM-DD  format  A    relaxed    syntax is permitted  Any punctuation character may be used as the delimiter between date parts  For example   2012-12-31    2012 12 31    2012 12 31   and  2012 12 31  are equivalent    As a string with no delimiters in either  YYYYMMDD  or  YYMMDD  format  provided that the string makes sense as a date  For example   20070523  and  070523  are interpreted as  2007-05-23   but  071332  is illegal  it has nonsensical month and day parts  and becomes  0000-00-00     As a number in either YYYYMMDD or YYMMDD format  provided that the number makes sense as a date  For example  19830905 and 830905 are interpreted as  1983-09-05        Therefore  the string  08 25 2012  is not a valid MySQL date literal   You have four options  in some vague order of preference  without any further information of your requirements     Configure Datepicker to provide dates in a supported format using an altField together with its altFormat option    lt input type  hidden  id  actualDate  name  actualDate   gt        selector    datepicker       altField     actualDate      altFormat   yyyy-mm-dd        Or  if you re happy for users to see the date in YYYY-MM-DD format  simply set the dateFormat option instead       selector    datepicker       dateFormat   yyyy-mm-dd       Use MySQL s STR TO DATE   function to convert the string   INSERT INTO user date VALUES        name   STR TO DATE   date     m  d  Y     Convert the string received from jQuery into something that PHP understands as a date  such as a DateTime object    dt    DateTime  createFromFormat  m d Y     POST  date       and then either    obtain a suitable formatted string    date    dt- gt format  Y-m-d     obtain the UNIX timestamp    timestamp    dt- gt getTimestamp      which is then passed directly to MySQL s FROM UNIXTIME   function   INSERT INTO user date VALUES        name   FROM UNIXTIME  timestamp     Manually manipulate the string into a valid literal    parts   explode        POST  date      date      parts 2 - parts 0 - parts 1         Warning   Your code is vulnerable to SQL injection   You really should be using prepared statements  into which you pass your variables as parameters that do not get evaluated for SQL   If you don t know what I m talking about  or how to fix it  read the story of Bobby Tables   Also  as stated in the introduction to the PHP manual chapter on the mysql   functions      This extension is deprecated as of PHP 5 5 0  and is not recommended for writing new code as it will be removed in the future  Instead  either the mysqli or PDO MySQL extension should be used  See also the MySQL API Overview for further help while choosing a MySQL API   You appear to be using either a DATETIME or TIMESTAMP column for holding a date value  I recommend you consider using MySQL s DATE type instead   As explained in The DATE  DATETIME  and TIMESTAMP Types      The DATE type is used for values with a date part but no time part  MySQL retrieves and displays DATE values in  YYYY-MM-DD  format  The supported range is  1000-01-01  to  9999-12-31        The DATETIME type is used for values that contain both date and time parts  MySQL retrieves and displays DATETIME values in  YYYY-MM-DD HH MM SS  format  The supported range is  1000-01-01 00 00 00  to  9999-12-31 23 59 59        The TIMESTAMP data type is used for values that contain both date and time parts  TIMESTAMP has a range of  1970-01-01 00 00 01  UTC to  2038-01-19 03 14 07  UTC

User · Answer

HTML    lt div class  form-group  gt     lt label for  pt date  class  col-2 col-form-label  gt Date lt  label gt     lt input class  form-control  type  date  value  lt  php echo  date  Y-m-d      gt  id  pt date  name  pt date  gt   lt  div gt    SQL   pt date     POST  pt date      sql    INSERT INTO  table     pt date   VALUES     pt date

User · Answer

You should consider creating a timestamp from that date witk mktime()

eg:

$date = explode('/', $_POST['date']);
$time = mktime(0,0,0,$date[0],$date[1],$date[2]);
$mysqldate = date( 'Y-m-d H:i:s', $time );

User · Answer

First of all store  date   POST  your date field name     insert into   Your Table Name   values   date    other fields       You must contain date in single cote        I hope it is helps

User · Answer

date field           date  Y-m-d  strtotime   POST  date field       sql   mysql query  INSERT INTO user date  column name column name column name  VALUES     name  date field    or die  mysql error

User · Answer

The simplest method is   dateArray   explode        POST  date      date    dateArray 2   -   dateArray 0   -   dateArray 1     sql   mysql query  INSERT INTO user date  column column column  VALUES     name  date    or die  mysql error

[php] PHP mysql insert date format

The answer is

Examples related to php

Examples related to mysql

Examples related to date

Examples related to insert

Tags