讀取資料 

2024/07/31
2024/10/18 (調整內容)
2024/10/24 (微調內容)

資料庫設定

要先新增一個practice的資料庫(schema)，collation(定序)請選擇utf8_unicode_ci或者mysql預設的utf8_general_ci。有些同學會看網路上的文章使用utf8mb4_0900_ai_ci，但是，會有些同學的資料庫版本比較舊，不支援utf8mb4_0900_ai_ci。

• 理解MySQL中的字符集和排序規則

然後，利用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));

** 練習 **

• 利用phpMyAdmin

  • 建立資料庫

  • 建立資料表

  • 新增資料

連接資料庫

最簡單的連接資料庫的方法是透過MySQLi的procedural方式進行，由於我們在本地執行，所以，資料庫的伺服器就設為localhost，這邊的帳號、密碼跟學校機房的設定一樣。  

• PHP Connect to MySQL

先利用MySQLi產生一個connection的變數，並傳四個變數，伺服器名稱、帳號、密碼、資料庫名稱:

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

利用如果有錯誤，則將錯誤訊息印出:

if ($conn->connect_error) {

  die("Connection failed: " . $conn->connect_error);

}

完整的程式: testdb.php

注意，如果是在自己的電腦上執行，密碼可能不同，請修改為自己的資料庫密碼。

<?php

$servername = "localhost";

$dbname = "practice";

$dbUsername = "root";

$dbPassword = "12345678";

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

if (!$conn) {

  die("無法連線: " . mysqli_connect_error());

}

else {

  echo "成功連線!";

}

?>

如果看到:

Fatal error: Uncaught mysqli_sql_exception: Access denied for user 'root'@'localhost' (using password: YES) in C:\xampp\htdocs\web\testdb.php:7 Stack trace: #0 C:\xampp\htdocs\web\testdb.php(7): mysqli_connect('localhost', 'root', Object(SensitiveParameterValue), 'practice') #1 {main} thrown in C:\xampp\htdocs\web\testdb.php on line 7

那就是用了錯誤的密碼了。

在php 8.1以後，上面的寫法就會有問題。

• PHP 8.1: MySQLi: Default error mode set to exceptions

要改用try catch語法，這裡用到try catch的語法跟java很像 (詳參: PHP Exception Handling)。

$e是個Exception類別的物件，在php裡，使用物件裡的方法要使用「->」，不是「.」。

testdb.php

<?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()';

}

?>

** 練習 **

• 測試以上範例程式

Fetch with Associative Array

讀取資料使用的SQL語法是:

select * from job

接下來利用mysqli_query()新增一個query，query接受兩個參數: 連接資料庫的產生的$conn，以及$query，執行之後，結果會存放到$result變數裡。

$sql="select * from job";

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

如果利用print_r()印出$result，會知道$result裡並沒有資料:

mysqli_result Object ( [current_field] => 0 [field_count] => 4 [lengths] => [num_rows] => 0 [type] => 0 )

記得要先利用phpmyadmin新增一些資料，例如，如果有一筆資料的話:

mysqli_result Object ( [current_field] => 0 [field_count] => 4 [lengths] => [num_rows] => 1 [type] => 0 )

再利用mysqli_fetch_assoc()讀取到名稱為$row的associative array裡，再將讀取到的內容顯示在網頁上 (詳參: Select Data From MySQL)

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

  print_r($row);

}

會看到:

Array ( [postid] => 1 [company] => Microsoft [content] => 雲端工程師 [pdate] => 2024-10-18 )

可以看到每一筆資料$row是個associative array，所以，我們就可以利用associative array去取得每個欄位的內容:

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

   echo $row["company"];

   echo $row["content"];

   echo $row["pdate"];

}

最後，記得將資料庫的連結清空，否則，當多人使用系統時，會因為連結滿了，而產生錯誤:

mysqli_close($conn);

因為我們會一直重複使用資料庫的設定，所以，我們利用require引用db.php (詳參: PHP Include Files)，這樣未來如果改變這些參數，不用到所有檔案去改，只要改db.php就好了。

  require_once 'db.php';

db.php

<?php

$servername = "localhost";

$dbname = "practice";

$dbUsername = "root";

$dbPassword = "12345678";

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

?>

加上bootstrap之後，完整的query.php:

<?php

require_once "header.php";

try {

  require_once 'db.php';

  $sql="select * from job";

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

?>

<div class="container">

<table class="table table-bordered table-striped">

 <tr>

  <td>求才廠商</td>

  <td>求才內容</td>

  <td>日期</td>

 </tr>

 <?php

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

 <tr>

  <td><?=$row["company"]?></td>

  <td><?=$row["content"]?></td>

  <td><?=$row["pdate"]?></td>

 </tr>

 <?php

  }

 ?>

</table>

</div>

<?php

  mysqli_close($conn);

}

//catch exception

catch(Exception $e) {

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

}

require_once "footer.php";

?>

** 練習 **

• 測試以上範例程式

• 加到上週的介面，登入後才可以看到資料

** 作業 **

• 在資料庫裡新增user資料表，有兩個欄位

  • account  VARCHAR(20) (Primary Key)

  • password VARCHAR(20)

  • 並請新增5個帳號

• 修改login_process.php，並透過資料庫進行帳號驗證

  • 需要使用以下的sql

$sql = "select * from user where account = '$account'";

程式內容，請參考:

<?php

session_start();

//檢查是否取得POST內容

$account = $_POST['account'] ?? "N/A";

//因為db.php裡有$password

$password = $_POST['password'] ?? "N/A";

try {

  require_once 'db.php';

  $sql = "select * from user where account = '$account'";

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

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

    if ( ){

      echo "登入成功";

      $_SESSION["account"]=$account;

      header("Location: status.php");

    }

    else {

      echo "登入失敗";

      header("Location: login.php?msg=帳密錯誤");

    }

  }

  else {

    echo "登入失敗";

    header("Location: login.php?msg=帳密錯誤");

  }

  mysqli_close($conn);

}  //catch exception

catch(Exception $e) {

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

}

?>

• 請把剛剛的query.php整合到上週的作業中

• 記得匯出資料，下次上課就可以直接匯入資料，不用每次都重新建立資料庫

SQL Injection

有同學的登入驗證的寫法是:

$sql = "select * from user where account = '$account' and password = '$password'";

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

if (mysqli_num_rows($result) > 0) {

}

這樣的寫法會有SQL Injection的漏洞，所以，不要這樣寫。

• SQL注入

上面這樣寫會有SQL Injection的問題，有兩種處理方式，一種是先利用mysqli_real_escape_string，處理輸入內容，另一種是利用prepared statement，一般都建議採用prepared statement來解決。

• 【網頁安全】給網頁後端新人的 SQL Injection 介紹與防範 (PHP)

• [week 11] 資訊安全 - 雜湊與加密 & 常見攻擊：SQL Injection、XSS

• mysqli_stmt_prepare