How to insert multiple pieces of data through PHP MySQL

It is often seen inserting data into the database on the website. This article explains how to insert multiple pieces of data into the database.

Use MySQLi and PDO to insert multiple pieces of data into MySQL

mysqli_multi_query() function can be used to execute multiple SQL statements.

The following example adds three new records to the "MyGuests" table:

Example (MySQLi - Object Oriented)

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB"; 
// 创建链接$conn = new mysqli($servername, $username, $password, $dbname);// 检查链接if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);} 
 $sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES (&#39;John&#39;, &#39;Doe&#39;, &#39;john@example.com&#39;);";$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES (&#39;Mary&#39;, &#39;Moe&#39;, &#39;mary@example.com&#39;);";$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES (&#39;Julie&#39;, &#39;Dooley&#39;, &#39;julie@example.com&#39;)"; 
if ($conn->multi_query($sql) === TRUE) {
    echo "新记录插入成功";} else {
    echo "Error: " . $sql . "<br>" . $conn->error;}
 $conn->close();?>

Please note that each SQL statement must be separated by a semicolon.

Example (MySQLi - Procedure-oriented)

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB"; 
// 创建链接$conn = mysqli_connect($servername, $username, $password, $dbname);// 检查链接if (!$conn) {
    die("连接失败: " . mysqli_connect_error());}
 $sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES (&#39;John&#39;, &#39;Doe&#39;, &#39;john@example.com&#39;);";$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES (&#39;Mary&#39;, &#39;Moe&#39;, &#39;mary@example.com&#39;);";$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES (&#39;Julie&#39;, &#39;Dooley&#39;, &#39;julie@example.com&#39;)"; 
if (mysqli_multi_query($conn, $sql)) {
    echo "新记录插入成功";} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);}
 mysqli_close($conn);?>

Example (PDO)

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDBPDO"; 
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
    // 开始事务
    $conn->beginTransaction();    // SQL 语句
    $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) 
    VALUES (&#39;John&#39;, &#39;Doe&#39;, &#39;john@example.com&#39;)");    $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) 
    VALUES (&#39;Mary&#39;, &#39;Moe&#39;, &#39;mary@example.com&#39;)");    $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) 
    VALUES (&#39;Julie&#39;, &#39;Dooley&#39;, &#39;julie@example.com&#39;)"); 
    // 提交事务
    $conn->commit();    echo "新记录插入成功";}catch(PDOException $e){
    // 如果执行失败回滚
    $conn->rollback();    echo $sql . "<br>" . $e->getMessage();}
 $conn = null;?>

Using prepared statements

mysqli extension provides a second way to use for insert statements.

We can prepare statements and bind parameters.

mysql extension can send statements or queries to mysql database without data. You can nematically associate or "bind" variables.

Example (MySQLi uses prepared statements)

<?php$servername = "localhost";$username = "username";$password = "password";$dbname = "myDB"; 
// 创建连接$conn = new mysqli($servername, $username, $password, $dbname);// 检测连接if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);} else {
    $sql = "INSERT INTO MyGuests(firstname, lastname, email)  VALUES(?, ?, ?)"; 
    // 为 mysqli_stmt_prepare() 初始化 statement 对象
    $stmt = mysqli_stmt_init($conn); 
    //预处理语句
    if (mysqli_stmt_prepare($stmt, $sql)) {
        // 绑定参数
        mysqli_stmt_bind_param($stmt, &#39;sss&#39;, $firstname, $lastname, $email); 
        // 设置参数并执行
        $firstname = &#39;John&#39;;        $lastname = &#39;Doe&#39;;        $email = &#39;john@example.com&#39;;        mysqli_stmt_execute($stmt); 
        $firstname = &#39;Mary&#39;;        $lastname = &#39;Moe&#39;;        $email = &#39;mary@example.com&#39;;        mysqli_stmt_execute($stmt); 
        $firstname = &#39;Julie&#39;;        $lastname = &#39;Dooley&#39;;        $email = &#39;julie@example.com&#39;;        mysqli_stmt_execute($stmt);    }}?

We can see that modularization is used to solve the problem in the above example. We can achieve easier reading and management by creating code blocks.

Pay attention to the binding of parameters. Let's take a look at the code in mysqli_stmt_bind_param():

mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);

This function binds parameters Query and pass parameters to the database. The second parameter is "sss" . The following list shows the parameter types. The s character tells mysql that the parameter is a string.

can be the following four parameters:

i - integer

d - double precision floating point number

s - string

b - Boolean value

Each parameter must specify a type to ensure data security. Type judgment can reduce the risk of SQL injection vulnerabilities.

This article explains in detail how to insert multiple pieces of data through php. For more learning materials, please pay attention to the php Chinese website.

Related recommendations:

How to insert data through PHP MySQL

How to create a MySQL table through PHP

How to create a database through PHP MySQL

The above is the detailed content of How to insert multiple pieces of data through PHP MySQL. For more information, please follow other related articles on the PHP Chinese website!