PHP MySQL CRUD 完整實作範例：連線、新增、刪除、修改、查詢

資料庫連線通用檔：conn.php

<?php
// 連線 MySQL
$conn = @mysql_connect("localhost:3306", "root", "root") or die("MySQL 連線失敗");
// 選擇資料庫、設定字元集
@mysql_select_db("php_blog", $conn) or die("資料庫選擇失敗: " . mysql_error());
mysql_query('SET NAMES UTF8') or die("字元集設定錯誤");
?>

注意：mysql_* 函數已棄用，新專案請使用 mysqli 或 PDO。

新增資料：add.php

<?php
include("conn.php");
if (!empty($_POST['sub'])) {
    $title = $_POST['title'];
    $con   = $_POST['con'];
    $sql = "INSERT INTO news (id, title, dates, contents) VALUES (NULL, '$title', NOW(), '$con')";
    mysql_query($sql);
    echo "新增成功";
}
?>
<form action="add.php" method="post">
標題: <input type="text" name="title"><br>
內容: <textarea rows="5" cols="50" name="con"></textarea><br>
<input type="submit" name="sub" value="發表">
</form>

刪除資料：del.php

<?php
include("conn.php");
if (!empty($_GET['del'])) {
    $d = $_GET['del'];
    $sql = "DELETE FROM news WHERE id = '$d'";
    mysql_query($sql);
    echo "刪除成功";
}
?>

修改資料：edit.php

<?php
include("conn.php");
// 取得原資料
if (!empty($_GET['id'])) {
    $sql = "SELECT * FROM news WHERE id = '" . $_GET['id'] . "'";
    $query = mysql_query($sql);
    $rs = mysql_fetch_array($query);
}
// 處理更新
if (!empty($_POST['sub'])) {
    $title = $_POST['title'];
    $con   = $_POST['con'];
    $hid   = $_POST['hid'];
    $sql = "UPDATE news SET title='$title', contents='$con' WHERE id='$hid' LIMIT 1";
    mysql_query($sql);
    echo "<script> alert('更新成功'); location.href='index.php'</script>";
}
?>
<form action="edit.php" method="post">
<input type="hidden" name="hid" value="<?php echo $rs['id'] ?>">
標題: <input type="text" name="title" value="<?php echo $rs['title'] ?>"><br>
內容: <textarea rows="5" cols="50" name="con"><?php echo $rs['contents'] ?></textarea><br>
<input type="submit" name="sub" value="發表">
</form>

查詢列表：index.php

<a href="add.php">新增內容</a>
<hr>
<form>
<input type="text" name="keys" />
<input type="submit" name="subs" value="搜尋"/>
</form>
<?php
include("conn.php");
if (!empty($_GET['keys'])) {
    $w = "title LIKE '%" . $_GET['keys'] . "%'";
} else {
    $w = "1";
}
$sql = "SELECT * FROM news WHERE $w ORDER BY id DESC";
$query = mysql_query($sql);
while ($rs = mysql_fetch_array($query)) {
?>
<h2>標題: <a href="view.php?id=<?php echo $rs['id'] ?>"><?php echo $rs['title'] ?></a> 
     <a href="edit.php?id=<?php echo $rs['id'] ?>">編輯</a> | 
     <a href="del.php?del=<?php echo $rs['id'] ?>">刪除</a></h2>
<li><?php echo $rs['dates'] ?></li>
<p><?php echo $rs['contents'] ?></p>
<hr>
<?php } ?>

安全性補充：防止 SQL Injection

<?php
// 使用 mysqli 預處理語句（推薦）
$stmt = $mysqli->prepare("INSERT INTO news (title, contents) VALUES (?, ?)");
$stmt->bind_param("ss", $title, $con);
$stmt->execute();

// 或使用 PDO
$stmt = $pdo->prepare("INSERT INTO news (title, contents) VALUES (:title, :con)");
$stmt->execute([':title' => $title, ':con' => $con]);
?>