How to use PDO for SELECT query? (code example)

There are several ways to use PDO to run SELECT queries. Their differences mainly lie in the existence of parameters, parameter types and result types. I will show examples for each case so that I can choose the most suitable one.

SELECT query without parameters

If there are no variables to be used in the query, we can use the traditional query ()method.

// 选择所有用户
$stmt = $pdo->query("SELECT * FROM users");

This will give us a $stmt object which can be used to get the actual rows.

Get a row

If a query only returns one row, then you can call the fetch() method of the $stmt variable:

//获取最后一个注册用户
$stmt = $pdo->query("SELECT * FROM users ORDER BY id DESC LIMIT 1");
$user = $stmt->fetch();

Note that in In PHP, you can call the method of the returned object, such as:

$user = $pdo->query("SELECT * FROM users ORDER BY id DESC LIMIT 1")->fetch();

Query multiple rows

There are two ways to get the multiple rows returned by the query. The most traditional method is to use the fetch() method in a while loop:

$stmt = $pdo->query("SELECT * FROM users");
while ($row = $stmt->fetch()) {
    echo $row[&#39;name&#39;]."<br />\n";
}

This method can be recommended if the rows must be processed one by one. For example, if such processing is the only action that needs to be taken, or the data needs to be preprocessed in some way before use.

But the best way to get multiple rows of data that will be displayed on the web page is to call the method called fetchAll(). It will put all the rows returned by the query into a PHP array, which can later be used to output the data using a template (this is considered much better than echoing the data directly during the fetch).

The code is as follows:

$data = $pdo->query("SELECT * FROM users")->fetchAll();
foreach ($data as $row) {
    echo $row[&#39;name&#39;]."<br />\n";
}

SELECT query with parameters

But most of the time, we have to use one or two variables in the query, in In this case, we should use a prepared statement (also called a parameterized query) to first prepare a query with parameters (or placeholder markers) and then execute it, sending the variables respectively.

In PDO, we can use positional placeholders and named placeholders at the same time. For simple queries, personally I prefer positional placeholders, I find them less verbose, but it's entirely a matter of personal preference.

SELECT query using positional placeholders

//通过id选择特定的用户
$stmt = $pdo->prepare("SELECT * FROM users WHERE id=?");
$stmt->execute([$id]); 
$user = $stmt->fetch();

SELECT query using named placeholders

// 通过id选择特定的用户
$stmt = $pdo->prepare("SELECT * FROM users WHERE id=:id");
$stmt->execute(['id' => $id]); 
$user = $stmt->fetch();

This article This article is an introduction to using PDO for SELECT query. I hope it will be helpful to friends in need!

The above is the detailed content of How to use PDO for SELECT query? (code example). For more information, please follow other related articles on the PHP Chinese website!