DB-PHP 寫入 MySQL（HTML 表單資料） (tc)

CREATE DATABASE IF NOT EXISTS demo
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

CREATE TABLE contacts (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(190) NOT NULL,
  message    TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

<!-- index.html: the form sends data to insert.php via POST -->
<form action="insert.php" method="post">
  <label>Name: <input type="text" name="name" required></label><br>
  <label>Email: <input type="email" name="email" required></label><br>
  <label>Message: <textarea name="message" required></textarea></label><br>
  <button type="submit">Submit</button>
</form>

<?php
// insert.php — write form data into MySQL (mysqli, prepared statement)
$conn = mysqli_connect('localhost', 'app_user', 'password', 'demo');
if (!$conn) {
    die('Connection failed: ' . mysqli_connect_error());
}
mysqli_set_charset($conn, 'utf8mb4');   // support Chinese, avoid mojibake

// 1. Read and validate form data
$name    = trim($_POST['name'] ?? '');
$email   = trim($_POST['email'] ?? '');
$message = trim($_POST['message'] ?? '');

if ($name === '' || $message === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
    exit('Invalid input');             // stop if any required field is missing
}

// 2. Prepare an INSERT statement with ? placeholders
$stmt = mysqli_prepare($conn, 'INSERT INTO contacts (name, email, message) VALUES (?, ?, ?)');

// 3. Bind variables: "sss" = three strings, in order
mysqli_stmt_bind_param($stmt, 'sss', $name, $email, $message);

// 4. Execute and check the result
if (mysqli_stmt_execute($stmt)) {
    $new_id = mysqli_insert_id($conn);   // id of the newly inserted row
    echo "Saved successfully. New record id: $new_id";
} else {
    echo 'Error: ' . mysqli_stmt_error($stmt);
}

mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

<?php
// insert.php — write form data into MySQL (PDO, recommended)
$dsn = 'mysql:host=localhost;dbname=demo;charset=utf8mb4';
$pdo = new PDO($dsn, 'app_user', 'password', [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,  // throw exceptions on error
    PDO::ATTR_EMULATE_PREPARES   => false,                  // use native prepared statements
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

// Read and validate form data
$name    = trim($_POST['name'] ?? '');
$email   = trim($_POST['email'] ?? '');
$message = trim($_POST['message'] ?? '');

if ($name === '' || $message === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
    exit('Invalid input');
}

// Prepared statement with named placeholders
$sql  = 'INSERT INTO contacts (name, email, message) VALUES (:name, :email, :message)';
$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':name'    => $name,
    ':email'   => $email,
    ':message' => $message,
]);

$new_id = (int) $pdo->lastInsertId();   // id of the newly inserted row
echo "Saved successfully. New record id: $new_id";
?>