[Solved] PHP Uncaught Error: Call to a member function bind_param()
A prepared statement is a very useful feature in database management that increases efficiency and protects the database against SQL injections. It is also referred to as a parameterized statement.
It works by separating the SQL code from the data.
In the prepared statement, an SQL statement template is created and sent to the database with values unspecified (labeled with "?"). These question marks (?) are referred to as parameters.
The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing it.
The application later binds the values to the parameters, then executes the SQL statement. The application can execute the statement many times with different values without having to do the parsing and thus increasing the efficiency.
Since the parameter values are transmitted later, separately from the SQL statement using a different protocol, SQL injection cannot occur.
Below is an example code of inserting data in a table with a prepared statement in MySQLi.
<?php $fname = "John" $lname = "Doe"; $email = "email@example.com"; $con = new mysqli("servername", "db_username", "db_password", "db_name"); $stmt = $con->prepare("INSERT INTO users(first_name, last_name, email) VALUES(?,?,?)"); $stmt->bind_param("sss", $fname, $lname, $email); $stmt->execute();
The example below shows a code for updating a record in the database table using a prepared statement.
<?php $fname = "John" $lname = "Doe"; $email = "firstname.lastname@example.org"; $userid = 5; $con = new mysqli("servername", "db_username", "db_password", "db_name"); $stmt = $con->prepare("UPDATE users SET first_name = ?, last_name = ?, email = ? WHERE id = ?"); $stmt->bind_param("sssi", $fname, $lname, $email, $userid); $stmt->execute();
When working with prepared statements in MySQLi, it's common to encounter the error below from time to time.
PHP Fatal error: Uncaught Error: Call to a member function bind_param() on bool in /path/to/file/filename.php:x
This happens when prepare returns a bool false instead of a MySQLi statement object. That is then causing the fatal error you see here. This is due to an error in the SQL statement.
How to Fix the Error
Below are possible ways to fix the error:
- Countercheck to make sure that the name of the table and its columns which are specified in the SQL statement is exactly the same as in the database with no misspellings.
- If you specify the table columns you are working with, ensure that the number of column names matches that of the parameters (?). For instance in example 1 above, we have 3 columns (first_name, last_name, email) we are inserting into. We must include exactly 3 "?" and not more or less. Ensure that they are separated with commas (,) and no extra comma is there.
- If you don't specify the column names that you’re inserting values into, then you have to give values for every column. For instance, if your table has 5 columns and you don't specify columns in the statement, you have to supply 5 values or else you get the error.
- In the bind_param() function, make sure that the number of values is equal to the number of parameters (?) passed in the prepare() function. For instance, in example 1 we have 3 parameters (?,?,?) in the prepare() function and 3 values ($fname, $lname, $email) in the bind_param().
- Make sure to specify the types of data of the parameters as the first argument in the bind_param() function, eg "sss" in example 1 and "sssi" in example 2. The "s" character tells mysql that the parameter is a string. Other types include "i" for integer, "d" for double and "b" for BLOB.
The number of types in the argument should be equal to the number of values. Their order should also be the same as that of the respective values.
By fixing any of the above possible causes, your code should definitely work with no errors.