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.
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.
// 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.
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:
// 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:
$user = $pdo->query("SELECT * FROM users ORDER BY id DESC LIMIT 1")->fetch();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:
$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
$data = $pdo->query("SELECT * FROM users")->fetchAll();
// and somewhere later:
foreach ($data as $row) {
echo $row['name']."<br />\n";
}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.
SELECT query with positional placeholders
// select a particular user by id
$stmt = $pdo->prepare("SELECT * FROM users WHERE id=?");
$stmt->execute([$id]);
$user = $stmt->fetch();SELECT query with named placeholders
// select a particular user by id
$stmt = $pdo->prepare("SELECT * FROM users WHERE id=:id");
$stmt->execute(['id' => $id]);
$user = $stmt->fetch();Selecting multiple rows
Fetching multiple rows from a prepared query would be identical to that from a query without parameters already shown:
$stmt = $pdo->prepare("SELECT * FROM users LIMIT ?, ?");
$stmt->execute([$limit, $offset]);
while ($row = $stmt->fetch()) {
echo $row['name']."<br />\n";
}or
$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";
}Related articles:
- INSERT query using PDO
- UPDATE query using PDO
- How to connect to MySQL using PDO
- PDO Examples
- Authenticating a user using PDO and password_verify()
- How to check if email exists in the database?
- Select the number of rows using PDO
- How to create a WHERE clause for PDO dynamically
- DELETE query using PDO
- How to create a prepared statement for UPDATE query
- Getting a nested array when multiple rows are linked to a single entry
- How to execute 1000s INSERT/UPDATE queries with PDO?
- Adding a field name in the ORDER BY clause based on the user's choice
- INSERT helper function for PDO Mysql
- PDO Examples
- PDO Examples
Got a question?
I am the only person to hold a gold badge in
,
and
on Stack Overflow and I am eager to show the right way for PHP developers.
Besides, your questions let me make my articles even better, so you are more than welcome to ask any question you have.
Add a comment
Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator's review.
Markdown is now supported:
>before and an empty line after for a quote