mysql - Attempting to perform an update/insert query using php prepared statement and mysqli but to no avail
I'm trying to execute the following code as a prepared statement using PHP:
private function prepQuery(){
$this->prepSQL = $this->con->prepare('
IF EXIST(SELECT * FROM tablename WHERE reference=?)
UPDATE tablename
SET
column1=?,
column2=?,
column3=?
WHERE reference=?
ELSE
INSERT INTO tablename(
column1,
column2,
column3,
column4
) VALUES(?, ?, ?, ?);
');}
The code executes in an object context where the connection object is a private field variable in the class. The function should initialize the $prepSQL field variable to be an object but I get the following error:
Fatal error: Uncaught Error: Call to a member function bind_param() on boolean
The purpose of the code is to read a file which gets updated regularly(The file size and field data is out of my control so using prepared statements would be the best choice for speed and security). The code should check if the record exist in the database and if so, update it to contain the changes made. Else, insert the new row from the file into the database.
However, it doesn't complete as it says that the $prepSQL variable is a boolean and cannot call the bind_param method on it.
I tried executing the sql manually and that works fine. If I use a normal insert statement in the code, it works fine. But when run as is below it doesn't work. I'm really not sure what I'm doing wrong here.
Answer
Solution:
Your query contains an error. And you are trying to run
bind_paramon something that didn't work.You are probably doing:
The prepare() method can return false and you should check for that. As for why it returns false, perhaps the table name or column names (in SELECT or WHERE clause) are not correct?
Also, consider use of something like $this->db->conn->error_list to examine errors that occurred parsing the SQL. (I'll occasionally echo the actual SQL statement strings and paste into phpMyAdmin to test, too, but there's definitely something failing there.)
That said while not an answer to the actual question DO listen to what other comments said:
Using
INSERT INTO ... ON DUPLICATE KEY UPDATE ...is much easier than your IF ELSE statement.https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html