使用php将错误的日期插入到mysql中

Question

I am using a form an php to capture a date, convert it to unixtime, then insert it into mysql. The problem is, when I try to insert Jan. 1, 2012, what gets inserted is Dec. 31, 2011. This must be a simple problem, but I could use some help.

Here's my form code:

<select name="mo">
   <?php 
   $arrMo = array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");
   foreach ($arrMo as $key => $value) {
       $option_val = intval($key) + 1;
    echo "<option value='" . $option_val . "'>" . $value . "</option>"; 
}

   ?>
   </select>  / 

   <select name="day">
   <?php 
   for ($b = 1; $b < 32; $b++) {
       echo "<option value='" . $b . "'>" . $b . "</option>
";
   }
   ?>
   </select>  / 
   <select name="year">
   <?php $thisyear = date("Y");
   $thisyear = intval($thisyear);
   for ($c = 0; $c < 6; $c++) {
       $newval = $thisyear - intval($c);
       echo "<option value='" . $newval . "'>" . $newval . "</option>
";
   }

   ?>
   </select>

Then I alter it in php:

$timestamp =  $_POST['year'] . "-" .  $_POST['mo'] . "-" . $_POST['day'];
$timestamp = strtotime("Y-m-d H:i:s", $timestamp);//turn it into Unix time
$mysqldatetime = date("Y-m-d H:i:s", $timestamp);

Then I try to insert it into the database:

$q_location = "Insert into markers(MarkerID, lat, lng, street, neighborhood, date) values(" . $nextLocation . ", '" . $_POST['latitude'] . "', '" . $_POST['longitude'] . "', '" . addslashes($_POST['address_public']) . "', '" .   addslashes($_POST['neighborhood']) . "', FROM_UNIXTIME(" . $mysqldatetime . "))";

Any idea what I'm doing wrong, please?

Answer 1

Remove FROM_UNIXTIME in your SQL query. If your date format is already Y-m-d H:i:s you can simply use this variable directly $mysqldatetime.

Otherwise, you would need to pass a timestamp to the FROM_UNIXTIME() function.

EDIT:

Actually your timestamp is wrong:

$timestamp =  $_POST['year'] . "-" .  $_POST['mo'] . "-" . $_POST['day'];
$timestamp = strtotime("Y-m-d H:i:s", $timestamp);//turn it into Unix time
$mysqldatetime = date("Y-m-d H:i:s", $timestamp);

Simply change it to:

$mysqldatetime = strtotime($_POST['year'] . "-" .  $_POST['mo'] . "-" . $_POST['day']);

Without the need for the $timestamp lines, and you should be able to leave your SQL query as is.