How to execute query in php

In PHP, query is a very frequently used operation to obtain or check the required data from the database. This is an important foundation because it provides us with access to the content in the database.

In this article, we will explore how to execute queries in PHP to make our web applications more powerful.

1. Connect to the database

Before starting the query, we need to establish a database connection. You can use PDO (PHP Data Objects) or mysqli extension.

Using PDO:

$host = 'localhost';
$dbname = 'mydatabase';
$user = 'myusername';
$pass = 'mypassword';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
    //设置错误模式为异常处理
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully!";
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

Using mysqli:

$host = 'localhost';
$dbname = 'mydatabase';
$user = 'myusername';
$pass = 'mypassword';

// 创建连接
$conn = new mysqli($host, $user, $pass, $dbname);

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

2. Execute the query

Once we have established the database connection, we can execute the query. Here is an example of how to perform a SELECT query using PDO and mysqli:

Using PDO:

$pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

$sql = "SELECT * FROM mytable";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 输出结果
print_r($result);

Using mysqli:

$conn = new mysqli($host, $user, $pass, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM mytable";
$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 results";
}

$conn->close();

3. Bind parameters

When executing a query, we usually want the query to return only results that meet certain criteria. In this case, we need to use bind parameters to separate the query conditions from the query statement.

The following is an example of binding parameters using PDO and mysqli:

Using PDO:

$pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

$sql = "SELECT * FROM mytable WHERE name = :name";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':name', $name);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 输出结果
print_r($result);

Using mysqli:

$conn = new mysqli($host, $user, $pass, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$name = "John Doe";

$sql = "SELECT * FROM mytable WHERE name = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $name);
$stmt->execute();
$result = $stmt->get_result();

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

$conn->close();

4. Precompiled query

Precompiled queries are a way to optimize database queries, which allow us to use placeholders in queries to avoid SQL injection attacks.

The following is an example of using PDO and mysqli precompiled query:

Using PDO:

$pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

$sql = "SELECT * FROM mytable WHERE name = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$name]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 输出结果
print_r($result);

Using mysqli:

$conn = new mysqli($host, $user, $pass, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$name = "John Doe";

$sql = "SELECT * FROM mytable WHERE name = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $name);
$stmt->execute();
$result = $stmt->get_result();

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

$conn->close();

5. Result set processing

Once we have executed the query, we need to process the result set, which means formatting them into an appropriate format for use in a web application, such as an array or a JSON string.

The following is an example of processing a result set:

Using PDO:

$pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

$sql = "SELECT * FROM mytable";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 格式化为JSON字符串
echo json_encode($result);

Using mysqli:

$conn = new mysqli($host, $user, $pass, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM mytable";
$result = $conn->query($sql);

// 格式化为数组
$data = array();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
}

// 格式化为JSON字符串
echo json_encode($data);

$conn->close();

Summary

In PHP Executing queries is an important basic and is frequently used in web applications. We can use PDO or mysqli extension to establish a database connection and use SELECT query to retrieve the data we need. When query conditions require bound parameters, we need to use bound parameters and precompiled queries to ensure query security. Finally, we need to process the result sets and format them into the appropriate web application data format.

The above is the detailed content of How to execute query in php. For more information, please follow other related articles on the PHP Chinese website!