DB-Edit MySQL Record（讀取資料回填 HTML 表單再寫回）(tc)

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,          -- single-line text
  bio TEXT,                            -- multi-line text
  is_active TINYINT(1) DEFAULT 0,      -- checkbox (0/1)
  gender ENUM('male','female','other') -- radio button
);

<?php
// db.php — reusable PDO connection
function getPDO(): PDO {
    $host = '127.0.0.1';
    $db   = 'mydb';
    $user = 'dbuser';
    $pass = 'secret';
    $dsn  = "mysql:host=$host;dbname=$db;charset=utf8mb4";
    return new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
}

// escape helper — always escape before output to HTML
function e(?string $v): string {
    return htmlspecialchars($v ?? '', ENT_QUOTES, 'UTF-8');
}

<?php
require 'db.php';

// Step 1: read & validate the id
$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
if (!$id) { http_response_code(400); exit('Invalid record ID'); }

$stmt = getPDO()->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute(['id' => $id]);
$u = $stmt->fetch();

if (!$u) { http_response_code(404); exit('Record not found'); }
?>
<form method="post" action="save.php">
  <input type="hidden" name="id" value="<?= $u['id'] ?>">

  <!-- 1. single-line text field: value attribute -->
  <label>Name
    <input type="text" name="name" value="<?= e($u['name']) ?>">
  </label>

  <!-- 2. multi-line text field: content goes between the tags, no value attribute -->
  <label>Bio
    <textarea name="bio" rows="5" cols="40"><?= e($u['bio']) ?></textarea>
  </label>

  <!-- 3. checkbox: checked attribute -->
  <label>Active
    <input type="checkbox" name="is_active" value="1"
      <?= $u['is_active'] ? 'checked' : '' ?>>
  </label>

  <!-- 4. radio button: compare the value, add checked when it matches -->
  <fieldset>Gender
    <label><input type="radio" name="gender" value="male"
      <?= $u['gender'] === 'male' ? 'checked' : '' ?>> Male</label>
    <label><input type="radio" name="gender" value="female"
      <?= $u['gender'] === 'female' ? 'checked' : '' ?>> Female</label>
    <label><input type="radio" name="gender" value="other"
      <?= $u['gender'] === 'other' ? 'checked' : '' ?>> Other</label>
  </fieldset>

  <button type="submit">Save</button>
</form>

<?php
require 'db.php';

$id   = filter_input(INPUT_POST, 'id', FILTER_VALIDATE_INT);
$name = trim($_POST['name'] ?? '');
$bio  = trim($_POST['bio'] ?? '');

// an unchecked checkbox is not submitted — use isset() to detect it
$is_active = isset($_POST['is_active']) ? 1 : 0;
$gender    = $_POST['gender'] ?? null;

if (!$id || $name === '') { http_response_code(400); exit('Invalid input'); }

$sql = "UPDATE users
        SET name = :name,
            bio = :bio,
            is_active = :is_active,
            gender = :gender
        WHERE id = :id";

$stmt = getPDO()->prepare($sql);
$stmt->execute([
    'name'      => $name,
    'bio'       => $bio,
    'is_active' => $is_active,
    'gender'    => $gender,
    'id'        => $id,
]);

header('Location: edit.php?id=' . $id);
exit;

<?php
// DB stores JSON or comma-separated string — explode into an array after reading
$hobbies = explode(',', $u['hobbies'] ?? '');
$all = ['reading', 'gaming', 'coding', 'sports'];
foreach ($all as $h): ?>
  <label>
    <input type="checkbox" name="hobbies[]" value="<?= $h ?>"
      <?= in_array($h, $hobbies) ? 'checked' : '' ?>> <?= $h ?>
  </label>
<?php endforeach; ?>

// save.php receives the array, joins it back into a string for DB storage
$hobbies = implode(',', $_POST['hobbies'] ?? []);