[PHP] SELECT query with PDO

There are several ways to run a SELECT query using PDO, that differ mainly by the presence of parameters, type of parameters, and the result type. I will show examples for the every case so you can choose one that suits you best.

Just make sure you've got a properly configured PDO connection variable that needs in order to run SQL queries with PDO and to inform you of the possible errors.


I - SELECT query without parameters
If there are no variables going to be used in the query, we can use a conventional query() method instead of prepare and execute.
Mã:// select all users
$stmt = $pdo->query("SELECT * FROM users"); 
This will give us an $stmt object that can be used to fetch the actual rows.

1. Getting a single row
If a query is supposed to return just a single row, then you can just call fetch() method of the $stmt variable:
Mã:// getting the last registered user
$stmt = $pdo->query("SELECT * FROM users ORDER BY id DESC LIMIT 1");
$user = $stmt->fetch(); 
Note that in PHP you can "chain" method calls, calling a method of the returned object already, like:
Mã:$user = $pdo->query("SELECT * FROM users ORDER BY id DESC LIMIT 1")->fetch(); 

2. Selecting multiple rows
There are two ways to fetch multiple rows returned by a query. The most traditional way is to use the fetch() method within a while loop:
Mã:$stmt = $pdo->query("SELECT * FROM users");
while ($row = $stmt->fetch()) {
    echo $row['name']."<br />\n";
} 
This method could be recommended if rows have to be processed one by one. For example, if such processing is the only action that needs to be taken, or if the data needs to be pre-processed somehow before use.

But the most preferred way to fetch multiple rows which would to be shown on a web-page is calling the great helper method called fetchAll(). It will put all the rows returned by a query into a PHP array, that later can be used to output the data using a template (which is considered much better than echoing the data right during the fetch process). So the code would be
Mã:$data = $pdo->query("SELECT * FROM users")->fetchAll();
// and somewhere later:
foreach ($data as $row) {
    echo $row['name']."<br />\n";
} 


II - SELECT query with parameters
But most of time we have to use a variable or two in the query, and in such a case we should use a prepared statement (also called a parameterized query), first preparing a query with parameters (or placeholder marks) and then executing it, sending variables separately.

In PDO we can use both positional and named placeholders. For simple queries, personally I prefer positional placeholders, I find them less verbose, but it's entirely a matter of taste.

1. SELECT query with positional placeholders
Mã:// select a particular user by id
$stmt = $pdo->prepare("SELECT * FROM users WHERE id=?");
$stmt->execute([$id]); 
$user = $stmt->fetch(); 

2. SELECT query with named placeholders
Mã:// select a particular user by id
$stmt = $pdo->prepare("SELECT * FROM users WHERE id=:id");
$stmt->execute(['id' => $id]); 
$user = $stmt->fetch(); 

3. Selecting multiple rows
Fetching multiple rows from a prepared query would be identical to that from a query without parameters already shown:
Mã:$stmt = $pdo->prepare("SELECT * FROM users LIMIT ?, ?");
$stmt->execute([$limit, $offset]); 
while ($row = $stmt->fetch()) {
    echo $row['name']."<br />\n";
} 
or:
Mã:$stmt = $pdo->prepare("SELECT * FROM users LIMIT :limit, :offset");
$stmt->execute(['limit' => $limit, 'offset' => $offset]); 
$data = $stmt->fetchAll();
// and somewhere later:
foreach ($data as $row) {
    echo $row['name']."<br />\n";
}