php实现查询功能实现
实现基础查询功能
使用PHP和MySQL实现基础的数据库查询功能需要连接数据库并执行SQL语句。以下是一个简单的示例：
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检查连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// 输出数据
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 结果";
}
$conn->close();
?>实现带条件的查询
添加WHERE子句实现条件查询：
$search_term = $_POST['search_term']; // 从表单获取搜索词
$sql = "SELECT * FROM products WHERE name LIKE '%".$search_term."%'";
$result = $conn->query($sql);防止SQL注入
使用预处理语句提高安全性：
$stmt = $conn->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
$email = "john@example.com";
$stmt->execute();
$result = $stmt->get_result();实现分页查询
添加LIMIT和OFFSET实现分页：
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$per_page = 10;
$offset = ($page - 1) * $per_page;
$sql = "SELECT * FROM articles LIMIT $per_page OFFSET $offset";实现多表联合查询
使用JOIN实现多表查询：
$sql = "SELECT users.name, orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id";
$result = $conn->query($sql);实现排序功能
添加ORDER BY实现排序：
$sort = isset($_GET['sort']) ? $_GET['sort'] : 'id';
$order = isset($_GET['order']) ? $_GET['order'] : 'ASC';
$sql = "SELECT * FROM products ORDER BY $sort $order";使用PDO实现查询
PDO方式更现代且支持多种数据库：
try {
$pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->prepare("SELECT * FROM customers WHERE country = :country");
$stmt->bindParam(':country', $country);
$country = 'USA';
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $e) {
echo "错误: " . $e->getMessage();
}实现AJAX查询
结合JavaScript实现无刷新查询：
// search.php
$term = $_GET['term'];
$stmt = $conn->prepare("SELECT * FROM products WHERE name LIKE ?");
$search_term = "%$term%";
$stmt->bind_param("s", $search_term);
$stmt->execute();
$result = $stmt->get_result();
$data = array();
while($row = $result->fetch_assoc()) {
$data[] = $row;
}
echo json_encode($data);// 前端JavaScript
fetch(`search.php?term=${input.value}`)
.then(response => response.json())
.then(data => console.log(data));实现全文搜索
对于更复杂的搜索需求，可以使用MySQL的全文索引：
$sql = "SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('$search_term' IN NATURAL LANGUAGE MODE)";每个实现方法都应根据具体需求进行调整，特别是安全方面要考虑参数绑定和输入验证。
