[BE101] 用 PHP 與 MySQL 學習後端基礎 筆記

[iamvince24]

前言：內容為 [BE101] 用 PHP 與 MySQL 學習後端基礎 的筆記

[iamvince24]

初探 PHP

必看！在開始前一定要做的事：Disable cache

• Disable cache 打勾
  • 清掉快取

從前端傳資料給後端：GET 與 POST

• foundation

  
  // data.php
  <?php
  echo "Hello! <br>";
  
  if(isset($_GET['a'])){
      echo "a:" . $_GET['a'] . '<br>';
  }
  
  if(isset($_GET['b'])){
      echo "b:" . $_GET['b'] . '<br>';
  }
  
  print_r($_GET);
  ?>

// index.php <?php echo "My name is Vince!"; ?>

a:
age:

      - 送出後，會自動把資料帶到網址頁
         - [`http://localhost/vince/be101/data.php?a=dsfds&age=sdfsd`](http://localhost/vince/be101/data.php?a=dsfds&age=sdfsd)
- `GET`
```php
// data.php
<?php
    if(!isset($_GET['name']) || !isset($_GET['age'])){
        echo "資料有缺，請再次填寫<br>";
        exit();
    } 
    echo "Hello!" . $_GET['name'] . "<br>";
    echo "Your age is" . $_GET['age'] . "<br>";
?>

// index.php
<?php 
    echo "My name is Vince!";
?>

<form method="GET" action="data.php">
    name: <input name="name" />
    age: <input name="age" />
    <input type="submit" />
</form>

  - 送出後，會自動把資料帶到網址頁
     - [`http://localhost/vince/be101/data.php?a=dsfds&age=sdfsd`](http://localhost/vince/be101/data.php?a=dsfds&age=sdfsd)

• POST

  
  // data.php
  <?php
  if(empty($_POST['name']) || empty($_POST['age'])){
      echo "資料有缺，請再次填寫<br>";
      exit();
  } 
  echo "Hello!" . $_POST['name'] . "<br>";
  echo "Your age is" . $_POST['age'] . "<br>";
  ?>

// index.php <?php echo "My name is Vince!"; ?>

name: age:

![Image.png](https://res.craft.do/user/full/6e51b78d-bb10-9598-78bc-b1dd033f3f34/doc/6D704287-C98B-4432-B940-9B07D5BA0F13/21425F70-FEDB-4C57-B959-B1BD005866AF_2/yzXnXjCJiyAkpNpWhF18DFjqE1e6vCtfn6MoGEYcUq4z/Image.png)

      - 直接送出會這樣
   - Method 變成 POST ，所以要變成 `$_POST`
- 混用
```php
// data.php
<?php
    if(empty($_POST['name']) || empty($_POST['age'])){
        echo "資料有缺，請再次填寫<br>";
        exit();
    } 
    echo "Hello!" . $_POST['name'] . "<br>";
    echo "Your age is" . $_POST['age'] . "<br>";
    echo "Your school is" . $GET['school'];
?>

// index.php
<?php 
    echo "My name is Vince!";
?>

<form method="POST" action="data.php">
    name: <input name="name" />
    age: <input name="age" />
    school: <input name="school" />
    <input type="submit" />
</form>

補充一下

影片裡面有一段示範 $_GET 有沒有設置的，是用了 isset，然後有個細節沒有講清楚

假設網址是 ?a=&b=，只有傳 key 沒有傳 value，isset 的結果依舊會是 true

必須連 key 都沒有傳，isset 才會是 false

因此針對這種檢查，會推薦使用 empty 來檢測，因為 empty 可以順便檢測空字串的狀況

從 PHP 連線到 MySQL 資料庫

// conn.php

<?php
    $server_name = 'localhost';
    $username = 'vince';
    $password = 'vince';
    $db_name = 'vince';

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

• $conn->connect_error
  • 在物件上存取屬性用 → 符號
• die
  • 把後面的訊息輸出，後面都不會執行。
• 建立 conn.php 檔案並在其他檔案引入 require_once('conn.php');
  • 建議把 conn.php 這個檔案加入 .gitignore
• $conn->query('SET NAMES UTF8');
  • 設定編碼，要不然用中文會有錯誤。
• $conn->query('SET time_zone = "+8:00"');
  • 把資料庫時區設成台灣的時區。

PHP 與 MySQL 的互動：讀取資料

<?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>';
  }
?>

// data.php

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

    $result = $conn->query("select now() as n;");
    if (!$result) {
        die($conn->error);
    }

    // print_r($result);

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

• 要拿資料
  • 第一步下 query
  • 第二步檢查有沒有錯誤，有沒有拿到結果
  • 才會拿到 key 與 value

// 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>';
    }
?>

<form method="POST" action="data.php">
    name: <input name="name" />
    age: <input name="age" />
    school: <input name="school" />
    <input type="submit" />
</form>

[iamvince24]

PHP 與 MySQL 的互動：讀取資料

<?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>';
  }
?>

// data.php

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

    $result = $conn->query("select now() as n;");
    if (!$result) {
        die($conn->error);
    }

    // print_r($result);

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

• 要拿資料
  • 第一步下 query
  • 第二步檢查有沒有錯誤，有沒有拿到結果
  • 才會拿到 key 與 value

// 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>';
    }
?>

<form method="POST" action="data.php">
    name: <input name="name" />
    age: <input name="age" />
    school: <input name="school" />
    <input type="submit" />
</form>

PHP 與 MySQL 的互動：新增資料

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

    $result = $conn->query("insert into users(username) value('apple')");

    if (!$result) {
        die($conn->error);
    }

    print_r($result) // 1, successed
?>

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

    $username = 'apple';
    $sql = "insert into users(username) values('" . $username . "')";
    echo $sql;

    $result = $conn->query($sql);
    if (!$result) {
        die($conn->error);
    }
    print_r($result);
?>

• 指定 id

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

• 搭配表單

  
  // 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>Adding 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
);

echo 'SQL:' . $sql . '<br>';
$result = $conn->query($sql);
if (!$result) {
    die($conn->error);
}

echo 'Success';

header("Location: index.php"); // 自動跳轉到 index.php

?>

      - `header("Location: index.php"); // 自動跳轉到 index.php`
- 設成唯一
   - 不會有重複的 username

![Image.png](https://res.craft.do/user/full/6e51b78d-bb10-9598-78bc-b1dd033f3f34/doc/6D704287-C98B-4432-B940-9B07D5BA0F13/2FF293E3-1E61-4CFB-AA32-F9F59A9D3B5C_2/yGF1zHrnuSKLlnpO9ntFvcfP3vnVbdNcaxnIhu9von8z/Image.png)

      - 之後若重複寄會跳出錯誤
         - `SQL:insert into users(username) values('ccc')`
- Set order
   - ASC 漸大
   - DESC 漸小
```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'] . '<br>';
        echo "username:" . $row['username'] . '<br>';
    }
?>
<h2>Adding User</h2>
<form method="POST" action="add.php">
    username: <input name="username" />
    <input type="submit" />
</form>

PHP 與 MySQL 的互動：刪除資料

• 利用 GET 把資料帶過去 delete.php 的檔案

  <?php 
  require_once('conn.php');
  
  if (empty($_GET['id'])){
      die('請輸入 id');
  }
  
  $id = $_GET['id'];
  $sql = sprintf(
      "delete from user where id=%d",
      $id
  );
  echo $sql;
  
  $result = $conn->query($sql);
  if (!$result) {
      die($conn->error);
  }
  header("Location: index.php"); // 自動跳轉到 index.php
  ?>

• 利用 affected_rows 來判斷剛剛的操作影響了多少筆資料？

  <?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 的互動：編輯資料

<?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 '查無資料';
  }
?>