PHP mysqli 插入命令 [英] PHP mysqli insert command

问题描述

我正在尝试通过数组向 MySQL 中插入多个值，但它不起作用或传递错误消息，所以我不确定我哪里出错了.任何帮助将不胜感激.

I am trying to insert multiple values into MySQL via an array, but It's not working or delivering an error message so I'm not sure where I'm going wrong. Any help would be appreciated.

这里是我调用函数的地方

Here is where I call the function

$testArrayList = array();
          $testArrayList[] = 'Account_idAccount';
          $testArrayList[] = 'firstName';
          $testArrayList[] = 'lastName';
          $testArrayValues = array();
          $testArrayValues[] = $idAccount;
          $testArrayValues[] = $firstName;
          $testArrayValues[] = $lastName;
          $dbManager->insertValues("User", $testArrayList, $testArrayValues);

现在，这里是被调用的 insertValues 函数.

Now, here is the insertValues funciton being called.

        public function insertValues($table, $cols, $values) {
    foreach ($cols as $col)
        $colString .= $col.',';
    foreach ($values as $value)
    {
        $valueAmount .= '?,';
        $valueType .= 's';
        $valueParam .= $value.",";
    }
    $colString = substr($colString, 0, -1);  
    $valueAmount = substr($valueAmount, 0, -1); 
    $valueParam = substr($valueParam, 0, -1); 

    $mysqli = new mysqli(DBHOST, DBUSER, DBPASSWORD, DBDATABASE);
    $sql = "INSERT INTO $table ($colString) VALUES($valueAmount)";
    /* Prepared statement, stage 1: prepare */
    if (!($stmt = $mysqli->prepare($sql))) {
         echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
    }
    print_r($valueParam);
    /* Prepared statement, stage 2: bind and execute */
    if (!$stmt->bind_param("$valueType", $valueParam)) {
        echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
    }

    if (!$stmt->execute()) {
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
    }
    /* explicit close recommended */
    $stmt->close();
    $mysqli->close();
}

推荐答案

那里有一堆错误，这里是你函数的重写版本，应该可以工作:

There were a bunch of errors there, here's a rewritten version of you function that should work:

public function insertValues($table, array $cols, array $values) {

    $mysqli = new mysqli(DBHOST, DBUSER, DBPASSWORD, DBDATABASE);

    $colString = implode(', ', $cols); // x, x, x
    $valString = implode(', ', array_fill(0, count($values), '?')); // ?, ?, ?

    $sql = "INSERT INTO $table ($colString) VALUES($valString)";
    if (!$stmt = $mysqli->prepare($sql))
         echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;

    foreach ($values as $v)
        if (!$stmt->bind_param('s', $v))
            echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;

    if (!$stmt->execute())
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;

    $stmt->close();
    $mysqli->close();

}

您还应该在构造函数中初始化 mysqli 连接一次，而不是为每个方法初始化:

You should as well initialize the mysqli connection once in the constructor instead of for each method:

public function __construct() { 
    $this->mysqli = new mysqli(DBHOST, DBUSER, DBPASSWORD, DBDATABASE);
}

public function __destruct() {
    $this->mysqli->close();
}

此外，您最好创建一个适当的函数来处理这些错误，例如:

Also it's good that you create a proper function to handle those errors, such as:

public function showError($message, object $obj) {
    echo "$message: (" . $obj->errno . ") " . $obj->error;
}

导致你功能的这个更干净的版本:

leading to this cleaner version of you function:

public function insertValues($table, $cols, $values) {

    ...

    if (!$stmt = $mysqli->prepare($sql))
         $this->showError("Prepare failed", $mysqli);

    foreach ($values as $v)
        if (!$stmt->bind_param('s', $v))
            $this->showError("Binding parameters failed", $stmt);

    if (!$stmt->execute())
        $this->showError("Execute failed", $stmt);

    ...

}

这篇关于PHP mysqli 插入命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！