Appearance
数据库增删改查模板
连接数据库
php
<?php
// 数据库连接参数
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检查连接
if (!$conn) {
die("连接失败: " . mysqli_connect_error());
}
echo "连接成功";
?>查询数据（SELECT）
基本查询
php
<?php
// 基本查询
$sql = "SELECT id, name, age FROM users";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// 输出数据
while ($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"] . " - Name: " . $row["name"] . " - Age: " . $row["age"] . "<br>";
}
} else {
echo "0 结果";
}
?>带条件查询
php
<?php
// 带条件查询
$name = "John";
$sql = "SELECT id, name, age FROM users WHERE name = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "s", $name);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
if (mysqli_num_rows($result) > 0) {
// 输出数据
while ($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"] . " - Name: " . $row["name"] . " - Age: " . $row["age"] . "<br>";
}
} else {
echo "0 结果";
}
?>排序查询
php
<?php
// 排序查询
$sql = "SELECT id, name, age FROM users ORDER BY age DESC";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// 输出数据
while ($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"] . " - Name: " . $row["name"] . " - Age: " . $row["age"] . "<br>";
}
} else {
echo "0 结果";
}
?>分页查询
php
<?php
// 分页查询
$page = 1;
$limit = 10;
$offset = ($page - 1) * $limit;
$sql = "SELECT id, name, age FROM users LIMIT ? OFFSET ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "ii", $limit, $offset);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
if (mysqli_num_rows($result) > 0) {
// 输出数据
while ($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"] . " - Name: " . $row["name"] . " - Age: " . $row["age"] . "<br>";
}
} else {
echo "0 结果";
}
?>插入数据（INSERT）
基本插入
php
<?php
// 基本插入
$name = "John";
$age = 30;
$sql = "INSERT INTO users (name, age) VALUES (?, ?)";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "si", $name, $age);
if (mysqli_stmt_execute($stmt)) {
echo "新记录插入成功";
} else {
echo "错误: " . $sql . "<br>" . mysqli_error($conn);
}
?>批量插入
php
<?php
// 批量插入
$users = array(
array("John", 30),
array("Jane", 25),
array("Bob", 35)
);
$sql = "INSERT INTO users (name, age) VALUES (?, ?)";
$stmt = mysqli_prepare($conn, $sql);
foreach ($users as $user) {
mysqli_stmt_bind_param($stmt, "si", $user[0], $user[1]);
mysqli_stmt_execute($stmt);
}
echo "批量插入成功";
?>更新数据（UPDATE）
基本更新
php
<?php
// 基本更新
$id = 1;
$name = "John Doe";
$age = 31;
$sql = "UPDATE users SET name = ?, age = ? WHERE id = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "sii", $name, $age, $id);
if (mysqli_stmt_execute($stmt)) {
echo "记录更新成功";
} else {
echo "错误: " . $sql . "<br>" . mysqli_error($conn);
}
?>条件更新
php
<?php
// 条件更新
$age = 30;
$name = "John";
$sql = "UPDATE users SET age = ? WHERE name = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "is", $age, $name);
if (mysqli_stmt_execute($stmt)) {
echo "记录更新成功";
} else {
echo "错误: " . $sql . "<br>" . mysqli_error($conn);
}
?>删除数据（DELETE）
基本删除
php
<?php
// 基本删除
$id = 1;
$sql = "DELETE FROM users WHERE id = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "i", $id);
if (mysqli_stmt_execute($stmt)) {
echo "记录删除成功";
} else {
echo "错误: " . $sql . "<br>" . mysqli_error($conn);
}
?>条件删除
php
<?php
// 条件删除
$age = 30;
$sql = "DELETE FROM users WHERE age = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "i", $age);
if (mysqli_stmt_execute($stmt)) {
echo "记录删除成功";
} else {
echo "错误: " . $sql . "<br>" . mysqli_error($conn);
}
?>事务处理
php
<?php
// 事务处理
try {
mysqli_begin_transaction($conn);
// 插入数据
$sql1 = "INSERT INTO users (name, age) VALUES (?, ?)";
$stmt1 = mysqli_prepare($conn, $sql1);
mysqli_stmt_bind_param($stmt1, "si", "John", 30);
mysqli_stmt_execute($stmt1);
// 获取插入的 ID
$user_id = mysqli_insert_id($conn);
// 插入关联数据
$sql2 = "INSERT INTO orders (user_id, product) VALUES (?, ?)";
$stmt2 = mysqli_prepare($conn, $sql2);
mysqli_stmt_bind_param($stmt2, "is", $user_id, "Product 1");
mysqli_stmt_execute($stmt2);
// 提交事务
mysqli_commit($conn);
echo "事务执行成功";
} catch (Exception $e) {
// 回滚事务
mysqli_rollback($conn);
echo "事务执行失败: " . $e->getMessage();
}
?>关闭连接
php
<?php
// 关闭连接
mysqli_close($conn);
?>总结
本文提供了 PHP 中数据库增删改查的基本模板，包括连接数据库、查询数据、插入数据、更新数据、删除数据和事务处理等操作。这些模板可以作为开发中的参考，帮助你快速实现数据库操作。
在使用这些模板时，应该注意以下几点：
- 使用预处理语句防止 SQL 注入
- 正确处理错误和异常
- 及时关闭数据库连接
- 遵循最佳实践和编码规范
通过使用这些模板，你可以更高效地开发 PHP 应用程序，提高代码质量和安全性。