mysqli

2021/05/05
2024/09/19 (補充資料)
2024/10/25 (更新內容)
2024/11/10 (更新內容)

簡介

在PHP裡存取資料庫有兩大類的寫法，第一種是MySQLi，Mysqli是php提供的mysql資料庫語法，i是加強版(improved)的意思。第二種是PDO (PHP Data Objects)，使用PDO的好處是如果連接的不是MySQL，只要更改連接的部分，其他的部分都不用更動，算是比較彈性。缺點是PDO的語法只提供物件導向的寫法。

• PHP Connect to MySQL 

以下採用MySQLi的語法，而MySQLi又有procedural跟object-oriented兩種寫法。

Procedural語法

以下先介紹procedural的語法。

• MySQL Improved Extension 

要先新增一個practice的資料庫(schema)，編碼請選擇utf8_unicode_ci。然後，利用SQL產生以下的資料表:

CREATE TABLE `practice`.`job` ( 

 `postid` INT NOT NULL AUTO_INCREMENT ,

 `company` VARCHAR(45) NOT NULL ,

 `content` TEXT NOT NULL ,

 `pdate` DATE NOT NULL ,

 PRIMARY KEY (`postid`));

標準的SQL語法:

CREATE TABLE practice.job ( 

 postid INT NOT NULL AUTO_INCREMENT ,

 company VARCHAR(45) NOT NULL ,

 content TEXT NOT NULL ,

 pdate DATE NOT NULL ,

 PRIMARY KEY (postid));

以下是procedural MySQLi基本語法的整理。

建立連結

• PHP Connect to MySQL 

  • PHP mysqli connect() Function

  • PHP mysqli connect_errno() Function

  • PHP mysqli connect_error() Function

php 8.1以後的寫法:

<?php

$servername = "localhost";

$dbname = "practice";

$dbUsername = "root";

$dbPassword = "12345678";

try {

  $conn = mysqli_connect($servername, $dbUsername, $dbPassword, $dbname);

  echo "成功連線!";

  mysqli_close($conn);

}

catch(Exception $e) {

  echo '無法連線:$e->getMessage()';

}

?>

建議把建立連結的部分獨立為db.php:

<?php

$servername = "localhost";

$dbname = "practice";

$dbUsername = "root";

$dbPassword = "12345678";

$conn = mysqli_connect($servername, $dbUsername, $dbPassword, $dbname);

?>

讀取資料

• PHP MySQL Select Data 

  • PHP mysqli query() Function

  • PHP mysqli fetch_all() Function

  • PHP mysqli fetch_array() Function

  • PHP mysqli fetch_assoc() Function

<?php

require_once "header.php";

try {

  require_once 'db.php';

  $sql="select * from job";

  $result = mysqli_query($conn, $sql);

  while($row = mysqli_fetch_assoc($result)) {

    echo $row["company"];

    echo $row["content"];

    echo $row["pdate"];

  }

  mysqli_close($conn);

}

//catch exception

catch(Exception $e) {

  echo 'Message: ' .$e->getMessage();

}

?>

新增資料

• PHP MySQL Insert Data 

<?php

$servername = "localhost";

$username = "username";

$password = "password";

$dbname = "myDB";

// Create connection

$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection

if (!$conn) {

  die("Connection failed: " . mysqli_connect_error());

}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('John', 'Doe', 'john@example.com')";

if (mysqli_query($conn, $sql)) {

  echo "New record created successfully";

} else {

  echo "Error: " . $sql . "<br>" . mysqli_error($conn);

}

mysqli_close($conn);

?>

為了避免SQL Injection以及效率，比較好的寫法是透過Prepared Statement

• PHP MySQL Prepared Statements 

  • mysqli_stmt_prepare

• PHP MySQL Prepared Statements 

• PHP MySQLi Prepared Statements Tutorial to Prevent SQL Injection 

<?php

/* Attempt MySQL server connection. Assuming you are running MySQL

server with default setting (user 'root' with no password) */

$link = mysqli_connect("localhost", "root", "", "demo");

// Check connection

if($link === false){

    die("ERROR: Could not connect. " . mysqli_connect_error());

}

// Prepare an insert statement

$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";

if($stmt = mysqli_prepare($link, $sql)){

    // Bind variables to the prepared statement as parameters

    mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);

    /* Set the parameters values and execute

    the statement again to insert another row */

    $first_name = "Hermione";

    $last_name = "Granger";

    $email = "hermionegranger@mail.com";

    mysqli_stmt_execute($stmt);

    /* Set the parameters values and execute

    the statement to insert a row */

    $first_name = "Ron";

    $last_name = "Weasley";

    $email = "ronweasley@mail.com";

    mysqli_stmt_execute($stmt);

    echo "Records inserted successfully.";

} else{

    echo "ERROR: Could not prepare query: $sql. " . mysqli_error($link);

}

// Close statement

mysqli_stmt_close($stmt);

// Close connection

mysqli_close($link);

?>

更新資料

• Update Data In a MySQL Table Using MySQLi and PDO 

<?php

$servername = "localhost";

$username = "username";

$password = "password";

$dbname = "myDB";

// Create connection

$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection

if (!$conn) {

  die("Connection failed: " . mysqli_connect_error());

}

$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

if (mysqli_query($conn, $sql)) {

  echo "Record updated successfully";

} else {

  echo "Error updating record: " . mysqli_error($conn);

}

mysqli_close($conn);

?>

為了避免SQL Injection以及效率，比較好的寫法是透過Prepared Statement

$sql="update job set company=?, content=? where postid=?";

mysqli_stmt_bind_param($stmt, "ssi", $company, $content, $postid);

$result = mysqli_stmt_execute($stmt);

刪除資料

• PHP MySQL Delete Data 

<?php

$servername = "localhost";

$username = "username";

$password = "password";

$dbname = "myDB";

// Create connection

$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection

if (!$conn) {

  die("Connection failed: " . mysqli_connect_error());

}

// sql to delete a record

$sql = "DELETE FROM MyGuests WHERE id=3";

if (mysqli_query($conn, $sql)) {

  echo "Record deleted successfully";

} else {

  echo "Error deleting record: " . mysqli_error($conn);

}

mysqli_close($conn);

?>

object-oriented語法

Object-oriented語法基本上跟Procedural語法類似，只是conn本身會是個物件，而不是一般的變數，所以，接下來就呼叫這個物件所屬的方法。另外，回傳的內容也可以是個物件，只是會比較複雜，請參考PDO。

在資料處理的部分，其實Object-oriented有較多的處理資料的方式，一般常用的是以PHP特有的Associative Array的方式呈現，也可以採用物件(類別)的方式呈現，採用物件的話，還要先寫類別，會比較麻煩，所以，通常採用Associative Array。

我們以產生連結來比較兩種種語法:

mysqli procedural

$conn = mysqli_connect($servername, $dbUsername, $dbPassword, $dbname);

mysqli object-oriented，因為是物件導向，要先從mysqil類別中產生一個connection，除此之外，參數都一樣。

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

以讀取資料為例，procedural的寫法$conn就會是mysqli_query的參數之一:

$sql = "SELECT id, firstname, lastname FROM MyGuests";

$result = mysqli_query($conn, $sql);

object-oriented的寫法$conn就是個物件，所有的方法都是以物件去呼叫:

$sql = "SELECT id, firstname, lastname FROM MyGuests";

$result = $conn->query($sql);

建立連結

• PHP Connect to MySQL 

  • PHP mysqli connect() Function

  • PHP mysqli connect_errno() Function

  • PHP mysqli connect_error() Function

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

讀取資料

• PHP MySQL Select Data 

  • PHP mysqli query() Function

  • PHP mysqli fetch_all() Function

  • PHP mysqli fetch_array() Function

  • PHP mysqli fetch_assoc() Function

$sql = "SELECT id, firstname, lastname FROM MyGuests";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

  // output data of each row

  while($row = $result->fetch_assoc()) {

    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";

  }

} else {

  echo "0 results";

}

$conn->close();

新增資料

• PHP MySQL Insert Data 

$sql = "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {

  echo "New record created successfully";

} else {

  echo "Error: " . $sql . "<br>" . $conn->error;

}

• PHP MySQL Prepared Statements

// prepare and bind

$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");

$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute

$firstname = "John";

$lastname = "Doe";

$email = "john@example.com";

$stmt->execute();

更新資料

• Update Data In a MySQL Table Using MySQLi and PDO 

$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

if ($conn->query($sql) === TRUE) {

  echo "Record updated successfully";

} else {

  echo "Error updating record: " . $conn->error;

}

刪除資料

• PHP MySQL Delete Data 

// sql to delete a record

$sql = "DELETE FROM MyGuests WHERE id=3";

if ($conn->query($sql) === TRUE) {

  echo "Record deleted successfully";

} else {

  echo "Error deleting record: " . $conn->error;

}