Michal Patriak Michal Patriak - 7 months ago 20
SQL Question

Unable to INSERT dates and times into MYSQL database with PHP

I am trying to build an SQL query that will insert the check-in time for a child at a fictional daycare facility. Here is a condensed version of my query code:

$childFirstName = $_POST['childFirstName'];
$childLastName = $_POST['childLastName'];

$now = new DateTime();
$nowDate = $now->format('m-d-Y');
$nowTime = $now->format('h:i');


$sql_childID = "SELECT id FROM child
WHERE firstName = '$childFirstName'
AND lastName = '$childLastName'";
$result = $pdo->query($sql_childID);
$row = $result->fetch();


$sql = "INSERT INTO checkinout(date, in, child_id) VALUES(?,?,?)";
$statement = $pdo->prepare($sql);
$statement->bindValue(1, $nowDate);
$statement->bindValue(2, $nowTime);
$statement->bindValue(3, $row['id']);
$statement->execute();


The checkinout table uses VARCHAR datatypes for the date and in columns. Originally they were set to use DATETIME, but I received the same errors.

Right now I get the following errors returned...

enter image description here

You can see from the error messages that my values are getting passed in the way I want them to, but I don't understand where my syntax error would be.

Answer

Enclose your field names with backticks. Two of them are reserved words (date and in):

$sql = "INSERT INTO checkinout(`date`, `in`, `child_id`) VALUES(?,?,?)";

https://dev.mysql.com/doc/refman/5.5/en/keywords.html