PHP MySQL insert multiple pieces of data
We learned how to add data to the data table in the previous section, one by one. Can we add more pieces of data at a time? That's for sure. In this section, we will learn how to add multiple pieces of data at once.
mysqli_multi_query() function can be used to execute multiple SQL statements.
Let us look directly at the example
Example
Added three new records to our previous "MyGuests" table:
<?php
header("Content-type:text/html;charset=utf-8"); //設(shè)置編碼
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test";
// 創(chuàng)建鏈接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 檢查鏈接
if (!$conn) {
die("連接失敗: " . mysqli_connect_error());
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('tom', 'Doe', '12032047@asd.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 "新記錄插入成功";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>Program running results:
New records inserted Success
Open your data table and take a look:
There are three more new data.
Using prepared statements
The mysqli extension provides a second way to insert statements.
We can prepare statements and bind parameters.
mysql extension can send statements or queries to the mysql database without data. You can nematically associate or "bind" variables.
Example
##Using prepared statements
<?php
header("Content-type:text/html;charset=utf-8"); //設(shè)置編碼
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test";
// 創(chuàng)建連接
$conn = new mysqli($servername, $username, $password, $dbname);
// 檢測(cè)連接
if ($conn->connect_error) {
die("連接失敗: " . $conn->connect_error);
} else {
$sql = "INSERT INTO MyGuests VALUES(?, ?, ?)";
// 為 mysqli_stmt_prepare() 初始化 statement 對(duì)象
$stmt = mysqli_stmt_init($conn);
//預(yù)處理語(yǔ)句
if (mysqli_stmt_prepare($stmt, $sql)) {
// 綁定參數(shù)
mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);
// 設(shè)置參數(shù)并執(zhí)行
$firstname = 'liu';
$lastname = 'Doe';
$email = 'john@example.com';
mysqli_stmt_execute($stmt);
$firstname = 'zhang';
$lastname = 'Moe';
$email = 'mary@example.com';
mysqli_stmt_execute($stmt);
$firstname = 'li';
$lastname = 'Dooley';
$email = 'julie@example.com';
mysqli_stmt_execute($stmt);
}
}
?>Use prepared statements
VALUESIt is not followed by specific data, but with? Instead, how many are on top? , the preprocessing statement mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email), the second parameter must have several "s", and the second parameter represents Data type: s represents a string, and there are the following types:
· i - integer · d - double-precision floating point number· s - string · b - Boolean valueWhat does it mean? For example, when we are building a table, the firstname field is of VARCHAR type, so it must be "S". If we are building a table When the firstname field is of int type, it must be "i".Each parameter must specify a type to ensure data security. Type judgment can reduce the risk of SQL injection vulnerabilities. Let us explain PHP prepared statements in the next section.
