Mysqli更新抛出调用成员函数bind_param（）错误

Question

Hi i have a 70/80 field form that i need to insert into a table so instead of manually creating one huge insert statement I firstly have created a table in my db from the names of the inputs in the form here is the code that i use to create/alter the table

function createTable($array, $memberMysqli)
{
   foreach ($array as $key => $value)
   {
            //echo "<p>Key: ".$key." => Value: ".$value . "</p>";
            $query = "ALTER TABLE questionnaire ADD ".$key."  text";

            if($stmt = $memberMysqli->prepare($query))
            {
                $success = $stmt->execute();
            }
   }
         echo "<h1>Array count: ". count($array) ."</h1>" ;
}

This works fine and altered the table exactly how i wanted it. Now to insert the form values to do this i do a basic one field insert store the id of the row and then have loop all the post variables updating that row. Here is my code for that:

$stmt = $memberMysqli->prepare("INSERT INTO questionnaire(userid) VALUES (?)");

$stmt->bind_param('s', $_POST['userid']);
$stmt->execute();
$rowid = $stmt->insert_id;
$stmt->close();

$memberMysqli->autocommit(FALSE);

function updateColumn($memberMysqli, $query, $uid, $value) 
{
    if ($value) 
    {
        $stmt = $memberMysqli->prepare($query);
        //Throws bind param error here
        $stmt->bind_param("ss", $value, $uid);
        $stmt->execute();
    }
}

function loopInputs($array, $memberMysqli, $rowid)
{
     foreach ($array as $key => $formvalue)
     {
        var_dump($key);
        updateColumn($memberMysqli, "UPDATE questionnaire SET $key = ? WHERE id = ?", $rowid, $formvalue);
     }
}

loopInputs($_POST, $memberMysqli, $rowid);

$memberMysqli->commit();
$memberMysqli->close();

This throws a bind param error and i have no idea why. Any help would be great.

Answer 1

O, let's try a canonical answer.

Call to a member function (or expects parameter 1 to be mysqli_result, boolean given for the procedural style) is not an error itself but just a symptom, for some other problem.
This very error message means that no object was created where should.

So - there was a problem with creating an $stmt object.
Most likely it's a problem with the query. So, we need to track that error down.

Mysqli won't tell you what's going on unless asked explicitly. So, you have to always check the result of every mysqli function interacting with server and if result is FALSE - check $mysqli->error.

It is also very important to convert mysqli error message into PHP error, to let it go according site-wide error reporting settings.

If you are using mysqli_query() all over the application code without encapsulating it into some helper class, trigger_error() is a good way to raise a PHP error, as it will tell you also the file and the line number where error occurred

So, all your prepare(), execute() and query() calls have to be written this way:

$stmt = $mysqli->prepare($query) or trigger_error($mysqli->error."[$query]");

or in procedural style

$res = mysqli_query($mysqli,$query) or trigger_error(mysqli_error($mysqli)."[$query]");

in all your scripts
and since then you will be notified of the reason, why the object weren't created. (If you're curious of this or syntax, I've explained it here) Note that query also included in the error message, to let you inspect it visually and test in another environment.

However, if you're encapsulating your query into some class, file and line from trigger error will be quite useless as they will point to the call itself, not the application code that caused certain problem. So, when running mysqli commands encapsulated, another way have to be used:

$result = $mysqli->query($sql);
if (!$result) {
    throw new Exception($mysqli->error." [$query]");
}

as Exception will provide you with a stack trace, which will lead you the the place from which an erroneous query were called.

Note that you have to be able to see PHP errors in general. On a live site you have to peek into error logs, so, settings have to be

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

while on a local development server it's all right to make errors on screen:

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

and of course you should never ever use error suppression operator (@) in front of your statements.