PHP SQL Server: Selecting Data

Summary: in this tutorial, you will learn how to select one or multiple rows from a table in SQL Server using PHP.

This tutorial begins where the inserting multiple rows into a table from PHP tutorial left off.

We’ll show you how to select one row and multiple rows from the Authors table in the BookStore database.

Selecting one row #

Step 1. Add the findById method to the AuthorDB class:

function findById(int $authorId)
{
    $sql = 'SELECT * FROM Authors WHERE AuthorID = :id';
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['id' => $authorId]);
    $row = $stmt->fetch(\PDO::FETCH_ASSOC);
    return $row ? new Author($row['FirstName'], $row['LastName'], $row['BirthDate'], $row['AuthorID']) : null;
}Code language: PHP (php)

First, define a method called findById that takes $authorId as the parameter:

function findById(int $authorId)Code language: PHP (php)

Second, construct a SELECT statement that retrieves all columns from the Authors table where the AuthorID matches the specified parameter:

$sql = 'SELECT * FROM Authors WHERE AuthorID = :id';Code language: PHP (php)

In the query, the :id is a named placeholder. Using the parameterized query with a named placeholder makes the code easier to read and helps prevent SQL injection.

Third, create a prepared statement for executing the SELECT statement:

$stmt = $this->conn->prepare($sql);Code language: PHP (php)

Fourth, execute the SELECT statement with an associative array that maps the :id placeholder to the $authorId value:

$stmt->execute(['id' => $authorId]);Code language: PHP (php)

Fifth, fetch the next row from the result set as an associative array with the column names as keys of each item:

$row = $stmt->fetch(\PDO::FETCH_ASSOC);Code language: PHP (php)

Note that the \PDO::FETCH_ASSOC fetch mode instructs PDO to return the result set as an associative array.

Finally, create an Author object from each row in the result set and return it:

return $row ? new Author($row['FirstName'], $row['LastName'], $row['BirthDate'], $row['AuthorID']) : null;Code language: PHP (php)

Step 2. Modify the index.php and use the findById method to find the author with id 1:

<?php

require_once 'authordb.php';

// Connect to the SQL Server
$conn = require_once 'connect.php';


// Find author with id 1
$authorDB = new AuthorDB($conn);
$author = $authorDB->findById(1);
if($author != null) {
    echo 'Author: ' . $author->getFirstName() . ' ' . $author->getLastName();
}Code language: PHP (php)

How it works.

First, include the authordb.php to ensure the AuthorDB class is available in the script:

require_once 'authordb.php';Code language: PHP (php)

Second, connect to the SQL Server and assign the PDO object to $conn variable:

$conn = require_once 'connect.php';Code language: PHP (php)

Third, create a new AuthorDB object with the $conn object:

$authorDB = new AuthorDB($conn);Code language: PHP (php)

Fourth, retrieve the author with the id 1 by calling the findById() method of the AuthorDB object:

$author = $authorDB->findById(1);Code language: PHP (php)

Finally, display the author’s first and last names if the $author object is not null:

if($author != null) {
    echo 'Author: ' . $author->getFirstName() . ' ' . $author->getLastName();
}Code language: PHP (php)

Step 3. Run the index.php file on the web browser

It’ll show the following text on the page:

Author: Anthony PhamCode language: plaintext (plaintext)

Selecting multiple rows #

Step 1. Define the findAll() method in the AuthorDB class that retrieves all rows from the Authors table:

public function findAll()
{
    $sql = 'SELECT * FROM Authors ORDER BY FirstName';
    $stmt = $this->conn->query($sql);
    $authors = [];
    while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
        $authors[] = new Author($row['FirstName'], $row['LastName'], $row['BirthDate'], $row['AuthorID']);
    }
    return $authors;
}Code language: PHP (php)

How it works.

First, define a method called findAll() method:

function findById(int $authorId)Code language: PHP (php)

Second, construct a SELECT statement that retrieves all columns and rows from the Authors table sorted by the first names:

$sql = 'SELECT * FROM Authors ORDER BY FirstName';Code language: PHP (php)

Third, create a statement for executing the SELECT statement:

$stmt = $this->conn->query($sql);Code language: PHP (php)

Fourth, initialize an empty array that stores the Author objects:

$authors = [];Code language: PHP (php)

Fifth, fetch each row from the result set, create an Author object from each, and add it to the $authors array:

while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
   $authors[] = new Author($row['FirstName'], $row['LastName'], $row['BirthDate'], $row['AuthorID']);
}Code language: PHP (php)

Finally, return the $authors object:

return $authors;Code language: PHP (php)

Step 2. Modify the index.php to use the findAll() method:

<?php

require_once 'authordb.php';

// Connect to the SQL Server
$conn = require_once 'connect.php';

$authorDB = new AuthorDB($conn);

// Retrieve all authors
$authors = $authorDB->findAll();
foreach ($authors as $author) {
    echo $author->getFirstName() . ' ' . $author->getLastName() . '<br>';
}Code language: PHP (php)

How it works.

First, include the authordb.php file to use the AuthorDB class in the script:

require_once 'authordb.php';Code language: PHP (php)

Second, connect to the SQL Server and assign the PDO object to the $conn object:

$conn = require_once 'connect.php';Code language: PHP (php)

Third, create an AuthorDB object with the provided PDO object ($conn):

$authorDB = new AuthorDB($conn);Code language: PHP (php)

Fourth, retrieve all rows from the Authors table and return an array of Author objects:

$authors = $authorDB->findAll();Code language: PHP (php)

Finally, display the author’s first and last names:

foreach ($authors as $author) {
    echo $author->getFirstName() . ' ' . $author->getLastName() . '<br>';
}Code language: PHP (php)

Step 3. Open the index.php on the web browser.

It should return a list of authors:

Alexander Turner
Ann Kelly
Anthony Pham
Barbara Lopez
Charles Nicholson
...Code language: plaintext (plaintext)

Summary #

Execute the SELECT statement to retrieve data from a table.

Was this tutorial helpful?