揭秘PHP轻松掌控MySQL数据库的实战攻略

引言
PHP与MySQL的结合在Web开发中占据了举足轻重的地位。PHP作为服务器端脚本语言,提供了强大的动态网页开发能力,而MySQL作为关系型数据库管理系统,擅长存储和管理大量结构化数据。本文将深入探讨PHP如何轻松掌控MySQL数据库,通过实战攻略帮助读者掌握相关技能。
PHP与MySQL连接方法
1. MySQLi扩展
MySQLi是PHP的一个扩展,提供了面向对象和面向过程的两种方法来连接MySQL数据库。
1.1 面向对象
connect_error) {
die(“连接失败: ” . $conn->connect_error);
}
?>
1.2 面向过程

2. PDO扩展
PDO(PHP Data Objects)提供了一个数据访问抽象层,允许使用相同的函数来操作不同的数据库。
PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];

try {
$pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>
数据操作实战
1. 创建表
MySQLi
$sql = “CREATE TABLE IF NOT EXISTS myTable (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL
)”;

if ($conn->query($sql) === TRUE) {
echo “表创建成功”;
} else {
echo “Error: ” . $sql . “
” . $conn->error;
}
PDO
$sql = “CREATE TABLE IF NOT EXISTS myTable (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL
)”;

try {
$pdo->exec($sql);
echo “表创建成功”;
} catch (PDOException $e) {
echo “Error: ” . $e->getMessage();
}
2. 插入数据
MySQLi
$sql = “INSERT INTO myTable (firstname, lastname) VALUES (‘John’, ‘Doe’)”;

if ($conn->query($sql) === TRUE) {
echo “新记录插入成功”;
} else {
echo “Error: ” . $sql . “
” . $conn->error;
}
PDO
$sql = “INSERT INTO myTable (firstname, lastname) VALUES (:firstname, :lastname)”;
$stmt = $pdo->prepare($sql);

$stmt->execute([‘:firstname’ => ‘John’, ‘:lastname’ => ‘Doe’]);

echo “新记录插入成功”;
3. 查询数据
MySQLi
$sql = “SELECT id, firstname, lastname FROM myTable”;
$result = $conn->query($sql);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo “id: ” . $row[“id”]. ” – Name: ” . $row[“firstname”]. ” ” . $row[“lastname”]. “
“;
}
} else {
echo “0 结果”;
}
PDO
$sql = “SELECT id, firstname, lastname FROM myTable”;
$stmt = $pdo->query($sql);

while ($row = $stmt->fetch()) {
echo “id: ” . $row[“id”]. ” – Name: ” . $row[“firstname”]. ” ” . $row[“lastname”]. “
“;
}
4. 更新数据
MySQLi
$sql = “UPDATE myTable SET lastname = ‘Smith’ WHERE id = 1”;

if ($conn->query($sql) === TRUE) {
echo “记录更新成功”;
} else {
echo “Error: ” . $sql . “
” . $conn->error;
}
PDO
$sql = “UPDATE myTable SET lastname = :lastname WHERE id = :id”;
$stmt = $pdo->prepare($sql);

$stmt->execute([‘:lastname’ => ‘Smith’, ‘:id’ => 1]);

echo “记录更新成功”;
5. 删除数据
MySQLi
$sql = “DELETE FROM myTable WHERE id = 1”;

if ($conn->query($sql) === TRUE) {
echo “记录删除成功”;
} else {
echo “Error: ” . $sql . “
” . $conn->error;
}
PDO
$sql = “DELETE FROM myTable WHERE id = :id”;
$stmt = $pdo->prepare($sql);

$stmt->execute([‘:id’ => 1]);

echo “记录删除成功”;
总结
通过以上实战攻略,读者应该能够轻松掌控PHP与MySQL数据库的交互。无论是创建表、插入、查询、更新还是删除数据,PHP都提供了丰富的方法来实现这些操作。熟练掌握这些技能,将为您的Web开发之路提供坚实的后盾。