php创建表格后怎么查询

PHP创建表格后如何实现数据查询

在PHP开发中,创建数据库表格并实现数据查询是常见的操作，本文将详细介绍在PHP中创建表格后，如何编写代码来查询表格中的数据。

创建表格的基本步骤

在讨论查询之前,我们先简单回顾一下如何使用PHP创建表格：

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
// 创建表格SQL语句
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
if ($conn->query($sql) === TRUE) {
    echo "表格创建成功";
} else {
    echo "创建表格错误: " . $conn->error;
}
$conn->close();
?>

查询表格数据的常用方法

基本查询语句

创建表格后,最常用的查询方法是使用SELECT语句：

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
// 查询所有数据
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    // 输出每行数据
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 结果";
}
$conn->close();
?>

带条件的查询

使用WHERE子句可以添加查询条件：

<?php
$sql = "SELECT * FROM MyGuests WHERE lastname='Doe'";
$result = $conn->query($sql);
// 处理结果...
?>

排序查询结果

使用ORDER BY对结果进行排序：

<?php
// 按reg_date降序排列
$sql = "SELECT id, firstname, lastname FROM MyGuests ORDER BY reg_date DESC";
$result = $conn->query($sql);
// 处理结果...
?>

限制查询结果数量

使用LIMIT限制返回的行数：

<?php
// 只返回前5条记录
$sql = "SELECT * FROM MyGuests LIMIT 5";
$result = $conn->query($sql);
// 处理结果...
?>

使用预处理语句进行安全查询

为了防止SQL注入,建议使用预处理语句：

<?php
$stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests WHERE lastname = ?");
$stmt->bind_param("s", $lastname);
$lastname = "Doe";
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 结果";
}
$stmt->close();
$conn->close();
?>

查询结果的多种处理方式

使用fetch_assoc()

while($row = $result->fetch_assoc()) {
    // $row是关联数组
}

使用fetch_row()

while($row = $result->fetch_row()) {
    // $row是索引数组
}

使用fetch_object()

while($row = $result->fetch_object()) {
    // $row是对象
    echo $row->firstname;
}

完整示例：创建表格并查询数据

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
// 查询数据
$sql = "SELECT id, firstname, lastname, email FROM MyGuests";
$result = $conn->query($sql);
// 输出数据
if ($result->num_rows > 0) {
    echo "<table border='1'>";
    echo "<tr><th>ID</th><th>First Name</th><th>Last Name</th><th>Email</th></tr>";
    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . $row["id"] . "</td>";
        echo "<td>" . $row["firstname"] . "</td>";
        echo "<td>" . $row["lastname"] . "</td>";
        echo "<td>" . $row["email"] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "0 结果";
}
$conn->close();
?>

在PHP中创建表格后查询数据的基本步骤包括：