PHP: Trying to insert dates in Database

Question

I want to insert all the dates between a starting and an end date into a table in my database.

I already found a code for the two dates and the dates between.

The problem now is that the dates are not inserted into my database when running the code. Only 0000-00-00 is displayed in my date table.

Also no error message occurs when running the code so I don't know where I made a mistake. Can someone help me with this problem?

Here is my Code:

$begin = new DateTime('2010-05-01');
$end = new DateTime('2010-05-10');

$interval = DateInterval::createFromDateString('1 day');
$period = new DatePeriod($begin, $interval, $end);

foreach ($period as $dt) 
{
  $newdate = $dt->format("Y-m-d ");

  $statement = $pdo->prepare("INSERT INTO table (date) VALUES ($newdate)");
  $statement->execute();
}

That's not the way to use prepared statements. Please see: https://www.php.net/manual/en/pdo.prepared-statements.php — KIKO Software, May 05 '21 at 09:23
Use proper value binding for prepared statement as KIKO mentioned. Even if you wanted to make this without placeholders, the date value should be quoted like `"INSERT INTO table (date) VALUES ('$newdate')"` and if you would use binding properly, the placeholder need to be unquoted like `"INSERT INTO table (date) VALUES (:newdate)"` — biesior, May 05 '21 at 09:26
Does this answer your question? [SQL date format and quoting confusion](https://stackoverflow.com/questions/27605382/sql-date-format-and-quoting-confusion) — biesior, May 05 '21 at 09:36
@biesior that's useful background reading but IMHO it's not directly a duplicate of that really because parameterisation is a better all-round solution in the PHP context :-) — ADyson, May 05 '21 at 09:37
@ADyson Of course, it is a better option, but in the mentioned code the strict problem is lack of quotes, and in this case prepared statement is not necesary as we cannot expect SQL injection from DateTime object ;) — biesior, May 05 '21 at 09:40
@biesior I`m aware of the problem of SQL injections. But I was just searching for a solution for my database problem. I also now that there is a lack of quotes but i thought thereby would be the focus on my real question and not on some unlogic code :) — Gandalfthegrey, May 05 '21 at 09:47
Literally the only problem is lack of quotes and de facto this is real answer ;) Of course using prepared statements is better solution and as ADyson showed not too much harder, so now you have a least two proper solutions and definitely should go with the better one with binding. — biesior, May 05 '21 at 09:52

score 1 · Accepted Answer · answered May 05 '21 at 09:26

You forgot to enclose the date value inside the SQL with single quotes. Therefore the SQL engine is trying to treat your input as a number, but it can't (because clearly a date is not a valid number).

But you shouldn't include raw data into your query like that anyway. Using parameters makes it far less likely you'll make simple syntax errors like that (as well as protecting your data from SQL injection when dealing with external data input).

$statement = $pdo->prepare("INSERT INTO table (date) VALUES (:newdate)");
$statement->bindParam(':newdate', $newdate);
$statement->execute();

See the PHP manual on using prepared statements

P.S. If your code was throwing an error due to the bad SQL, you might not see it unless you have switched on PDO exception handling when you create your connection.

Thanks a lot. That really helped with my problem. I searched for hours and didnt find any solution in the Internet. Thank you very much you made my day. — Gandalfthegrey, May 05 '21 at 09:31
Accept and upvote the answer if it fits your requirements. BTW some basics like using quotes in SQL statements should be searched before asking. — biesior, May 05 '21 at 09:33

PHP: Trying to insert dates in Database

1 Answers1