prepare() outputs bool false, statement looks correct to me

Question

I have a function that executes a prepared statement. I've pinpointed the problem to the SQL code by wrapping the prepare() line with an exception catcher, but I can't figure out what is wrong with it.

This is the function

    function update_table($column, $value, $conn, $email) {
        require "config.php";
        $stmtupdate = $conn->prepare("UPDATE $table SET ? = ? WHERE email = ?");
        $stmtupdate->bind_param("sis", $column, $value, $email);
        $stmtupdate->execute();
        $stmtupdate->close();
    }

This is one of the function calls

update_table("failedCount", 0, $conn, $email);

Error printed to the page

Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in /var/www/html/usenergy/login.php:125 Stack trace: #0 /var/www/html/usenergy/login.php(218): update_table('failedCount', 0, Object(mysqli), 'testmail@test...') #1 {main} thrown in /var/www/html/usenergy/login.php on line 125

column is a string that defines the column name where $value will be set to ($value is integer).

The column you intend to set isn't named when the statement is prepared which includes a syntax check so it fails. Params are bound and passed upon execution. — Jim Castro, Mar 31 '19 at 00:54
@JimCastro , the column is supposed to be "failedCount", which is passed as an argument to the function. — crimson_king, Mar 31 '19 at 00:59
You can't bind a variable to a column name. You need `$stmtupdate = $conn->prepare("UPDATE $table SET $column= ? WHERE email = ?"); $stmtupdate->bind_param("is", $value, $email);`. Adding `or die($conn->error);` at the end of the call to `prepare` would have shown you the error you were getting. — Nick, Mar 31 '19 at 01:31

score 1 · Accepted Answer · answered Mar 31 '19 at 00:58

You have this:

$stmtupdate = $conn->prepare("UPDATE $table SET ? = ? WHERE email = ?");
$stmtupdate->bind_param("sis", $column, $value, $email);

This results in this SQL:

UPDATE $table
SET @var1 = @var2 WHERE email = @var3

substituted for example with

UPDATE $table
SET 'column' = 123 WHERE email = 'something'

But you want something like

UPDATE mytable
SET mycolumn = 123 WHERE email = 'something'

So there are two mistakes: You want a table name, not $table. And you want a column name, not a string bind variable value.

Use string concatenation:

$stmtupdate = $conn->prepare("UPDATE ".$table." SET ".$column." = ? WHERE email = ?");
$stmtupdate->bind_param("is", $value, $email);

(I hope I'm not mistaken. It's been some time since I used PHP with SQL.)

Thanks for the detailed explanation, I have solved the problem thanks to you and others that replied. — crimson_king, Mar 31 '19 at 02:12

Ludo · Answer 2 · 2019-03-31T00:57:38.657

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 SET or WHERE clause) are not correct?

Try more like this :

$stmtupdate = $conn->prepare("UPDATE ".$table." SET ".$column." = ? WHERE email = ?");
$stmtupdate->bind_param("is", $value, $email);

Also, consider use of something like $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.)

prepare() outputs bool false, statement looks correct to me

2 Answers2