I have a pretty simple query that's failing in CI:
$sql = "INSERT INTO tenant (name, image, url) VALUES (?, ?, ?)";
$this->db->query($sql, $name, $image, $url);
When I try to execute this query, I end up with the following error:
A Database Error Occurred
Error Number: 1064
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 '?, ?, ?)' at line 1
INSERT INTO tenant (name, image, url) VALUES (?, ?, ?)
Filename: models/Tenant.php
Line Number: 107
All of the variables used are defined properly, and I can get it to work no problem by converting it to this:
$this->db->query("INSERT INTO tenant (name, image, url) VALUES ('$name', '$image', '$url')");
There's nothing special about any of the variables - they're all just strings. What needs to happen for query binding to work here?
Take a look at documentation about query binding. query takes two arguments. Your bindings should be in one array.
$sql = "INSERT INTO tenant (name, image, url) VALUES (?, ?, ?)";
$this->db->query($sql, array($name, $image, $url));
You Must pass 2nd parameters in array form :
Query Bindings :Bindings enable you to simplify your query syntax by letting the system put the queries together for you. Consider the following example
$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array(3, 'live', 'Rick'));
The question marks in the query are automatically replaced with the values in the array in the second parameter of the query function
For more information read here in detail