How to insert a date stored in a variable into my mysql database

maxwell_

I have managed to combine the values of 3 drop down menus year, month and day and store the result in a variable.
If I choose from the menus and echo the variable it I get, as an example -

echo $dateRequired; output is 2017-10-20

My problem is getting this value into my MySQL database

$query = "INSERT INTO newsletters(filename, document_name, document_type, description, upload_date, size) ";

 $query .= " VALUES('{$filename}', '{$document_name}', '{$document_type}', '{$description}', '{$dateRequired}', {$size})";

I tried $dateRequired = date("Y-m-d", strtotime($dateRequired));

However this inserts the date as the UNIX Epoch 1970-01-01

The date column in my database is set to DATE

Best regards Maxwell

NogDog

If you already have it as a 'YYYY-MM-DD' string, there should be no reason to further manipulate it before using it in your query.

That being said, I'd really like to see some SQL injection prevention there. Hopefully you're either using the PDO or MySQLi extension and can use a prepared statement? (As opposed to the old, deprecated mysql_*() functions that are not even available in PHP 7)

Using PDO, I'd do something like:

$sql = "
INSERT INTO newsletters(
  filename,
  document_name,
  document_type,
  description,
  upload_date,
  size
) VALUES (
  :filename,
  :document_name,
  :document_type,
  :description,
  :dateRequired,
  :size
)";
$stmt = $pdo->prepare($sql);
if($stmt == false) {
    throw new Exception($pdo->errorInfo());
}
$result = $stmt->execute(array(
    ':filename' => $filename,
    ':document_name' => $document_name,
    ':document_type' => $document_type,
    ':description' => $description,
    ':dateRequired' => $dateRequired,
    ':size' => $size
));
if($result == false) {
    throw new Exception($stmt->errorInfo());
}

maxwell_

Hi, thanks for the advice, I will change to mysqli prepared statements before going live.

I tried adding the variable containing the date directly into the INSERT query -

$dateRequired = ''; // I had to declare the variable at the start of my script

......'{description}', '{$dateRequired}'

however the database date field now says 0000-00-00

NogDog

Well, $dateRequired need to have some value. I had assumed from your original post that it was getting populated with the string '2017-10-20' from somewhere or other, and that's what you wanted to use in the query for that date field. If it's empty, then the zeroes make "sense", and it also makes sense that the PHP date functions gave you that 1970 date, as a 0 time in PHP == 1970-01-01UTC00:00:00 (a.k.a. UNIX epoch). So you may need to do some debugging to ensure it has a value, and perhaps some form validation as well?