我正在尝试学习如何使用数组将多个条目插入到数据库表中。这是我的尝试。我做错了什么？

$servername = "localhost";
$username = "#";
$password = "#";
$dbname = "hosts";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$client = array(
    "1" => array("Jerry Garcia", "193.169.5.11"),

    "2" => array("Bill Graham", "193.169.5.12"),

    "3" => array("Arlo Guthrie", "193.169.5.13")
     );

if(is_array($client) {
    $DataArr = array();
    foreach($client as $row) {
        $fieldVal1 = mysqli_real_escape_string($client[$row][1]);
        $fieldVal2 = mysqli_real_escape_string($client[$row][2]);
        $fieldVal3 = mysqli_real_escape_string($client[$row][3]);

        $DataArr[] = "('fieldVal1', 'fieldVal2', 'fieldVal3')";

    }

    $sql = "INSERT INTO ip_data (field1, field2, field3) values ";
    $sql .= implode(',' , $DataArr);

    mysqli_query($conn, $query);
}

我试过了，还是不行。我错过了什么？

$servername = "localhost";
$username = "#";
$password = "#";
$dbname = "hosts";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$client = array(
    "0" => array("Jerry Garcia"),

    "1" => array(""193.169.5.11"),
     );

if(is_array($client)) {
    $DataArr = array();
    foreach($client as $row) {
        $fieldVal1 = mysqli_real_escape_string($client[$row][0]);
        $fieldVal2 = mysqli_real_escape_string($client[$row][1]);


        $DataArr[] = "('$fieldVal1', '$fieldVal2')";

    }

    $sql = "INSERT INTO ip_data (field1, field2) values ";
    $sql .= implode(',' , $DataArr);

    mysqli_query($conn, $query);
}

感谢您的建议。

下次试试。

$servername = "localhost";
$username = "#";
$password = "#";
$dbname = "hosts";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$client = array(
    "0" => array("name" => "Peter Maxx", "ip" => "193.169.5.16"),
    "1" => array("name" => "Ravi Shankar", "ip" => "193.169.5.17")
     );

if(is_array($client)) {
        $DataArr = array();         
        foreach($client as $row) {

            $DataArr[] = "('". mysqli_real_escape_string($conn, $row[0]) ."', '". mysqli_real_escape_string($conn, $row[1]) ."')";
        }
        $sql = "INSERT INTO ip_data (name, ip)
VALUES 
( 'Peter Maxx', '193.169.5.16'),
('Ravi Shankar', '193.169.5.17')";
        $sql .= implode(", " , $DataArr);
        mysqli_query($conn, $sql);  

}

我收到这些错误消息。

PHP Notice:  Undefined offset: 0 in php shell code on line 5
PHP Notice:  Undefined offset: 1 in php shell code on line 5
PHP Notice:  Undefined offset: 0 in php shell code on line 5
PHP Notice:  Undefined offset: 1 in php shell code on line 5

最佳答案

不清楚您的目的是什么，因为您问题中的 SQL 查询指定了 3 个字段(field1、field2、field3)要插入到您的表中，但您的客户数组中只有 2 个值。如果你想在一个查询中插入多行，假设你的客户数组中的“name”和“ip”值，你可以这样做:

    if(is_array($client)) {
        $DataArr = array();         
        foreach($client as $row) {
            //CREATE ARRAY WITH name AND ip VALUES FOR EACH USER...               
            $DataArr[] = "('". mysqli_real_escape_string($conn, $row[0]) ."', '". mysqli_real_escape_string($conn, $row[1]) ."')";
        }
        $sql = "INSERT INTO ip_data (name, ip) VALUES ";
        $sql .= implode(", " , $DataArr);
        mysqli_query($conn, $sql);
    }

$sql 变量将包含以下符合多重插入语法的查询:

INSERT INTO ip_data (name, ip) 
VALUES ('Jerry Garcia', '193.169.5.11'), 
       ('Bill Graham', '193.169.5.12'), 
       ('Arlo Guthrie', '193.169.5.13')

请注意，您的问题中有 $query 而不是 $slq。它应该是:mysqli_query($conn, $sql);。

另一件事:使用 mysqli_real_escape_string() 程序方式代替 object oriented way ，如您的示例所示，需要将链接标识符作为参数传递给连接:mysqli_real_escape_string($conn, $row[0])

更新: 在您最近的尝试中，您已将数组更改为关联数组，因此应该这样做:

 $servername = "localhost";
 $username = "#";
 $password = "#";
 $dbname = "hosts";

 $conn = new mysqli($servername, $username, $password, $dbname);

 if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
 }
 $client = array(
     "0" => array("name" => "Peter Maxx", "ip" => "193.169.5.16"),
     "1" => array("name" => "Ravi Shankar", "ip" => "193.169.5.17")
 );

 if(is_array($client)) {
    $DataArr = array();         
    foreach($client as $row) {

        $DataArr[] = "('". mysqli_real_escape_string($conn, $row["name"]) ."', '". mysqli_real_escape_string($conn, $row["ip"]) ."')";
    }
    $sql = "INSERT INTO ip_data (name, ip) VALUES ";
    $sql .= implode(", " , $DataArr);
    mysqli_query($conn, $sql);  

 }

关于php - 将数组插入数据库表，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/42352170/