PHP实现条件查询
PHP实现条件查询的方法
使用MySQLi预处理语句
$conn = new mysqli("localhost", "username", "password", "database");
$sql = "SELECT * FROM products WHERE price > ? AND category = ?";
$stmt = $conn->prepare($sql);
$minPrice = 100;
$category = "electronics";
$stmt->bind_param("is", $minPrice, $category);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc()) {
print_r($row);
}
使用PDO预处理语句
$pdo = new PDO("mysql:host=localhost;dbname=database", "username", "password");
$minPrice = 100;
$category = "electronics";
$stmt = $pdo->prepare("SELECT * FROM products WHERE price > :price AND category = :category");
$stmt->execute([':price' => $minPrice, ':category' => $category]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($results);
动态构建查询条件
$conditions = [];
$params = [];
if(isset($_GET['price_min'])) {
$conditions[] = "price >= ?";
$params[] = $_GET['price_min'];
}
if(isset($_GET['category'])) {
$conditions[] = "category = ?";
$params[] = $_GET['category'];
}
$sql = "SELECT * FROM products";
if(!empty($conditions)) {
$sql .= " WHERE " . implode(" AND ", $conditions);
}
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
使用LIKE进行模糊查询
$searchTerm = "%" . $_GET['search'] . "%";
$stmt = $pdo->prepare("SELECT * FROM products WHERE name LIKE ?");
$stmt->execute([$searchTerm]);
多表联合条件查询
$sql = "SELECT p.*, c.name as category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.stock > 0 AND c.status = 'active'";
注意事项
- 始终使用预处理语句防止SQL注入
- 对用户输入进行验证和过滤
- 考虑添加LIMIT子句限制返回结果数量
- 复杂的查询可以建立适当的索引提高性能
