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
            WHERE reference=?
        INSERT INTO tablename(
        ) 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.




Your query contains an error. And you are trying to runbind_param on something that didn't work.

You are probably doing:

$this->prepSQL = $this->con->prepare('...');

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:

UsingINSERT INTO ... ON DUPLICATE KEY UPDATE ... is much easier than your IF ELSE statement.

People are also looking for solutions to the problem: php - MySQL: Unknown column 'price_USD' in 'field list' - Update operation - correct query (it runs on phpMyAdmin)


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.