How to execute query in php-PHP Problem-php.cn

How to execute query in php

WBOY

Release： 2023-05-06 15:07:07

Original

627 people have browsed it

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();
}

Copy after login

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!";

Copy after login

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);

Copy after login

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();

Copy after login

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);

Copy after login

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();

Copy after login

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);

Copy after login

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();

Copy after login

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);

Copy after login

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();

Copy after login

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!