10个PHP数据库函数：优化SQL查询

PHP是世界上最流行的服务器端脚本语言之一，用于动态Web内容开发。PHP中的大多数在线应用程序都需要与数据库进行交互。在PHP中，有很多数据库函数可以与不同类型的数据库（如MySQL、Oracle、PostgreSQL等）进行交互。在本文中，我们将探讨10个PHP数据库函数，以优化SQL查询。

1. mysqli_connect

适用于MySQL，打开与MySQL服务器的新连接。此函数需要连接的主机名、用户名、密码和数据库名称。

示例代码：

$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "example";

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检测连接
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

2. mysqli_query

适用于MySQL，执行SQL查询并返回结果对象。

示例代码：

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  while($row = mysqli_fetch_assoc($result)) {
    echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
  }
} else {
  echo "0 results";
}

mysqli_close($conn);

3. mysqli_num_rows

适用于MySQL，返回结果集合中的行数。

示例代码：

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);
echo "Number of rows: " . mysqli_num_rows($result);

4. mysqli_fetch_array

适用于MySQL，从结果对象中获取一行作为关联数组和数字索引数组。

示例代码：

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

while($row = mysqli_fetch_array($result)) {
  echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
}

5. mysqli_fetch_assoc

适用于MySQL，从结果对象中获取一行作为关联数组。

示例代码：

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

while($row = mysqli_fetch_assoc($result)) {
  echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
}

6. mysqli_fetch_row

适用于MySQL，从结果对象中获取一行作为数字索引数组。

示例代码：

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

while($row = mysqli_fetch_row($result)) {
  echo "ID: " . $row[0]. " - Name: " . $row[1]. " - Email: " . $row[2]. "<br>";
}

7. mysqli_real_escape_string

适用于MySQL，将字符串转义为安全的SQL语句。

示例代码：

$name = mysqli_real_escape_string($conn, $name);
$email = mysqli_real_escape_string($conn, $email);

$sql = "SELECT * FROM users WHERE name='$name' AND email='$email'";
$result = mysqli_query($conn, $sql);

8. mysqli_fetch_object

适用于MySQL，从结果对象中获取一行作为对象。

示例代码：

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

while($obj = mysqli_fetch_object($result)) {
  echo "ID: " . $obj->id. " - Name: " . $obj->name. " - Email: " . $obj->email. "<br>";
}

9. mysqli_affected_rows

适用于MySQL，返回前一个操作所影响的行数。

示例代码：

$sql = "DELETE FROM users WHERE id=1";
mysqli_query($conn, $sql);

echo "Rows deleted: " . mysqli_affected_rows($conn);

10. mysqli_prepare

适用于MySQL，准备SQL语句，以便绑定变量到查询中。

示例代码：

$name = "John";
$email = "john@example.com";

$stmt = mysqli_prepare($conn, "SELECT * FROM users WHERE name=? AND email=?");
mysqli_stmt_bind_param($stmt, "ss", $name, $email);

mysqli_stmt_execute($stmt);