How to use pdo method to implement query in php

PHP is a widely used server-side scripting language. It has many frameworks and libraries for developers to use, and PDO (PHP Data Object) is an extension of PHP to connect to the database, making it more convenient for developers. It can operate different types of databases efficiently, and also provides a safer way to avoid security issues such as SQL injection. This article will focus on how to use PDO to implement database queries.

1. Introduction to PDO

PDO is a database extension introduced in PHP 5.1. It is a lightweight, cross-platform data access layer. It provides standardized access to various databases, as well as support for data preprocessing and parameterized queries, thereby reducing the risk of security vulnerabilities and making our code more robust.

2. Establish a database connection

Before using PDO for database query, we need to establish a connection with the database first. The connection requires the user name, password, host name, port number, database name and other parameters of the database. Finally, a PDO object is obtained, and we can use this object for subsequent operations.

//Connect to the database
$dsn = "mysql:host=localhost;dbname=test;charset=utf8";
$username = "root";
$password = " 123456";
try {

$pdo = new PDO($dsn, $username, $password);
echo "连接成功!";

} catch (PDOException $e) {

echo "连接失败：" . $e->getMessage();

}

In the above code, $dsn is the data source name, where Contains hostname, database name, and character set. $username and $password are the username and password required to connect to the database. If the connection is successful, "Connection successful!" will be output; if the connection fails, an error message will be output.

3. Basic query operations

Let’s introduce some basic PDO database query operations.

1. Query a single field

When we want to query a field in the database, we can use the following code:

//Query a single field
$sql = "SELECT count(*) FROM user WHERE status=1";
$res = $pdo->query($sql);
$row = $res->fetch(PDO::FETCH_NUM) ;
$count = $row[0];
echo "Number of users:" . $count;

In the above code, $sql is the SQL statement to be executed, $pdo-> ;query($sql) executes the statement and saves the result in $res. We can get the first row of data in the result set through $res->fetch(PDO::FETCH_NUM). PDO::FETCH_NUM means returning a numeric index. The returned array is accessed in numeric index mode, which facilitates us to obtain a single field. value.

2. Query multiple fields

When we need to query multiple fields, we can use the following code:

//Query multiple fields
$sql = "SELECT id, username, email FROM user WHERE status=1";
$res = $pdo->query($sql);
while ($row = $res->fetch(PDO:: FETCH_ASSOC)) {

echo "ID：" . $row['id'] . "，姓名：" . $row['username'] . "，邮箱：" . $row['email'] . "<br/>";

}

In the above code, $res->fetch(PDO::FETCH_ASSOC) will return an associative array, where the keys of the array represent the field names, and the array The value of is the value corresponding to this field.

3. Query multi-row data

When we need to query multi-row data, we can use the following code:

//Query multi-row data
$sql = "SELECT * FROM user WHERE status=1";
$res = $pdo->query($sql);
$list = $res->fetchAll(PDO::FETCH_ASSOC);
foreach ($list as $row) {

echo "ID：" . $row['id'] . "，姓名：" . $row['username'] . "，邮箱：" . $row['email'] . "<br/>";

}

In the above code, $res->fetchAll() returns all result set data. The fetchAll() method uses the PDO::FETCH_ASSOC parameter to convert the result set into an associative array array, and uses the foreach statement to traverse the array and output the data.

4. Use parameterized query

If we directly use the value entered by the user as part of the SQL statement in the query operation, it may cause security issues such as SQL injection. In order to avoid this Question, we can use parameterized queries. The following example shows how to use parameterized query:

//parameterized query
$username = "admin";
$password = "123456";
$sql = 'SELECT * FROM user WHERE username = ? AND password = ?';
$stmt = $pdo->prepare($sql);
$stmt->execute([$username, $password]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$user) {

echo "用户名或密码错误！";

} else {

echo "欢迎，" . $user['username'] . "！";

}

In the above code, we use the prepare() method to process the SQL statement, and then use the execute() method to pass the parameters into the query. The $stmt->fetch() method fetches data from the result set. This approach avoids SQL injection issues that arise from having multiple query parameters.

4. Summary

This article introduces how to use PDO to implement database queries. By learning basic operations such as connecting to the database, querying a single field, querying multiple fields, querying multiple rows of data, and using parameterized queries, we can operate the database more flexibly and effectively improve development efficiency. In actual development, we also need to consider other security issues, such as SQL injection issues, strengthening parameter verification, filtering, etc. More care needs to be taken during the development process to ensure the security of the application.

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