PHP二次开发MySQL,海南云主机,免费云主机租用,win7云主机,重庆vps拨号

🗣 Wd提问: PHP二次开发MySQL

🤖 Ai回答:
PHP与MySQL二次开发指南

下面我将为您提供PHP与MySQL二次开发的全面指南，包括核心概念、常用操作和最佳实践。

一、数据库连接

1、使用PDO连接MySQL（推荐）

php
<?php
$host = 'localhost';
$dbname = 'your_database';
$username = 'your_username';
$password = 'your_password';

try {
$pdo = new PDO(mysql:host=$host;dbname=$dbname;charset=utf8, $username, $password);
$pdo-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo-setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
echo 数据库连接成功;
} catch (PDOException $e) {
die(连接失败: 、$e-getMessage());
}
?

2、使用mysqli连接MySQL

php
<?php
$host = 'localhost';
$username = 'your_username';
$password = 'your_password';
$database = 'your_database';

$mysqli = new mysqli($host, $username, $password, $database);

if ($mysqli-connect_error) {
die(连接失败: 、$mysqli-connect_error);
}

$mysqli-set_charset(utf8);
?

二、基本CRUD操作

1、创建数据（Create）

php
// 使用PDO
$sql = INSERT INTO users (name, email, created_at) VALUES (?, ?, NOW());
$stmt = $pdo-prepare($sql);
$stmt-execute(['John Doe', 'john@65.hk']);
$lastInsertId = $pdo-lastInsertId();

// 使用mysqli
$sql = INSERT INTO users (name, email, created_at) VALUES (?, ?, NOW());
$stmt = $mysqli-prepare($sql);
$stmt-bind_param(ss, $name, $email);
$name = John Doe;
$email = john@65.hk;
$stmt-execute();
$lastInsertId = $stmt-insert_id;

2、读取数据（Read）

php
// 使用PDO查询单条记录
$sql = SELECT * FROM users WHERE id = ?;
$stmt = $pdo-prepare($sql);
$stmt-execute([1]);
$user = $stmt-fetch();

// 使用PDO查询多条记录
$sql = SELECT * FROM users WHERE active = ?;
$stmt = $pdo-prepare($sql);
$stmt-execute([1]);
$users = $stmt-fetchAll();

// 使用mysqli查询
$sql = SELECT * FROM users WHERE active = 1;
$result = $mysqli-query($sql);
$users = [];
while ($row = $result-fetch_assoc()) {
$users[] = $row;
}

3、更新数据（Update）

php
// 使用PDO
$sql = UPDATE users SET name = ?, email = ? WHERE id = ?;
$stmt = $pdo-prepare($sql);
$stmt-execute(['Jane Doe', 'jane@65.hk', 1]);
$affectedRows = $stmt-rowCount();

// 使用mysqli
$sql = UPDATE users SET name = ?, email = ? WHERE id = ?;
$stmt = $mysqli-prepare($sql);
$stmt-bind_param(ssi, $name, $email, $id);
$name = Jane Doe;
$email = jane@65.hk;
$id = 1;
$stmt-execute();
$affectedRows = $stmt-affected_rows;

4、删除数据（Delete）

php
// 使用PDO
$sql = DELETE FROM users WHERE id = ?;
$stmt = $pdo-prepare($sql);
$stmt-execute([1]);
$affectedRows = $stmt-rowCount();

// 使用mysqli
$sql = DELETE FROM users WHERE id = ?;
$stmt = $mysqli-prepare($sql);
$stmt-bind_param(i, $id);
$id = 1;
$stmt-execute();
$affectedRows = $stmt-affected_rows;

三、高级查询技巧

1、分页查询

php
function getUsersWithPagination($page = 1, $perPage = 10) {
global $pdo;

$offset = ($page 1) * $perPage;
$sql = SELECT * FROM users WHERE active = 1 ORDER BY id DESC LIMIT :limit OFFSET :offset;
$stmt = $pdo-prepare($sql);
$stmt-bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt-bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt-execute();

return $stmt-fetchAll();
}

// 获取总记录数用于分页
function getTotalUsers() {
global $pdo;

$sql = SELECT COUNT(*) as total FROM users WHERE active = 1;
$stmt = $pdo-query($sql);
return $stmt-fetch()['total'];
}

2、事务处理

php
try {
$pdo-beginTransaction();

// 执行多个SQL操作
$sql1 = UPDATE account SET balance = balance ? WHERE id = ?;
$stmt1 = $pdo-prepare($sql1);
$stmt1-execute([100, 1]);

$sql2 = UPDATE account SET balance = balance + ? WHERE id = ?;
$stmt2 = $pdo-prepare($sql2);
$stmt2-execute([100, 2]);

$pdo-commit();
echo 事务执行成功;
} catch (Exception $e) {
$pdo-rollBack();
echo 事务执行失败: 、$e-getMessage();
}

四、安全最佳实践

1、预防SQL注入

php
// 错误的方式容易导致SQL注入
$sql = SELECT * FROM users WHERE id = 、$_GET['id']; // 危险!

// 正确的方式使用参数化查询
$sql = SELECT * FROM users WHERE id = ?;
$stmt = $pdo-prepare($sql);
$stmt-execute([$_GET['id']]);

2、输入验证和过滤

php
// 验证邮箱
$email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);
if (!$email) {
die(无效的邮箱地址);
}

// 过滤HTML标签
$name = filter_var($_POST['name'], FILTER_SANITIZE_STRING);
$bio = filter_var($_POST['bio'], FILTER_SANITIZE_SPECIAL_CHARS);

五、实用封装类示例

php
class Database {
private $pdo;
private $error;

public function __construct($host, $dbname, $username, $password) {
try {
$this-pdo = new PDO(
mysql:host=$host;dbname=$dbname;charset=utf8,
$username,
$password,
[
PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE = PDO::FETCH_ASSOC
]
);
} catch (PDOException $e) {
$this-error = $e-getMessage();
die(数据库连接失败: 、$this-error);
}
}

public function query($sql, $params = []) {
try {
$stmt = $this-pdo-prepare($sql);
$stmt-execute($params);
return $stmt;
} catch (PDOException $e) {
die(查询错误: 、$e-getMessage());
}
}

public function fetch($sql, $params = []) {
return $this-query($sql, $params)-fetch();
}

public function fetchAll($sql, $params = []) {
return $this-query($sql, $params)-fetchAll();
}

public function insert($table, $data) {
$columns = implode(, , array_keys($data));
$placeholders = implode(, , array_fill(0, count($data), ?));
$sql = INSERT INTO $table ($columns) VALUES ($placeholders);

$this-query($sql, array_values($data));
return $this-pdo-lastInsertId();
}

public function update($table, $data, $where, $whereParams) {
$setClause = [];
foreach (array_keys($data) as $column) {
$setClause[] = $column = ?;
}
$setClause = implode(, , $setClause);

$sql = UPDATE $table SET $setClause WHERE $where;
$params = array_merge(array_values($data), $whereParams);

Ai作答

📣 商家广告：