PHPMySQL插入多條數據

使用MySQLi 和PDO 向MySQL 插入多條數據

mysqli_multi_query() 函數可用於執行多條SQL語句。

以下實例為"MyGuests" 表格新增了三筆新的記錄:

實例(MySQLi - 物件導向)

<?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 ('John', 'Doe', '[email protected]'); " ; $sql .= " INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', '[email protected]'); " ; $sql .= " INSERT INTO MyGuests (firstname, lastname, email)VALUES ('Julie', 'Dooley', '[email protected]') " ; if ( $conn -> multi_query ( $sql ) === TRUE ) { echo "新記錄插入成功" ; } else { echo " Error: " . $sql . " <br> " . $conn -> error ; } $conn -> close ( ) ; ?>

	請注意，每個SQL語句必須用分號隔開。

實例(MySQLi - 面向過程)

<?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 ('John', 'Doe', '[email protected]'); " ; $sql .= " INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', '[email protected]'); " ; $sql .= " INSERT INTO MyGuests (firstname, lastname, email)VALUES ('Julie', 'Dooley', '[email protected]') " ; if ( mysqli_multi_query ( $conn , $sql ) ) { echo "新記錄插入成功" ; } else { echo " Error: " . $sql . " <br> " . mysqli_error ( $conn ) ; } mysqli_close ( $conn ) ; ?>

實例(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', '[email protected]') " ) ; $conn -> exec ( " INSERT INTO MyGuests (firstname , lastname, email) VALUES ('Mary', 'Moe', '[email protected]') " ) ; $conn -> exec ( " INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Julie', 'Dooley', '[email protected]') " ) ; //提交事務 $conn -> commit ( ) ; echo "新記錄插入成功" ; } catch ( PDOException $e ) { //如果執行失敗回滾 $conn -> rollback ( ) ; echo $sql . " <br> " . $e -> getMessage ( ) ; } $conn = null ; ?>

使用預處理語句

mysqli 擴充功能提供了第二種方式用於插入語句。

我們可以預處理語句及綁定參數。

mysql 擴充功能可以不帶資料傳送語句或查詢到mysql資料庫。 你可以向列關聯或"綁定" 變數。

實例(MySQLi 使用預處理語句)

<?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 , ' sss ' , $firstname , $lastname , $email ) ; //設定參數並執行 $firstname = ' John ' ; $lastname = ' Doe ' ; $email = ' [email protected] ' ; mysqli_stmt_execute ( $stmt ) ; $firstname = ' Mary ' ; $lastname = ' Moe ' ; $email = ' mary @example.com ' ; mysqli_stmt_execute ( $stmt ) ; $firstname = ' Julie ' ; $lastname = ' Dooley ' ; $email = ' [email protected] ' ; mysqli_stmt_execute ( $stmt ) ; } } ?>

我們可以看到上述實例中使用模組化來處理問題。我們可以透過建立程式碼區塊來實現更簡單的讀取和管理。

注意參數的綁定。讓我們看下mysqli_stmt_bind_param() 中的程式碼：

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

此函數綁定參數查詢並將參數傳遞給資料庫。第二個參數是"sss" 。以下列表展示了參數的類型。 s 字元告訴mysql 參數是字串。

可以是以下四種參數:

i - 整數

d - 雙精確度浮點數

s - 字串

b - 布林值

每個參數必須指定類型，以確保資料的安全性。透過類型的判斷可以減少SQL注入漏洞帶來的風險。