On a function that gets settings from a DB I ran into the error

Question

I'm busy on a function that gets settings from a DB, and suddenly, I ran into this error:

Fatal error: Call to a member function bind_param() on boolean in C:\xampp2\htdocs\application\classes\class.functions.php on line 16

Normally, this would mean that I'm selecting stuff from unexisting tables and stuff. But in this case, I 'm not...

Here's the getSetting function:

public function getSetting($setting)
{
    $query = $this->db->conn->prepare('SELECT value, param FROM ws_settings WHERE name = ?');
    $query->bind_param('s', $setting);
    $query->execute();
    $query->bind_result($value, $param);
    $query->store_result();
    if ($query->num_rows() > 0)
    {
        while ($query->fetch()) 
        {
            return $value;
            if ($param === '1')
            {
                $this->tpl->createParameter($setting, $value);
            }
        }
    }
    else
    {
        __('invalid.setting.request', $setting);
    }
}

The $this->db variable is passed through a constructor. In case of need, here is it:

public function __construct($db, $data, $tpl)
{
    $this->db = $db;
    $this->tpl = $tpl;
    $this->data = $data;
    $this->data->setData('global', 'theme', $this->getSetting('theme'));
}

Also, since I'm making use of a database, my database connection:

class Database
{
    private $data;

    public function __construct($data)
    {
    $this->data = $data;
    $this->conn = new MySQLi(
      $this->data->getData('database', 'hostname'), 
      $this->data->getData('database', 'username'), 
      $this->data->getData('database', 'password'), 
      $this->data->getData('database', 'database')
    );
    if ($this->conn->errno)
    {
        __('failed.db.connection', $this->conn->errno);
    }
    date_default_timezone_set('Europe/Amsterdam');
}

I've already tested the connection, 100% positive that it works as intended. I'm setting the DB connection things in a configuration file:

'database' => array(
    'hostname' => '127.0.0.1',
    'username' => 'root',
    'password' => ******,
    'database' => 'wscript'
)

Now the weird thing is; the table exists, the requested setting exists, the DB exists, but still, that error won't leave. Here's some proof that the DB is correct:

Does this answer your question? [mysqli\_fetch\_assoc() expects parameter / Call to a member function bind\_param() errors. How to get the actual mysql error and fix it?](https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-call-to-a-member-function-bind-param) — Dharman, Mar 01 '20 at 10:07

score 171 · Accepted Answer · edited Apr 01 '17 at 02:20

171

The problem lies in:

$query = $this->db->conn->prepare('SELECT value, param FROM ws_settings WHERE name = ?');
$query->bind_param('s', $setting);

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.)

edited Apr 01 '17 at 02:20

miken32

42,008
16
111
154

answered Dec 10 '14 at 06:43

RobP

9,144
3
20
33

5

Why do you suggest to call `$query->error_list`if the prepare methods returns false (and thus $query would be false?) when an error appears? – Adam May 11 '16 at 13:01
2

@Adam you have a point. Calling `$query->error_list` makes sense if the bind_param or an execute fails, but if `$query` is false then one should call `error` or `error_list` on the *mysqli* object, i.e. `$this->db->conn->error_list`. – RobP Dec 13 '16 at 14:53
When I had this problem I removed "(" from SQL query. Previous was "SELECT (user_id, price) FROM operations WHERE user_id = 1. The correct version is "SELECT user_id, price FROM ... When I removed "()" I got rid of this error. – Mateusz H. Aug 15 '19 at 14:55
The error is quite mis-leading. Unlike other languages, the root cause is not being indicated. When the error is caused by first line "prepare", the error thrown is indicating that "bind_param" is the reason. Qudos!! – Sriram Nadiminti May 06 '21 at 04:08

score 44 · Answer 2 · answered Aug 05 '15 at 21:01

Any time you get the...

"Fatal error: Call to a member function bind_param() on boolean"

...it is likely because there is an issue with your query. The prepare() might return FALSE (a Boolean), but this generic failure message doesn't leave you much in the way of clues. How do you find out what is wrong with your query? You ask!

First of all, make sure error reporting is turned on and visible: add these two lines to the top of your file(s) right after your opening <?php tag:

error_reporting(E_ALL);
ini_set('display_errors', 1);

If your error reporting has been set in the php.ini you won't have to worry about this. Just make sure you handle errors gracefully and never reveal the true cause of any issues to your users. Revealing the true cause to the public can be a gold engraved invitation for those wanting to harm your sites and servers. If you do not want to send errors to the browser you can always monitor your web server error logs. Log locations will vary from server to server e.g., on Ubuntu the error log is typically located at /var/log/apache2/error.log. If you're examining error logs in a Linux environment you can use tail -f /path/to/log in a console window to see errors as they occur in real-time....or as you make them.

Once you're squared away on standard error reporting adding error checking on your database connection and queries will give you much more detail about the problems going on. Have a look at this example where the column name is incorrect. First, the code which returns the generic fatal error message:

$sql = "SELECT `foo` FROM `weird_words` WHERE `definition` = ?";
$query = $mysqli->prepare($sql)); // assuming $mysqli is the connection
$query->bind_param('s', $definition);
$query->execute();

The error is generic and not very helpful to you in solving what is going on.

With a couple of more lines of code you can get very detailed information which you can use to solve the issue immediately. Check the prepare() statement for truthiness and if it is good you can proceed on to binding and executing.

$sql = "SELECT `foo` FROM `weird_words` WHERE `definition` = ?";
if($query = $mysqli->prepare($sql)) { // assuming $mysqli is the connection
    $query->bind_param('s', $definition);
    $query->execute();
    // any additional code you need would go here.
} else {
    $error = $mysqli->errno . ' ' . $mysqli->error;
    echo $error; // 1054 Unknown column 'foo' in 'field list'
}

If something is wrong you can spit out an error message which takes you directly to the issue. In this case there is no foo column in the table, solving the problem is trivial.

If you choose, you can include this checking in a function or class and extend it by handling the errors gracefully as mentioned previously.

Very good answer mainly for advices about the error reporting. Programming with the error reporting disabled is like playing chess with eyes shut. People also don't know how to use a debugger (or that it even does exist). — David Ferenczy Rogožan, Nov 27 '15 at 17:04
Thank you for this great work, this is very helpful @Jay Blanchard — Kerim Yagmurcu, Mar 17 '19 at 11:45
But the error was Fatal Error and died and stopped the program, So turn off displaying errors can not be good solution. — Nabi K.A.Z., Jun 19 '19 at 19:12
Also check the privileges of your user for your data base. If say your user can't make updates then you'll get this error but the above (really excellent answer) might not pick this up. — Shaedo, Aug 03 '19 at 14:17

score 23 · Answer 3 · answered May 24 '17 at 08:08

23

Even when the query syntax is correct, prepare could return false, if there was a previous statement and it wasn't closed. Always close your previous statement with

$statement->close();

If the syntax is correct, the following query will run well too.

answered May 24 '17 at 08:08

Tamas Kalman

1,925
1
19
24

score 12 · Answer 4 · answered Dec 10 '14 at 06:48

prepare return a boolean only when it fails therefore FALSE, to avoid the error you need to check if it is True first before executing:

$sql = 'SELECT value, param FROM ws_settings WHERE name = ?';
if($query = $this->db->conn->prepare($sql)){
    $query->bind_param('s', $setting);
    $query->execute();
    //rest of code here
}else{
   //error !! don't go further
   var_dump($this->db->error);
}

score 5 · Answer 5 · edited Apr 04 '19 at 12:07

5

Sometimes, it is also because of a wrong table name or column name in the prepare statement.

See this.

edited Apr 04 '19 at 12:07

Neuron

5,141
5
38
59

answered Feb 17 '18 at 11:40

Reejesh PK

658
1
11
27

1

This was my issue, wrong table name! Wish I'd seen this comment all the way down here earlier! – Reid Svntn Nov 09 '20 at 19:55

score 2 · Answer 6 · answered May 01 '18 at 19:10

Following two are the most probable cause of this issue:

Spelling mistake either in column names or table name
Previously established statement is not closed. Just close it before making the prepared statement.

$stmt->close(); // <<<-----This fixed the issue for me

$stmt = $conn->prepare("Insert statement");

score 1 · Answer 7 · edited Sep 22 '17 at 13:54

1

Another situation that can cause this problem is incorrect casting in your queries.

I know it may sound obvious, but I have run into this by using tablename instead of Tablename. Check your queries, and make sure that you're using the same case as the actual names of the columns in your table.

edited Sep 22 '17 at 13:54

Cody Gray - on strike

239,200
50
490
574

answered Sep 22 '17 at 12:04

4ndyG

73
1
9

score 1 · Answer 8 · answered Mar 27 '18 at 14:49

1

You should always try as much as possible to always put your statements in a try catch block ... it will always help in situations like this and will let you know whats wrong. Perhaps the table name or column name is wrong.

answered Mar 27 '18 at 14:49

Sam Banana

57
1
10

score 1 · Answer 9 · edited Feb 28 '19 at 04:07

1

I noticed that the error was caused by me passing table field names as variables i.e. I sent:

$stmt = $this->con->prepare("INSERT INTO tester ($test1, $test2) VALUES (?, ?)");

instead of:

$stmt = $this->con->prepare("INSERT INTO tester (test1, test2) VALUES (?, ?)");

Please note the table field names contained $ before field names. They should not be there such that $field1 should be field1.

edited Feb 28 '19 at 04:07

chb

1,727
7
25
47

answered Feb 27 '19 at 23:07

Chrisogonas Odero Mc'Odhiambo

21
1

score 1 · Answer 10 · edited Nov 15 '20 at 09:33

1

In my experience the bind_param was fine but I had mistaken the database name so, I changed only the database name in the connection parameter and it worked perfectly.

I had defined root path to indicate the root folder, include path to include folder and base url for the home url of website. This will be used for calling them anywhere they are required.

edited Nov 15 '20 at 09:33

David Kariuki

1,522
1
15
30

answered Jan 18 '20 at 06:18

Sangeeth Arulraj

91
2
6

please provide some code to make your answer more helpful and practical. – moh80s Jan 18 '20 at 07:37

score 0 · Answer 11 · edited Apr 04 '19 at 12:07

This particular error has very little to do with the actual error. Here is my similar experience and the solution...

I had a table that I use in my statement with |database-name|.login composite name. I thought this wouldn't be a problem. It was the problem indeed. Enclosing it inside square brackets solved my problem ([|database-name|].[login]). So, the problem is MySQL preserved words (other way around)... make sure your columns too are not failing to this type of error scenario...

score 0 · Answer 12 · edited Apr 04 '19 at 12:06

0

Sometimes explicitly stating your table column names (especially in an insert query) may help. For example, the query:

INSERT INTO tableName(param1, param2, param3) VALUES(?, ?, ?)

may work better as opposed to:

INSERT INTO tableName VALUES(?, ?, ?)

edited Apr 04 '19 at 12:06

Neuron

5,141
5
38
59

answered May 20 '18 at 17:32

Ebite Zion

340
2
10

On a function that gets settings from a DB I ran into the error

12 Answers12

Linked