CODEWITHSUNDEEP
phpmysqldate
Duc Tran
Duc Tran

Reputation: 6304

Strange behaviour when inserting date from PHP into database

In "form.php"

<html>
    <body>
        <form action="process.php" method="post">
            <input type="text" name="dat" />
            <input type="submit" value="submit" />
        </form>
    </body>
</html>

In "process.php":

<?php
    echo $_POST['dat'];

    mysql_connect("localhost", "root", "123456")
            or die("can't connect");
    mysql_select_db("st")
        or die("can't selectdb");

    $query = "INSERT INTO sts (time) VALUES ( " . $_POST['dat']. "  ) ";


    $result = mysql_query($query)
        or die(mysql_error());

    $mysql_close();

?>

This give me 0000-00-00 in the database, in the field name "time" of type "DATE". If using this:

$time = explode("-", $_POST['dat']);
    $query = "INSERT INTO sts (time) VALUES ( " .  date("m-d-Y", mktime(0, 0, 0, $time[1], $time[2], $time[0])). "  ) ";

-> doesn't work either

EDIT: I've changed from "m-d-Y" to "Y-m-d" for appropriate semantic, but it still doesn't work

However, if I change the code like this:

$query = "INSERT INTO sts (time) VALUES ('1991-10-05') ";

it works.

Can anyone explain this for me?

Upvotes: 0

Views: 295

Answers (3)

Mchl
Mchl

Reputation: 62395

You want date("Y-m-d" not date("m-d-Y"

See here for explanation of what date/time formats MySQL accepts

Also you need quotes around date if you're using this format, because without them, MySQL thinks you're inserting a nymber: 1991-10-05 = 1976.

$query = "INSERT INTO sts (time) VALUES ('" .  date("Y-m-d", mktime(0, 0, 0, $time[1], $time[2], $time[0])). "'  ) ";

Or alternatively shorter format and without quotes

$query = "INSERT INTO sts (time) VALUES (" .  date("Ymd", mktime(0, 0, 0, $time[1], $time[2], $time[0])). "  ) ";

and personally I'd do:

$ts = strtotime($_POST['dat']);
if($ts != 0) {
  $date = date('Ymd',$ts);    
  $query = "INSERT INTO sts (time) VALUES ($date) ";
} else {
// invalid date format
}

And just like others said in comments: never put $_POST (or $_GET or any untrusted data) directly in your queries. Always escape/sanitize data first.

Upvotes: 2

Marc B
Marc B

Reputation: 360872

Neither if your two PHP examples have the date being quoted. They'll generate queries that look like:

INSERT INTO sts (time) VALUES (2009-01-02);

which will be parsed down to

INSERT INTO sts (time) VALUES (2006)

2009-01-02 is a numeric literal with two subtractions, NOT a date. To get get MySQL to treat this as a date value, it MUST be quoted:

INSERT INTO sts (time) VALUES ('2009-01-02');
                               ^----------^--- quotes MUST be present

So your PHP should look like:

$date = mysql_real_escape_string($_POST['dat']);

$query = "INSERT INTO sts (time) VALUES ('$dat');";

Upvotes: 0

yokoloko
yokoloko

Reputation: 2860

The dates are stored as Y-m-d

So your code should do : 

$query = "INSERT INTO sts (time) VALUES ( " .  date("Y-m-d", mktime(0, 0, 0, $time[1], $time[2], $time[0])). "  ) ";

Upvotes: 1

Related Questions