I am using the function query to insert data to a mysql table from php. The function, call and database structure are show below.
I have two questions:
1) why does the error message say invalid datetime format. The column customCrew is a tinyint.
2) I am able to insert 0 or 1 but how come I can't insert 'FALSE' or FALSE for this column?
function query(/* $sql [, ... ] */){
// SQL statement
$sql = func_get_arg(0);
// parameters, if any
$parameters = array_slice(func_get_args(), 1);
// try to connect to database
static $handle;
if (!isset($handle)){
try{
// connect to database
$handle = new PDO("mysql:dbname=" . DATABASE . ";host=" . SERVER, USERNAME, PASSWORD);
// ensure that PDO::prepare returns false when passed invalid SQL
$handle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}catch (Exception $e){
echo 'help';
// trigger (big, orange) error
trigger_error($e->getMessage(), E_USER_ERROR);
exit;
}
}
// prepare SQL statement
$statement = $handle->prepare($sql);
if ($statement === false){
// trigger (big, orange) error
trigger_error($handle->errorInfo()[2], E_USER_ERROR);
exit;
}
// execute SQL statement
$results = $statement->execute($parameters);
// return result set's rows, if any
if ($results !== false){
return $statement->fetchAll(PDO::FETCH_ASSOC);
}else{
return false;
}
}
here is php statement to use above function:
query("INSERT INTO sails ( type,
date,
skipperId,
boat,
class,
resStart,
resEnd,
resTS,
spots,
customCrew)
VALUES(?,?,?,?,?,?,?,?,?,?)",
'RES',
$_POST['date'],
$_POST['skipperId'],
$_POST['boat'],
"50",
date('H:i', strtotime($_POST['start'])),
date('H:i', strtotime($_POST['end'])),
$response['now'],
$_POST['spots'],
'FALSE');
structure of table sails:
1 type varchar(4) utf8mb4_general_ci No None
2 date Primary date No None
3 skipperId Primary int(11) No None
4 boat varchar(20) utf8mb4_general_ci No None
5 class varchar(4) utf8mb4_general_ci No None
6 resStart time No None
7 resEnd time No None
8 resTS datetime No None
9 sailStart time Yes NULL
10 sailEnd time Yes NULL
11 sailTS datetime Yes NULL
12 spots int(11) No None
13 customCrew tinyint(1) No None
14 xclTS datetime Yes NULL
and here's the error message from the console:
<b>Fatal error</b>: Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1366
Incorrect integer value: 'FALSE' for column `sandbox`.`sails`.`customCrew` at row 1 in
/opt/lampp/htdocs/lagin/includes/functions.php:177
Stack trace:
#0 /opt/lampp/htdocs/lagin/includes/functions.php(177): PDOStatement->execute(Array)
#1 /opt/lampp/htdocs/lagin/public/skipper.php(64): query('INSERT INTO sai...', 'RES', '2021-07-29',
'1', 'Irlbach', '50', '09:00', '20:30', '2021-07-28 17:4...', '3', 'FALSE')