PDO: Selecting rows from MySQL.

In this tutorial, I will show you how to select rows from a MySQL database using PHP’s PDO object. Note that this article is aimed at beginners who are looking for a “cheat sheet” of sorts.

PS: If you’re a “noob”, then you will need to know how to connect to a MySQL database before using any of the code below. You can’t select data from a database if you’re not connected to it.

In my last tutorial, I showed you how to insert rows into a MySQL database using the PDO object. I also used a set up a pretty simple table structure in order to help illustrate my code examples:

CREATE TABLE IF NOT EXISTS `cars` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `make` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `model` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

There are three main ways to select rows from MySQL with the PDO object:

  • Use the PDO::query function – This is suitable for queries that do not require external data (i.e. data that has come from the client / user). You can use this when SQL injection is not a concern.
  • Use the PDOStatement::fetchAll function – This is suitable for SELECT queries that will return multiple rows.
  • Use the PDOStatement::fetch function – This is suitable for SELECT queries that will only return one row at a time.

PDO SELECT queries.

In scenarios where your SELECT queries are not susceptible to SQL injections, you can use the PDO query function like so:

//Selecting multiple rows from a MySQL database using the PDO::query function.
$sql = "SELECT `id`, `make`, `model` FROM `cars` ORDER BY make ASC";

foreach($pdo->query($sql, PDO::FETCH_ASSOC) as $row){
    echo 'Make: ' . $row['make'] . '<br>';
    echo 'Model: ' . $row['model'] . '<br>';
}

Note that this function should NOT be used whenever external variables are being used! If you are using variables that have come from the client (a form variable or a GET variable, for example), then you should be using one of the methods below.

Selecting multiple rows with PDO.

If you’re looking to SELECT multiple rows from MySQL using a prepared statement, then you can do it like so:

//Selecting multiple rows using prepared statements.
$sql = "SELECT `id`, `make`, `model` FROM `cars` WHERE `make` = :make";

//Prepare our SELECT statement.
$statement = $pdo->prepare($sql);

//The make that we are looking for.
$make = 'Nissan';

//Bind our value to the paramater :make.
$statement->bindValue(':make', $make);

//Execute our statement.
$statement->execute();

//Fetch our rows. Array (empty if no rows). False on failure.
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

//Loop through the $rows array.
foreach($rows as $row){
    echo 'Make: ' . $row['make'] . '<br>';
    echo 'Model: ' . $row['model'] . '<br>';
}

Step-by-step guide to the code above:

  1. We constructed our SQL SELECT statement. Notice how we created a placeholder / parameter called :make.
  2. We “prepared” our SELECT statement.
  3. The variable $make is a string that contains the car make that we want to select from our cars table.
  4. We bind the $make variable to our :make placeholder.
  5. We executed the prepared statement. It is at this stage that the SQL SELECT query is actually run.
  6. We fetched our selected table rows from our PDO Statement using the PDOStatement::fetchAll function. We also provided an optional parameter called PDO::FETCH_ASSOC, which tells the function to return an associative array.
  7. We looped through our data.

Selecting a single row with the PDO object.

In this example, we’re going to use a prepared statement to select a single row from a MySQL database:

//Selecting a single row!
$sql = "SELECT `id`, `make`, `model` FROM `cars` WHERE `id` = :id";

//Prepare our SELECT statement.
$statement = $pdo->prepare($sql);

//The Primary Key of the row that we want to select.
$id = 1;

//Bind our value to the paramater :id.
$statement->bindValue(':id', $id);

//Execute our SELECT statement.
$statement->execute();

//Fetch the row.
$row = $statement->fetch(PDO::FETCH_ASSOC);

//If $row is FALSE, then no row was returned.
if($row === false){
    echo $id . ' not found!';
} else{
    echo 'Found: ' . $row['make'] . ' ' . $row['model'];
}

As you can see, this code snippet is pretty similar to the “Fetch All” statement that we used in the example above. However, this time, instead of using “fetch all” to select multiple rows, we’re using “fetch” to select one row. Note that this function will return a boolean FALSE value if no values are found!

Related recommended reading: