MySQL查询错误处理日期字段

Question

I've got a PHP/MySQL script that is yielding strange results on a date field. All along the process, my dates are fine until the very end. The final result has every entry in the date field as '0000-00-00'. I'm totally stuck and don't know what else to do. I can tell that this is an issue with PHP not interpreting this as a date, but I don't know how to fix it. Here is my code:

$sql = "CREATE TABLE temp_workouts (my_date date, sg_id int(11), loc_id int(11))";
$result = mysql_query($sql);
if (!$result) {
    $tag_success = "failure";
    $tag_message = mysql_error();
    echo encodeJSON($tag_success, $tag_message);
    die();
}

$sql = "SELECT * FROM my_table";
$result = mysql_query($sql);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $my_date = $row['my_date'];
    echo $my_date . " ";   //<--this output looks perfect
    $sql = "INSERT INTO temp_table (my_date) VALUES ($my_date)";
    $result2 = mysql_query($sql);
}
die();  

When I flip over to MyPHPAdmin and look at the table, the entire column my_date contains '0000-00-00'. How can I get PHP to recognize this as a 'Y-m-d' formatted date? Thanks. I appreciate any help.

Answer 1

Your immediate problem is that you don't use quotes around date values in your insert statement.

Change

$sql = "INSERT INTO temp_table (my_date) VALUES ($my_date)";

to

$sql = "INSERT INTO temp_table (my_date) VALUES ('$my_date')";
                                                 ^        ^

---

Now, you can just use INSERT ... SELECT syntax to achieve your goal in one go

INSERT INTO temp_table (my_date)
SELECT my_date
  FROM my_table

Therefore this part of your code

$sql = "SELECT * FROM my_table";
$result = mysql_query($sql);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $my_date = $row['my_date'];
    echo $my_date . " ";   //<--this output looks perfect
    $sql = "INSERT INTO temp_table (my_date) VALUES ($my_date)";
    $result2 = mysql_query($sql);
}

can be changed to

$sql = "INSERT INTO temp_table (my_date)
        SELECT my_date FROM my_table";
$result2 = mysql_query($sql);

On a side note: Consider switching to either PDO or MySQLi and use prepared statements.