PHP MySQL inserts multiple pieces of data, phpmysql inserts multiple pieces_PHP tutorial

PHP MySQL inserts multiple pieces of data, phpmysql inserts multiple pieces of data

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

The 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("Connection failed: " . $conn->connect_error);
} 

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', 'mary@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', 'julie@example.com')";

if ($conn->multi_query($sql) === TRUE) {
    echo "New records created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Example (MySQLi - Procedure Oriented)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建链接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检查链接
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', 'mary@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', 'julie@example.com')";

if (mysqli_multi_query($conn, $sql)) {
    echo "New records created successfully";
} 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 ('John', 'Doe', 'john@example.com')");
    $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) 
    VALUES ('Mary', 'Moe', 'mary@example.com')");
    $conn->exec("INSERT INTO MyGuests (firstname, lastname, email) 
    VALUES ('Julie', 'Dooley', 'julie@example.com')");

    // commit the transaction
    $conn->commit();
    echo "New records created successfully";
    }
catch(PDOException $e)
    {
    // roll back the transaction if something failed
    $conn->rollback();
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?>

Use prepared statements

The mysqli extension provides a second way to insert statements.

We can prepare statements and bind parameters.

The mysql extension can send statements or queries to the 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";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} else {
    $sql = "INSERT INTO MyGuests VALUES(?, ?, ?)";

    // 为 mysqli_stmt_prepare() 初始化 statement 对象
    $stmt = mysqli_stmt_init($conn);

    //预处理语句
    if (mysqli_stmt_prepare($stmt, $sql)) {
        // 绑定参数
        mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);

        // 设置参数并执行
        $firstname = 'John';
        $lastname = 'Doe';
        $email = 'john@example.com';
        mysqli_stmt_execute($stmt);

        $firstname = 'Mary';
        $lastname = 'Moe';
        $email = 'mary@example.com';
        mysqli_stmt_execute($stmt);

        $firstname = 'Julie';
        $lastname = 'Dooley';
        $email = 'julie@example.com';
        mysqli_stmt_execute($stmt);
    }
}
?>

We can see that modularity is used to solve problems in the above examples. We can achieve easier reading and management by creating code blocks.

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

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

This function binds parameter query and passes the 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.

This argument may be one of four types:

• i - integer
• d - double
• s - string
• b - BLOB

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

Original address:/php/php_mysql_insert_multiple.html

php mysql related reading materials:

• Introduction to PHP MySQL
• PHP connects to MySQL
• php creates database
• php create table
• php mysq insert data
• PHP MySQL insert multiple pieces of data
• PHP MySQL prepared statements
• php mysql read data
• php mysql where
• PHP MySQL Order By
• PHP MySQL Update
• PHP MySQL Delete
• php ODBC

http://www.bkjia.com/PHPjc/1125994.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1125994.htmlTechArticlePHP MySQL inserts multiple pieces of data, phpmysql inserts multiple pieces of data. Use MySQLi and PDO to insert multiple pieces of data into MySQL mysqli_multi_query() Functions can be used to execute multiple SQL statements. The following examples are directed to...