CODEWITHSUNDEEP
phpmysqldate
AndroidDev
AndroidDev

Reputation: 21247

MySQL query mishandling date field

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.

Upvotes: 0

Views: 73

Answers (3)

peterm
peterm

Reputation: 92845

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.

Upvotes: 1

Ikong
Ikong

Reputation: 2570

Try this one...This will re-convert it to date, and then save..

    $dt = strtotime($row['my_date']);
    $date = date("Y-m-d",$dt);
    $sql = "INSERT INTO temp_table (my_date) VALUES ({$date})";

Upvotes: 0

spencer7593
spencer7593

Reputation: 108510

I suspect the issue is that you haven't enclosed a string literal in single quotes:

 INSERT INTO temp_table (my_date) VALUES ('$my_date')
                                          ^---     ^--- string literals in single quotes

Otherwise, the statement is probably something like:

 ... VALUES (2013-08-22)

MySQL isn't converting that into a valid date, issuing a warning message, and inserting a "zero" date.

Upvotes: 2

Related Questions