Mysqli "Prepare statement" is not creating the Object
I'm a but unused to Mysqli, and I am having a problem with the following code..
$mysql = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME) or die('There was a problem connecting to the database');
if (mysqli_connect_errno()) {
printf("DB error: %s", mysqli_connect_error());
exit();
}
$query = "INSERT INTO employee(id, name, age, address, phone, email, department,
designation, joindt, terminate, salary, deduction, tds, pf)
VALUES (:id, :name, :age, :addre开发者_如何学Pythonss, :phone, :email, :department,
:designation, :joindt, :terminate, :salary, :deduction, :tds, :pf)";
$ins = $mysql->prepare($query);
if(!ins){
echo "prepare failed\n";
echo "error: ", $mysql->error, "\n";
echo "OBJECT NOT CREATED";
return;
}
Upon running this code, I get the following errors in my browser :
( ! ) Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' in C:\wamp\www\payroll\new_backend.php on line 40
( ! ) mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join, terminate, salary, deduction, tds, pf) VALUES (:id, :name, :age, ' at line 2 in C:\wamp \www\payroll\new_backend.php on line 40
I guess the PREPARE statement is not creating the $ins object. Any help ?
join
is a reserved word in mySQL. You will either need to enclose it in backticks:
`join`
or - better - change the column's name.
Additionally, it looks like mysqli doesn't support PDO-style :fieldname
bindings. Check out the example in the manual on prepare().
I can't test this right now but the correct syntax should go something like this (abbreviated):
$id = 10;
$name = "John Doe";
$query = "INSERT INTO employee(id, name) values (?, ?)";
$query->bind_param("i", $id);
$query->bind_param("s", $name);
Look at the manual page for mysqli::prepare():
- http://es.php.net/mysqli_prepare
Prepared statements expect ?
as place-holders, rather than :foo
.
Hereis the solution I arrived at, after Pekka's help.. :)
$mysql = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME) or die('There was a problem connecting to the database');
if (mysqli_connect_errno()) {
printf("DB error: %s", mysqli_connect_error());
exit();
}
$query = "INSERT INTO employee(name, age, address, phone, email, department,
designation, joindt, terminate, salary, deduction, tds, pf)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
$ins = $mysql->prepare($query);
if(!$ins){
echo "prepare failed\n";
echo "error: ", $mysql->error, "\n";
return;
}
$ins->bind_param('sisisssiiiiii', $data['name'], $data['age'], $data['address'],
$data['phone'], $data['email'], $data['department'], $data['designation'],
$data['joindate'], $data['terminationdate'], $data['salary'], $data['leave_deduction'], $data['tds'], $data['pf']);
$ins->execute();
精彩评论