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







Related Posts

Android Status bar 深色模式設定

Android Status bar 深色模式設定

平面設計大叔的轉職之路

平面設計大叔的轉職之路

2. Entity Framework - CodeFirst

2. Entity Framework - CodeFirst


Comments