CODEWITHSUNDEEP
phpmysqldatetimeinsert
user2388643
user2388643

Reputation: 15

Inserting variable into MySQL datetime

I am getting the date and time from user input and then trying to insert the given date and time into a datetime column in MySQL. This it doesn't seem to be working. However, when I insert NOW() it works fine.

I have the variables: $year, $month, $date, $hour, and $minute.

And this is what I am doing:

    $i_date = "$year";
    $month= sprintf("%02s", $month); // These add a leading zero to numbers less than 10
    $i_date .= "$month";
    $date = sprintf("%02s", $date);
    $i_date .= "$date";
    $hour = sprintf("%02s", $hour);
    $i_date .= "$hour";
    $minute = sprintf("%02s", $minute);
    $i_date .= "$minute";
    $i_date .= "00"; // These are for seconds.

This gives the me the correct format for the date. I have checked it by echoing it out and then trying to manually insert into the database and it works.

But when I try to insert $i_date into the datetime column via PHP, it does not accept it.

How can I correct this?

Upvotes: 0

Views: 1658

Answers (3)

silkfire
silkfire

Reputation: 25945

I think you're missing the hyphens and the colons. Format should be:

Y-m-d H:i:s // PHP format

For example, the date time right now:

2013-06-03 21:09:00

And thus, with your code:

$i_date = sprintf('%d-%02d-%02d %02d:%02d:00', $year, $month, $date, $hour, $minute);

Upvotes: 2

Marc B
Marc B

Reputation: 360672

You could simplify all of those repeated sprintfs:

$mysql_date = sprintf('%04d-%02d-%02d %02d:%02d:%02d', $year, $month, $day, $hour, $minute, $second);

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191749

The correct format for the date is YYYY-MM-DD HH:II:SS, so you assuming $year is guaranteed to be four digits, you should update it to

 $i_date = "$year-$month-$date $hour:$minute:00";

after all of the 0 padding is done.

Upvotes: 0

Related Questions