整理 PHP 連接 MySQL 並執行查詢、新增、修改、刪除的標準流程,採用 mysqli 物件導向風格,含預處理語句防止 SQL 注入。
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '123456';
$dbname = 'products';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
die('資料庫連線失敗:' . $mysqli->connect_error);
}
$mysqli->set_charset('utf8mb4');
?>
<?php
$sql = "SELECT * FROM `category`";
$result = $mysqli->query($sql);
while ($row = $result->fetch_assoc()) {
echo $row['id'] . ' ' . $row['name'] . '
';
}
$result->free();
?>
<?php
$name = '新分類';
$stmt = $mysqli->prepare("INSERT INTO `category` (`name`) VALUES (?)");
$stmt->bind_param('s', $name);
$stmt->execute();
if ($stmt->affected_rows) {
echo '新增成功,ID:' . $stmt->insert_id;
} else {
echo '新增失敗:' . $stmt->error;
}
$stmt->close();
?>
<?php
$id = 1; $name = '更新名稱';
$stmt = $mysqli->prepare("UPDATE `category` SET `name`=? WHERE `id`=?");
$stmt->bind_param('si', $name, $id);
$stmt->execute();
echo $stmt->affected_rows ? '修改成功' : '修改失敗:' . $stmt->error;
$stmt->close();
?>
<?php
$id = 1;
$stmt = $mysqli->prepare("DELETE FROM `category` WHERE `id`=?");
$stmt->bind_param('i', $id);
$stmt->execute();
echo $stmt->affected_rows ? '刪除成功' : '刪除失敗:' . $stmt->error;
$stmt->close();
?>
$mysqli->close();
prepare/bind_param/execute),防 SQL 注入。affected_rows、error、errno。begin_transaction/commit/rollback)。mysqli::ping() 檢測連線存活。