Mysqli更新抛出调用成员函数bind_param()错误

2021-12-25 00:00:00 php mysqli

我有一个 70/80 字段表单，我需要将其插入到表中，因此我不是手动创建一个巨大的插入语句，而是首先根据表单中输入的名称在我的数据库中创建了一个表，这里是代码我用来创建/更改表格

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>" ;
}

这很好用并且完全按照我想要的方式改变了表格.现在要插入表单值来执行此操作，我执行一个基本的单字段插入存储行的 id，然后循环更新该行的所有后期变量.这是我的代码:

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.

推荐答案

O，让我们尝试一个规范的答案.

O, let's try a canonical answer.

调用成员函数(或期望参数1为mysqli_result，布尔值给定用于程序风格)本身不是错误，而只是一个症状，对于其他一些问题.
这个错误消息意味着没有在应该创建的地方创建对象.

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.

因此 - 创建 $stmt 对象时出现问题.
很可能是查询有问题.因此，我们需要跟踪该错误.

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 不会告诉您发生了什么.因此，您必须始终检查与服务器交互的每个 mysqli 函数的结果，如果结果为 FALSE - 检查 $mysqli->error.

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.

将 mysqli 错误消息转换为 PHP 错误也很重要，让它根据站点范围的错误报告设置进行处理.

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

如果您在整个应用程序代码中使用 mysqli_query() 而不将其封装到某个辅助类中，trigger_error() 是引发 PHP 错误的好方法，因为它还会告诉您文件和发生错误的行号

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

因此，您所有的prepare()、execute() 和query() 调用都必须这样编写:

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

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

或程序风格

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

在你所有的脚本中
从那时起，您将被告知原因，即未创建对象的原因.(如果你对这个 or 语法感到好奇，我已经在这里解释过)请注意，错误消息中还包含查询，让您可以直观地检查它并在另一个环境中进行测试.

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.

但是，如果您将查询封装到某个类中，触发器错误中的文件和行将毫无用处，因为它们将指向调用本身，而不是导致某些问题的应用程序代码.所以，在运行封装的mysqli命令时，还得用另一种方式:

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 将为您提供堆栈跟踪，它将引导您找到调用错误查询的位置.

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

请注意，您必须能够看到一般的 PHP 错误.在实时站点上，您必须查看错误日志，因此设置必须

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.

相关文章