php实现数据查询

使用PDO进行数据库查询

PHP中使用PDO（PHP Data Objects）扩展可以安全高效地执行数据库查询。PDO支持多种数据库系统，提供统一的接口。

创建PDO连接：

$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'username';
$password = 'password';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

执行简单查询：

$stmt = $pdo->query('SELECT * FROM users');
while ($row = $stmt->fetch()) {
    echo $row['username'] . "\n";
}

使用预处理语句防止SQL注入

预处理语句是防止SQL注入的最佳实践，PDO和MySQLi都支持预处理。

PDO预处理示例：

$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute(['id' => $userId]);
$user = $stmt->fetch();

MySQLi预处理示例：

$stmt = $mysqli->prepare('SELECT * FROM users WHERE id = ?');
$stmt->bind_param('i', $userId);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();

使用MySQLi进行查询

MySQLi是PHP专门针对MySQL数据库的扩展，提供面向对象和过程化两种接口。

面向对象方式：

$mysqli = new mysqli('localhost', 'username', 'password', 'testdb');

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

$result = $mysqli->query('SELECT * FROM users');
while ($row = $result->fetch_assoc()) {
    echo $row['username'] . "\n";
}

处理查询结果

获取单行数据：

$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([$userId]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

获取多行数据：

$stmt = $pdo->query('SELECT * FROM users');
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

获取单列值：

$stmt = $pdo->prepare('SELECT username FROM users WHERE id = ?');
$stmt->execute([$userId]);
$username = $stmt->fetchColumn();

构建复杂查询

使用命名参数：

$sql = 'SELECT * FROM users WHERE status = :status AND created_at > :date';
$stmt = $pdo->prepare($sql);
$stmt->execute([
    'status' => 'active',
    'date' => '2023-01-01'
]);

使用IN语句：

$placeholders = implode(',', array_fill(0, count($ids), '?'));
$sql = "SELECT * FROM users WHERE id IN ($placeholders)";
$stmt = $pdo->prepare($sql);
$stmt->execute($ids);

分页查询实现

实现基本分页功能：

$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$perPage = 10;
$offset = ($page - 1) * $perPage;

$stmt = $pdo->prepare('SELECT * FROM users LIMIT :limit OFFSET :offset');
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$users = $stmt->fetchAll();

使用ORM进行高级查询

Laravel的Eloquent ORM示例：

$users = User::where('active', 1)
             ->orderBy('name')
             ->take(10)
             ->get();

Doctrine ORM示例：

$query = $entityManager->createQuery(
    'SELECT u FROM User u WHERE u.active = 1 ORDER BY u.name ASC'
);
$users = $query->getResult();