11

I am trying to insert date and time into mysql datetime field. When a user select a date and time, it will generate two POST variables. I have searched internet but still not sure how to do it.

My code.

//date value is 05/25/2010
//time value is 10:00

$date=$_POST['date'];
$time=$_POST['time'];

$datetime=$date.$time

If I insert $datetime into mysql, the date appears to be 0000-00-00:00:00:00

I appreciate it if anyone could help me about this. Thanks.

FlyingCat
  • 14,036
  • 36
  • 119
  • 198

6 Answers6

7
$datetime = $_POST['date'] . ' ' . $_POST['time'] . ':00';
$datetime = mysql_real_escape_string($datetime);
$query = "INSERT INTO table(timestamp) VALUES ('$datetime')";

alternative solution that can handle more formats:

$datetime = $_POST['date'] . ' ' . $_POST['time'];
$datetime = mysql_real_escape_string($datetime);
$datetime = strtotime($datetime);
$datetime = date('Y-m-d H:i:s',$datetime);
$query = "INSERT INTO table(timestamp) VALUES ('$datetime')";
GSto
  • 41,512
  • 37
  • 133
  • 184
  • 1
    Because the first snippet contains an SQL injection vulnerability. One more -1 from me. :-P :-) – Notinlist May 28 '10 at 15:46
  • 2
    @Noninlist - code snippets from StackOverflow shouldn't be cut n paste verbatim. I addressed the problem at hand. but just for you, i added a call to mysql real escape string – GSto May 28 '10 at 16:02
4

Date must have format shown to you: 0000-00-00 00:00:00

So, you have to convert it. There are thousand questions about this conversion here on SO.
The shortest way is like

list($m,$d,$y) = explode("/",$_POST['date']);
$date = mysql_real_escape_string("$y-$m-$d ".$_POST['time']);
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
3

I think the datetime format looks like this:

YYYY-MM-DD HH:MM:SS

so you've got to format your $datetime to look like that. And in your query that needs to be encapsulated in quoation marks.

vicatcu
  • 5,407
  • 7
  • 41
  • 65
2
  • Either you transform the string to be in the YYYY-MM-DD HH:MM:SS format yourself,

  • or you use the str_to_date() function from MySQL.

    INSERT INTO table DATETIME values (str_to_date($date,"%m/%d/%Y %h:%i"))

UdayKiran Pulipati
  • 6,579
  • 7
  • 67
  • 92
Konerak
  • 39,272
  • 12
  • 98
  • 118
1

As far as I remember, by default, Mysql datetime should be in "yyyy-mm-dd hh:mm:ss" format.

a1ex07
  • 36,826
  • 12
  • 90
  • 103
0

I have added date and time in mysql db via API like this:

Call API

http://localhost/SendOrder.php?odate=20120323&otime=164545

  1. odate type is DATE
  2. otime type is TIME in DB.

It will store odate as 2012-03-23 and otime as 16:45:45.

Sruthi.user3337197
  • 1
  • 2