Why can't write the date into mysql with php?

Question

Create a series of date in php shell.

$sDate = "2018-09-03";
for($i=0;$i<5;$i++){
    $number = 7*$i;
    $day=date("Y-m-d",strtotime("$sDate  + $number day"));
    echo $day."\n";
}

The result is as below:

Create a database which contain a table.

create database `test_day`;
create table test_day( `tday`  date);

I want to write all dates into table test_day.

$dsn = "mysql:host=localhost;dbname=test_day";
$con = new PDO($dsn,"root","xxxxx");
$sDate = "2018-09-03";
for($i=0;$i<18;$i++){
    $number = 7*$i;
    $day=date("Y-m-d",strtotime("$sDate  + $number day"));
    $query="insert into `test_day` (`tday`) value ($day)";
    $con->query($query);
}

Now select what exactly in test_day.

select * from  test_day;
+------------+
| tday       |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+

Why can't write the day serial into test_day?

Try with single quotes : `$query="insert into \`test_day\` (\`tday\`) values ('".$day."')";` — Niklesh Raut, Oct 13 '18 at 10:37
It seems that you are using PDO library; then you should really changed your query to [Prepared Statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Madhur Bhaiya, Oct 13 '18 at 10:40
Add `$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` and errors will start to shout at you — RiggsFolly, Oct 13 '18 at 10:46
@MadhurBhaiya - I second that, and if you prepare it outside of the loop, it will actually run a tiny bit faster. Because it only has to interpret the query 1 time and execute it {n} times. — ArtisticPhoenix, Oct 13 '18 at 10:53

Matthijs · Accepted Answer · 2018-10-13T11:45:32.967

You have to put the date between single quotes.

The query now looks like this:

insert into `test_day` (`tday`) values (2018-09-03)

While it has to be:

insert into `test_day` (`tday`) values ('2018-09-03')

Change your code to:

$query = "insert into `test_day` (`tday`) values ('$day')";

Edit

I agree with the people who (also) recommend to use Prepared Statements for safety and efficiency. In your case, the code could be like this:

$dsn = "mysql:host=localhost;dbname=test_day";
$con = new PDO($dsn,"root","xxxxx");
$sDate = "2018-09-03";

for ($i=0; $i < 18; $i++){
    $number = 7*$i;
    $day = date("Y-m-d",strtotime("$sDate  + $number day"));

    // Prepare the statement
    $statement = $con->prepare("INSERT INTO `test_day` (`tday`) VALUES (?)");

    // Fill the string placeholder and execute the statement.
    $statement->execute("s", $day);
}

Or better still use a prepared parameterised and bound query — RiggsFolly, Oct 13 '18 at 10:43

Why can't write the date into mysql with php?

1 Answers1