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.