MySQL查询函数：10个PHP数据库操作函数使用示例

MySQL是一种广泛使用的关系型数据库管理系统。在PHP中，我们可以使用一些内置函数来执行MySQL查询操作。下面是10个常用的PHP数据库操作函数及其使用示例。

1. mysqli_connect() - 建立数据库连接

   <?php
   $conn = mysqli_connect("localhost", "root", "password", "database");
   if (!$conn) {
       die("连接失败：" . mysqli_connect_error());
   }
   ?>
   

2. mysqli_query() - 执行查询语句

   <?php
   $sql = "SELECT * FROM users";
   $result = mysqli_query($conn, $sql);
   if (mysqli_num_rows($result) > 0) {
       while ($row = mysqli_fetch_assoc($result)) {
           echo "Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
       }
   } else {
       echo "0 结果";
   }
   mysqli_free_result($result);
   ?>
   

3. mysqli_num_rows() - 获取查询结果的行数

   <?php
   $sql = "SELECT * FROM users";
   $result = mysqli_query($conn, $sql);
   $num_rows = mysqli_num_rows($result);
   echo "查询结果的行数为: " . $num_rows;
   ?>
   

4. mysqli_fetch_assoc() - 获取查询结果的关联数组

   <?php
   $sql = "SELECT * FROM users";
   $result = mysqli_query($conn, $sql);
   if (mysqli_num_rows($result) > 0) {
       while ($row = mysqli_fetch_assoc($result)) {
           echo "Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
       }
   } else {
       echo "0 结果";
   }
   mysqli_free_result($result);
   ?>
   

5. mysqli_fetch_row() - 获取查询结果的索引数组

   <?php
   $sql = "SELECT * FROM users";
   $result = mysqli_query($conn, $sql);
   if (mysqli_num_rows($result) > 0) {
       while ($row = mysqli_fetch_row($result)) {
           echo "Name: " . $row[0]. " - Email: " . $row[1]. "<br>";
       }
   } else {
       echo "0 结果";
   }
   mysqli_free_result($result);
   ?>
   

6. mysqli_fetch_array() - 获取查询结果的关联数组和索引数组的混合数组

   <?php
   $sql = "SELECT * FROM users";
   $result = mysqli_query($conn, $sql);
   if (mysqli_num_rows($result) > 0) {
       while ($row = mysqli_fetch_array($result)) {
           echo "Name: " . $row["name"]. " - Email: " . $row[1]. "<br>";
       }
   } else {
       echo "0 结果";
   }
   mysqli_free_result($result);
   ?>
   

7. mysqli_insert_id() - 获取最后插入的记录的自增ID

   <?php
   $sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
   if (mysqli_query($conn, $sql)) {
       $last_id = mysqli_insert_id($conn);
       echo "新记录的 ID 是: " . $last_id;
   } else {
       echo "Error: " . $sql . "<br>" . mysqli_error($conn);
   }
   ?>
   

8. mysqli_affected_rows() - 获取上一个查询的受影响行数

   <?php
   $sql = "DELETE FROM users WHERE id=1";
   if (mysqli_query($conn, $sql)) {
       $affected_rows = mysqli_affected_rows($conn);
       echo "受影响的行数为: " . $affected_rows;
   } else {
       echo "Error: " . $sql . "<br>" . mysqli_error($conn);
   }
   ?>
   

9. mysqli_real_escape_string() - 转义特殊字符以防止SQL注入

   <?php
   $name = mysqli_real_escape_string($conn, $_POST['name']);
   $email = mysqli_real_escape_string($conn, $_POST['email']);
   $sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
   if (mysqli_query($conn, $sql)) {
       echo "记录插入成功";
   } else {
       echo "Error: " . $sql . "<br>" . mysqli_error($conn);
   }
   ?>
   

10. mysqli_close() - 关闭数据库连接

    <?php
    mysqli_close($conn);
    ?>
    

以上是常用的10个PHP数据库操作函数及其使用示例。通过使用这些函数，我们可以方便地进行数据库查询操作，并对结果进行处理和安全过滤，确保数据的准确性和安全性。