PHP 與 MySQL 的互動：讀取、新增、刪除、編輯資料

#PHP #MySQL

Posted by saffran on 2021-02-25

後端最基本的這四個功能，就是 CRUD
Create 新增
Read 讀取
Update 編輯
Delete 刪除

讀取資料（PHP 與 MySQL 的互動）

在 PHP 裡面 select MySQL 的資料

從資料庫取得現在的時間

透過 PHP 從 MySQL 資料庫拿資料的步驟如下：

  1. 先下 query()
    $conn->query('select now();'); 就是在「向資料庫拿資料」
  2. 檢查 $result 是否有拿到結果
    如果沒有拿到任何結果（$result 是空的）就代表 query() 發生錯誤。這時，就把錯誤印出來，程式碼不再繼續往下執行
  3. 如果 $result 有拿到結果，就用 $row = $result->fetch_assoc();
    fetch_assoc() 是在把相對應的結果取出來放到 $row 裡面。$row 就是 MySQL query 之後的結果，會根據我 select 的東西給我一個 array，陣列的 key 就是「我 select 的東西 now()」，value 就是「now() 所對應到的值」

now() 是 MySQL 提供的一個 function，可以取得現在的時間

data.php:

<?php
  require_once('conn.php');
  $result = $conn->query('select now() as n;');
  if (!$result) {
    die($conn->error);
  }

  $row = $result->fetch_assoc();
  print_r($row);
  echo '<br> now: ' . $row['n'];
?>

output:

取得 table 的第一筆資料

現在，在 MySQL 資料庫裡有這三筆資料：

<?php
  require_once('conn.php');
  $result = $conn->query('select * from users;');
  if (!$result) {
    die($conn->error);
  }

  $row = $result->fetch_assoc();
  print_r($row);
?>

output:
可以看到，每執行一次 $row = $result->fetch_assoc(); 只會印出一筆資料而已

因此，如果想要取得每一筆資料，就需要跑 while 迴圈

用 while 迴圈取得 table 中的每一筆資料

在跑每圈 while 迴圈時，實際上是分成兩個步驟

  1. 步驟一：每圈都會先執行一次 $result->fetch_assoc()，並把結果放到 $row
  2. 步驟二：while 實際上會判斷的是 $row，判斷 $row 是否為空（當資料都拿完後，$row 就會是空的），$row 是空的就是 false
<?php
  require_once('conn.php');
  $result = $conn->query('select * from users;');
  if (!$result) {
    die($conn->error);
  }

  while ($row = $result->fetch_assoc()) {
    print_r($row);
  }
?>

output:
可以看到，這樣就可以拿到 table 中的每一筆資料了

把資料用我想要的樣子顯示出來：

<?php
  require_once('conn.php');
  $result = $conn->query('select * from users;');
  if (!$result) {
    die($conn->error);
  }

  while ($row = $result->fetch_assoc()) {
    echo 'id: '. $row['id'] . '<br>';
    echo 'username: ' . $row['username'] . '<br>';
  }
?>

output:

新增資料（PHP 與 MySQL 的互動）

add.php:

  • insert into users(username) values("apple") 這就是一個 SQL query
  • 要放入的 values 會是一個字串，所以要用單引號或是雙引號包起來
<?php
  require_once('conn.php');
  $result = $conn->query('insert into users(username) values("apple")');
  if (!$result) {
    die($conn->error);
  }

  print_r($result);

?>

print_r($result); 的 output 會是 1，
1 就代表 true （有成功）的意思

會發現，當我在 add.php 的頁面第二次重新整理後，回到 phpMyAdmin 看，就會出現第二個 apple

優化上方的程式碼

add.php:

  • 把要放入的 values 用變數 $username 取代

在 debug 時，可以先把 $sql 印出來，看是不是正確的 SQL query

把 SQL query 獨立出來變成一個變數 $sql，這樣程式碼的可讀性比較高。在 debug 時，也可以先把 $sql 印出來 echo $sql;，看是不是正確的 SQL query

debug 完之後，記得要把 echo $sql; 拿掉

用 sprintf() 函式來寫 SQL query，提高程式碼可讀性

在 PHP 有一個叫做 sprintf() 的函式，建議是把 SQL query 用 sprintf() 來寫，讓整個 SQL query 字串更好寫也更好看懂：

  • 第一個參數是字串，把「動態要塞值進去的地方」變成 %d 和 "%s"，類似於 placeholder 的感覺
    • %d 代表：我要放入的是一個 number （這裡的 d 是 decimal 十進位的意思）
    • "%s" 代表：我要放入的是一個 string
  • 從第二個參數開始依序放「我要塞進去的值」

會按照順序，把 15 帶到 %d 的位置，把 $username 帶到 "%s" 的位置

<?php
  require_once('conn.php');
  $username = 'apple';
  $sql = sprintf(
    'insert into users(id, username) values(%d, "%s")',
    15,
    $username
  );
  echo $sql;
  exit();
  $result = $conn->query($sql);
  if (!$result) {
    die($conn->error);
  }

  print_r($result);

?>

組出來的 SQL query 會長這樣：

動態新增 username 到資料庫的表單

index.php:

<?php
  require_once('conn.php');
  $result = $conn->query('select * from users;');
  if (!$result) {
    die($conn->error);
  }

  while ($row = $result->fetch_assoc()) {
    echo 'id: '. $row['id'] . '<br>';
    echo 'username: ' . $row['username'] . '<br>';
  }
?>

<h2>新增 user</h2>
<form method="POST" action="add.php">
  username: <input name="username">
  <input type="submit">
</form>

加上「自動跳轉的 response Header」

header('Location: index.php'); 意思就是：我要回傳一個 response Header 叫做 Location: index.php。瀏覽器接收到這個 response Header 後，就知道我的目的是要跳轉到 index.php，因此就會自動幫我跳轉回到 index.php 這個檔案去 （因為跳轉的太快了，甚至不會看到中間的 add.php 的畫面，就直接跳轉到 index.php 去了）

add.php:

<?php
  require_once('conn.php');

  if (empty($_POST['username'])) {
    die('請輸入 username');
  }
  $username = $_POST['username'];
  $sql = sprintf(
    'insert into users(username) values("%s")',
    $username
  );
  echo 'sql: ' . $sql. '<br>';
  $result = $conn->query($sql);
  if (!$result) {
    die($conn->error);
  }

  // 如果有新增成功
  echo '新增成功！';
  header('Location: index.php');
?>

錯誤處理

上面 add.php 的程式碼，做錯誤處理的地方有兩個：

  1. 檢查 $_POST['username'] 是否為空值
  2. 檢查 SQL query 是否有執行成功，也就是 $conn->query($sql); 這段

如果 SQL query 沒有執行成功，也就是 $result 是 false，那就會執行 die($conn->error);

現在，我把 username 欄位設為 unique （代表：不能有重複的 username）
然後我到 index.php 輸入一個重複的 username 叫做 aaa，按下 submit 後就會出現一行錯誤訊息「Duplicate entry 'aaa' for key 'username'」，就是因為在執行到 $conn->query($sql); 時發生錯誤（$result 會是 false），因此這行錯誤訊息就是從 die($conn->error); 這行印出來的

排序「資料庫的讀取結果」

「資料庫的讀取結果」的排序不一定會按照 id 順序，如果想要按照 id 排序的話，就在 index.php 的 SQL query 加上 order by id asc 或是 order by id desc

  • asc 是「由小到大」排列
    ascending 就是 increasing 的意思
<?php
  require_once('conn.php');
  $result = $conn->query('select * from users order by id asc;');
  if (!$result) {
    die($conn->error);
  }

  while ($row = $result->fetch_assoc()) {
    echo 'id: '. $row['id'] . '<br>';
    echo 'username: ' . $row['username'] . '<br>';
  }
?>

<h2>新增 user</h2>
<form method="POST" action="add.php">
  username: <input name="username">
  <input type="submit">
</form>
  • desc 是「由大到小」排列
    descending 就是下降的意思
<?php
  require_once('conn.php');
  $result = $conn->query('select * from users order by id desc;');
  if (!$result) {
    die($conn->error);
  }

  while ($row = $result->fetch_assoc()) {
    echo 'id: '. $row['id'] . '<br>';
    echo 'username: ' . $row['username'] . '<br>';
  }
?>

<h2>新增 user</h2>
<form method="POST" action="add.php">
  username: <input name="username">
  <input type="submit">
</form>

新增資料的程式碼，完整版如下：

conn.php:

<?php
  $server_name = 'localhost';
  $username = 'saffran';
  $password = 'rox';
  $db_name = 'saffran_db';

  $conn = new mysqli($server_name, $username, $password, $db_name);

  if ($conn->connect_error) {
    die('資料庫連線錯誤：' . $conn->connect_error);
  }

  $conn->query('SET NAMES UTF8');
  $conn->query('SET time_zone = "+8:00"');
?>

index.php:

<?php
  require_once('conn.php');
  $result = $conn->query('select * from users order by id desc;');
  if (!$result) {
    die($conn->error);
  }

  while ($row = $result->fetch_assoc()) {
    echo 'id: '. $row['id'] . '<br>';
    echo 'username: ' . $row['username'] . '<br>';
  }
?>

<h2>新增 user</h2>
<form method="POST" action="add.php">
  username: <input name="username">
  <input type="submit">
</form>

add.php:

<?php
  require_once('conn.php');

  if (empty($_POST['username'])) {
    die('請輸入 username');
  }
  $username = $_POST['username'];
  $sql = sprintf(
    'insert into users(username) values("%s")',
    $username
  );
  $result = $conn->query($sql);
  if (!$result) {
    die($conn->error);
  }

  // 如果有新增成功
  header('Location: index.php');
?>

刪除資料（PHP 與 MySQL 的互動）

index.php:

在 id 旁邊印出刪除的按鈕：

echo '<a href="delete.php?id=' . $row['id'] . '">刪除</a>';
用 GET（query string）的方式把 id 帶去給 delete.php 這個檔案

通常，刪除功能會用 POST 來傳參數（傳多個參數尤其要用 POST），這邊只是為了方便講解所以用 GET 來傳參數
如果要用 POST 來傳參數的話，就要把 '<a href="delete.php?id=' . $row['id'] . '">刪除</a>' 這段改成 form 的形式

<?php
  require_once('conn.php');
  $result = $conn->query('select * from users order by id asc;');
  if (!$result) {
    die($conn->error);
  }

  while ($row = $result->fetch_assoc()) {
    echo 'id: ' . $row['id'];
    echo '<a href="delete.php?id=' . $row['id'] . '">刪除</a>';
    echo '<br>';
    echo 'username: ' . $row['username'] . '<br>';
  }
?>

<h2>新增 user</h2>
<form method="POST" action="add.php">
  username: <input name="username">
  <input type="submit">
</form>

output:

這時，當我點擊 id: 7 的刪除按鈕時，就會跳到 http://localhost:8080/saffran/delete.php?id=7 這裡（delete.php 就可以用 $_GET 這個變數來拿到 id 的值）

在 delete.php 就這樣寫，就寫好刪除的功能了：

<?php
  require_once('conn.php');

  if (empty($_GET['id'])) {
    die('請輸入 id');
  }
  $id = $_GET['id'];
  $sql = sprintf(
    'delete from users where id = %d',
    $id
  );
  $result = $conn->query($sql);
  if (!$result) {
    die($conn->error);
  }

  // 如果刪除成功
  header('Location: index.php');
?>

刪除一個不存在的資料，不算是錯誤

現在，在資料庫裡面有下面這些資料：

我在網址列執行 http://localhost:8080/saffran/delete.php?id=330 並不會跳出錯誤訊息

或者是
我在 phpMyAdmin 裡面執行 DELETE FROM users WHERE id = 330 這個 query，結果也會是「執行成功」

雖然我在資料庫裡面沒有 id = 330 這筆資料，但是這個 query 還是會執行成功，只是它是刪除了一個不存在的資料（影響了 0 列），但這不算是錯誤

執行錯誤指的是：例如 table 名稱輸入錯誤、欄位名稱輸入錯誤等等

用 $conn->affected_rows 來得知「影響了幾列」

  • 如果 $conn->affected_rows 是 0，就代表「影響了 0 列」，刪除了一筆不存在的資料
  • 如果 $conn->affected_rows >= 1，就代表「有刪除了 xx 筆資料庫的資料」

delete.php:

<?php
  require_once('conn.php');

  if (empty($_GET['id'])) {
    die('請輸入 id');
  }
  $id = $_GET['id'];
  $sql = sprintf(
    'delete from users where id = %d',
    $id
  );
  echo $sql . '<br>';
  $result = $conn->query($sql);
  if (!$result) {
    die($conn->error);
  }

  // 影響了幾列
  if ($conn->affected_rows >= 1) {
    echo '已成功刪除此筆資料！';
  } else {
    echo '查無此資料';
  }

  // 如果刪除成功
  // header('Location: index.php');
?>

編輯資料（PHP 與 MySQL 的互動）

index.php:

<?php
  require_once('conn.php');
  $result = $conn->query('select * from users order by id asc;');
  if (!$result) {
    die($conn->error);
  }

  while ($row = $result->fetch_assoc()) {
    echo 'id: ' . $row['id'];
    echo '<a href="delete.php?id=' . $row['id'] . '">刪除</a>';
    echo '<br>';
    echo 'username: ' . $row['username'] . '<br>';
  }
?>

<h2>新增 user</h2>
<form method="POST" action="add.php">
  username: <input name="username">
  <input type="submit">
</form>

<h2>編輯 user</h2>
<form method="POST" action="update.php">
  id: <input name="id">
  username: <input name="username">
  <input type="submit">
</form>

輸入 id 和 username 就可以編輯資料

update.php:
'update users set username = "%s" where id = %d' 這句 SQL query 的意思是：會找到 where id = %d 所指定的 id，再針對該 id 的那一列去更新它的 username

<?php
  require_once('conn.php');

  if (empty($_POST['id'] || empty($_POST['username']))) {
    die('請輸入 id 與 username');
  }
  $id = $_POST['id'];
  $username = $_POST['username'];
  $sql = sprintf(
    'update users set username = "%s" where id = %d',
    $username,
    $id
  );
  echo $sql . '<br>';
  $result = $conn->query($sql);
  if (!$result) {
    die($conn->error);
  }

  // 如果編輯成功
  header('Location: index.php');
?>

PHP 與 MySQL 指令快速查表

<?php
  // 連線資料庫
  $server_name = 'localhost';
  $username = 'huli';
  $password = 'huli';
  $db_name = 'huli';

  $conn = new mysqli($server_name, $username, $password, $db_name);

  if ($conn->connect_error) {
    die('資料庫連線錯誤:' . $conn->connect_error);
  }

  $conn->query('SET NAMES UTF8');
  $conn->query('SET time_zone = "+8:00"');

  // 新增資料
  $username = $_POST['username'];
  $sql = sprintf(
    "insert into users(username) values('%s')",
    $username
  );
  $result = $conn->query($sql);
  if (!$result) {
    die($conn->error);
  }

  // 讀取資料
  $result = $conn->query("SELECT * FROM users ORDER BY id ASC;");
  if (!$result) {
    die($conn->error);
  }

  while ($row = $result->fetch_assoc()) {
    echo "id:" . $row['id'];
  }

  // 修改資料
  $id = $_POST['id'];
  $username = $_POST['username'];
  $sql = sprintf(
    "update users set username='%s' where id=%d",
    $username,
    $id
  );
  echo $sql . '<br>';
  $result = $conn->query($sql);
  if (!$result) {
    die($conn->error);
  }

  // 刪除資料
  $id = $_GET['id'];
  $sql = sprintf(
    "delete from users where id = %d",
    $id
  );
  $result = $conn->query($sql);
  if (!$result) {
    die($conn->error);
  }

  if ($conn->affected_rows >= 1) {
    echo '刪除成功';
  } else {
    echo '查無資料';
  }
?>

#PHP #MySQL

saffran




Related Posts

資料傳輸物件Data Transfer Object(DTO) 簡介

資料傳輸物件Data Transfer Object(DTO) 簡介

 jnjjiu-wei
Linux指令整理

Linux指令整理

 sleepingjun
Day 17－OOP & Quiz Game

Day 17－OOP & Quiz Game

 pei_______

Comments