Before we can start running queries, our program needs to set up a connection to the relevant database. In the context of database programming, a connection is like a pipeline of sorts that allows communication between a DBMS and an application program. With MySQL databases, you have to supply the following information when making a database connection: the host or URL of the database server, the database name, and the database user name and password. With SQLite databases, you only need to supply the path to the file:

$pdo = new PDO('sqlite:./movies.db');

Listings 14.3 and 14.4 illustrate how to make a connection to a database using the mysqli and PDO approaches. Notice that the PDO approach uses a connection string to specify the database details. A connection string is a standard way to specify database connection details: it is a case-sensitive string containing name=value pairs separated by semicolons.

// modify these variables for your installation
$host = "localhost";
$database = "bookcrm";
$user = "testuser";
$pass = "mypassword";

$connection = mysqli_connect($host, $user, $pass, $database);

// modify these variables for your installation
$connectionString = "mysql:host=localhost;dbname=bookcrm";
// you may need to add this if db has UTF data
$connectionString .= ";charset=utf8mb4;";
$user = "testuser";
$pass = "mypassword";

$pdo = new PDO($connectionString, $user, $pass);

<?php
define('DBHOST', 'localhost');
define('DBNAME', 'bookcrm');
define('DBUSER', 'testuser');
define('DBPASS', 'mypassword');
define('DBCONNSTRING',"mysql:host=". DBHOST. ";dbname=". DBNAME);
?>

require_once('protected/config.inc.php');
$pdo = new PDO(DBCONNSTRING,DBUSER,DBPASS);

Unfortunately not every database connection always works. Sometimes errors occur when trying to create a connection for the first time; other times connection errors occur with normally trouble-free code because there is a problem with the database server. Whatever the reason, you always need to be able to handle potential connection errors in your code.

The approach in PDO for handling connection errors uses try...catch ­exception-handling blocks. Listing 14.7 illustrates this approach.

try {
   $pdo = new PDO(DBCONNSTRING,DBUSER,DBPASS);
   ...
}
catch (PDOException $e) {
   die(  $e->getMessage()  );
}

try {
   $pdo = new PDO(DBCONNSTRING,DBUSER,DBPASS);
   // useful during initial development and debugging
   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   ...
}

If the connection to the database is successfully created, then you are ready to construct and execute the query. This typically involves creating a string that contains the SQL statement and then calling one of the query functions/methods as shown in Listings 14.9 and 14.10. Remember that SQL is case insensitive, so the use of uppercase for the SQL reserved words is purely a coding convention to increase readability.

$sql = "SELECT * FROM Categories ORDER BY CategoryName";

// returns a PDOStatement object
$result = $pdo->query($sql);

$sql = "DELETE FROM artists WHERE LastName = 'Connolly'";

// returns number of rows that were deleted
$count = $pdo->exec($sql);

So what type of data is returned by these query functions? The exec() function in Listing 14.10 returns an integer indicating the number of affected records; it shouldn't be used for SELECT queries. The query() function in Listing 14.9 returns a result set, a type of cursor or pointer to the returned data. As the comment indicates, this result set is in the form of a PDOStatement object. In the next section you will see how you can examine and display this result set. If the query was unsuccessful (for instance, a query with a WHERE clause that was not matched by the table data), then the query function returns FALSE.

If you are running a SELECT query, then you will want to do something with the retrieved result set, such as displaying it, or performing calculations on it, or searching for something in it. Listing 14.11 illustrates one technique for displaying content from a result set.

$sql = "SELECT * FROM Paintings ORDER BY Title";

// run the query
$result = $pdo->query($sql);

// fetch a record from result set into an associative array
while ($row = $result->fetch()) {
   // the keys match the field names from the table
   echo $row['ID']. " - ". $row['Title'];
   echo "<br/>";
}

Notice that some type of fetch function must be called to move the data from the database result set to a regular PHP array. Once in the array, then you can use any PHP array manipulation technique. Figure 14.22 illustrates the process of fetching from the result set.

Figure 14.22 Fetching from a result set

The PDO query() method returns an object of type PDOStatement. Interestingly, PDOStatement objects behave just like an array when passed into a foreach loop. That means the following loop would be equivalent to that shown in Listing 14.11:

foreach ($result as $row) {
  echo $row['Title']. "<br/>";
}

Looking at this foreach loop code, you would be tempted to think the query() method returned an array. But it in fact returns a forward-only cursor—that is, a pointer to the next record—and not an array. This can be seen if you tried two foreach loops in a row on the same $result variable: the first loop would display all the returned data, while the second loop would display nothing since the $result cursor would be at the end of the data after the first loop.

It is possible to fetch all the remaining rows in the result set into an array using the fetchAll() method, as shown in Listing 14.12.

$data = $result->fetchAll();
echo '<h2>First loop</h2>';
foreach ($data as $row){
    echo $row['Title']. "<br>";
}
echo '<h2>Second loop</h2>';
foreach($data as $row){
    echo $row['Title']. "<br>";
}

Because server memory is always a finite and constrained resource, fetchAll() should only be used for small blocks of data.

Interestingly, by default the fetch() method returns an array indexed by both the column name and the column number. This means in the loop code in Listing 14.11, you could also access the title column data using $row[1].

This duplicated array data is undesirable, however, when you are encoding it as a JSON string (for instance, when you are creating a PHP-based API). You can eliminate the numerically indexed duplicates by adding the following optional parameter:

$row = $result->fetch(PDO::FETCH_ASSOC);

class Book {
    public $ID;
    public $Title;
    public $CopyrightYear;
    public $Description;
 }

$sql = "SELECT * FROM Books";
$result = $pdo->query($sql);

// fetch a record into an object of type Book
while (  $b = $result->fetchObject('Book')  ) {
    // the property names match the field names from the table
    echo 'ID: '.  $b->ID  . '<br/>';
    echo 'Title: '.  $b->Title  . '<br/>';
    echo 'Year: '.  $b->CopyrightYear  . '<br/>';
    echo 'Description: '.  $b->Description  . '<br/>';
    echo '<hr>';
}

class Book {
    public $id;
    public $title;
    public $year;
    public $description;

    function __construct($record)
    {
       $this->id = $record['ID'];
       $this->title = $record['Title'];
       $this->year = $record['CopyrightYear'];
       $this->description = $record['Description'];
    }
}
...
// in some other page or class
$sql = "SELECT * FROM Books";
$result = $pdo->query($sql);

// fetch a record normally
while ( $row = $result->fetch() ) {
    $b = new Book($row);
    echo 'ID: '.  $b->id  . '<br/>';
    echo 'Title: '.  $b->title  . '<br/>';
    echo 'Year: '.  $b->year  . '<br/>';
    echo 'Description: '.  $b->description  . '<br/>';
    echo '<h>';
}

When you are finished retrieving and displaying your requested data, you should release the memory used by any result sets and then close the connection so that the database system can allocate it to another process. Listing 14.15 illustrates the code for closing the connection.

try {
   $pdo = new PDO(DBCONNSTRING,DBUSER,DBPASS);
   ...
   // closes connection and frees the resources used by the PDO object
   $pdo = null;
}

Many programmers do not explicitly code this step since it will happen anyway behind the scenes when the PHP script has finished executing. Nonetheless, it makes sense to get into the habit of explicitly closing the connection immediately after your script no longer needs it. Waiting until the entire page script has finished might not be wise since over time functionality might get added to the page, which lengthens its execution time. For instance, imagine a page that displays information from a database and which doesn’t explicitly close the connection but relies on the implicit connection closing once the script finishes execution. Then at some point in the future, new functionality gets added; this new functionality displays information obtained from a third-party web service. This externality has a time cost, which means the page takes longer to finish executing. That connection is now wasting finite server resources (that could be helping other requests), since the database processing is finished, but the page script has not finished executing due to the delay incurred by this external service. For this reason, it is a good practice to explicitly close your connections.

Recall that we typically use SQL in PHP to retrieve data from a database and then echo it out in a page’s markup. Figure 14.1 illustrated how the same page design can be used to display different data records. But how does a PHP page “know” which data record to display? In PHP, this is usually accomplished via query string parameters, as shown in Figure 14.23.

Figure 14.23 Integrating user input data into a query

So how would you accomplish this in PHP? Listing 14.16 illustrates a straightforward solution.

$pdo = new PDO(DBCONNSTRING,DBUSER,DBPASS);
$sql = "SELECT * FROM Galleries WHERE GalleryID=". $_GET["id"];
$result = $pdo->query($sql);

While this does work, it opens our site to one of the most common web security vulnerabilities, the SQL injection attack. In this attack, a devious (or curious) user decides to enter a SQL statement into a form’s text box (or indeed directly into any query string). As you will see later in Chapter 16 on Security, the SQL injection attack is quite common and can be incredibly dangerous to a site’s database.

// retrieve parameter value from query string
$id = $_GET['id'];

/* method 1 – notice the ? parameter */
$sql = "SELECT Title, CopyrightYear FROM Books WHERE ID = ?";
$statement = $pdo->prepare($sql);
$statement->bindValue(1, $id); // bind to the 1st ? parameter
$statement->execute();

/* method 2 */
$sql = "SELECT Title, CopyrightYear FROM Books WHERE ID = :id";
$statement = $pdo->prepare($sql);
$statement->bindValue(':id', $id);
$statement->execute();

/* technique 1 - question mark placeholders, explicit binding */
$sql = "INSERT INTO books (ISBN10, Title, CopyrightYear, ImprintId,
        ProductionStatusId, TrimSize, Description) VALUES (?,?,?,?,?,?,?)";
$statement = $pdo->prepare($sql);
$statement->bindValue(1, $_POST['isbn']);
$statement->bindValue(2, $_POST['title']);
$statement->bindValue(3, $_POST['year']);
$statement->bindValue(4, $_POST['imprint']);
$statement->bindValue(4, $_POST['status']);
$statement->bindValue(6, $_POST['size']);
$statement->bindValue(7, $_POST['desc']);
$statement->execute();

/* technique 2 - named parameters */
$sql = "INSERT INTO books (ISBN10, Title, CopyrightYear, ImprintId,
        ProductionStatusId, TrimSize, Description) VALUES (:isbn, :title,:year,:imprint,:status,:size,:desc) ";
$statement = $pdo->prepare($sql);
$statement->bindValue(':isbn', $_POST['isbn']);
$statement->bindValue(':title', $_POST['title']);
$statement->bindValue(':year', $_POST['year']);
$statement->bindValue(':imprint', $_POST['imprint']);
$statement->bindValue(':status', $_POST['status']);
$statement->bindValue(':size', $_POST['size']);
$statement->bindValue(':desc', $_POST['desc']);
$statement->execute();

$statement->bindValue(4, $_POST['imprint']);
$statement->bindValue(4, $_POST['status']);
$statement->bindValue(6, $_POST['size']);

$year1=1800;
$year2=1900;
$sql = "SELECT * FROM Paintings WHERE YearOfWork > ? and YearOfWork < ?";
$statement = $pdo->prepare($sql);
$statement->execute( array($year1,$year2) );

// alternate to the above
$sql = "SELECT * FROM Paintings WHERE YearOfWork>:y1 and YearOfWork<:y2";
$statement = $pdo->prepare($sql);
$statement->execute( array("y1"=>$year1,"y2"=>$year2) );

While transactions are unnecessary when retrieving data, they should be used for most scenarios involving any database writes. As mentioned back in Section 14.3.4, transactions in PHP can be done via SQL commands or via the database API. Since the earlier section covered the SQL commands for transactions, let’s look at the techniques using our two APIs. Listing 14.20 demonstrates how to make use of transactions.

$pdo = new PDO($connString,$user,$pass);
// turn on exceptions so that exception is thrown if error occurs
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
...
try {
    // begin a transaction
    $pdo->beginTransaction();
    // a set of queries: if one fails, an exception will be thrown
    $pdo->exec("INSERT INTO Categories (CategoryName) VALUES
                ('Philosophy')");
    $pdo->exec("INSERT INTO Categories (CategoryName) VALUES ('Art')");
    // if we arrive here, it means that no exception was thrown
    // which means no query has failed, so we can commit the
    // transaction
    $pdo->commit();
} catch (Exception $e) {
    // we must rollback the transaction since an error occurred
    // with insert
    $pdo->rollback();
}

When you are first learning web development or any type of programming, one’s focus is generally quite short term. “I just want to get this to work!” is the common cry of all new programmers. That is, we tend to think that the initial coding phase of a project is the most important or the most time-consuming. But, in fact, it’s long been recognized among experienced developers that it is the maintenance and revisions phase that ends up being the costliest in terms of time spent on any software project. For web projects, this is likely even more true, given the relative common frequency with which web projects have their visual design and functionalities updated.

The idea behind proper software design is that by spending more time and effort in the initial coding phase, the resulting code base will be easier to maintain and revise in the future. Perhaps the most important of these software design goals is to reduce the number of dependencies to externalities in your code (also known as reducing coupling). Why is this so important? The goal of reducing dependencies is to shield as much of your code base from things that might change in the future. Database details such as connection strings and table and field names are examples of externalities. These details tend to change over the life of a web application. Initially, the database for our website might be a SQLite database on our development machine; later it might change to a MySQL database on a data server, and even later, to a relational cloud service. Ideally, with each change in our database infrastructure, we would have to change very little in our code base. But in the type of database coding we have used in this section, this would not be the case. By using PDO code containing SQL and connection details in each PHP page, every time we change our database infrastructure, we would have to change every PHP page, which is far from ideal from a software design perspective.

What can we do to make our database code more maintainable? One simple step might be to extract all PDO code into separate functions or classes and use those instead. For instance, Listing 14.21 shows a simple class that encapsulates the ability to create a connection and run a query. Other features such as transaction support could also be added to this class.

class DatabaseHelper {

    public static function createConnection($values=array()) {
        $connString = $values[0];
        $user = $values[1];
        $password = $values[2];

        $pdo = new PDO($connString,$user,$password);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        return $pdo;
    }
    public static function runQuery($pdo, $sql, $parameters=array())     {
        // Ensure parameters are in an array
        if (!is_array($parameters)) {
            $parameters = array($parameters);
        }
        $statement = null;
        if (count($parameters) > 0) {
            // Use a prepared statement if parameters
            $statement = $pdo->prepare($sql);
            $executedOk = $statement->execute($parameters);
            if (! $executedOk) {
                throw new PDOException;
            }
        } else {
            // Execute a normal query
            $statement = $pdo->query($sql);
            if (!$statement) {
                throw new PDOException;
            }
        }
        return $statement;
    }
}

You might wonder why creating the connection happens separately in DatabaseHelper and is not part of the runQuery() method. Recall that database connections are a very limited resource and are very slow to create. Any given PHP page should thus only create one connection, and then use that single connection for all of its database access.

The following code illustrates two example uses of this class.

try {
  $conn = DatabaseHelper::createConnectionInfo(array(DBCONNECTION,
            DBUSER, DBPASS));
  $sql = "SELECT * FROM Paintings ";
  $paintings = DatabaseHelper::runQuery($conn, $sql, null);
  foreach ($paintings as $p) {
     echo $p["Title"];
  }
  $sql = "SELECT * FROM Artists WHERE Nationality=?";
  $artists = DatabaseHelper::runQuery($conn, $sql, Array("France"));
}

While an improvement, we still have a database dependency in this code with the SQL statements and field names. You could eliminate the SQL from this code by encapsulating the code needed for accessing a given table into its own class, as shown in Listing 14.22. Such a class is often called a table gateway or data access class.

class PaintingDB {
   private static $baseSQL = "SELECT * FROM Paintings ";
   public function __construct($connection) {
      $this->pdo = $connection;
   }
   public function getAll() {
      $sql = self::$baseSQL;
      $statement = DatabaseHelper::runQuery($this->pdo, $sql, null);
      return $statement->fetchAll();
   }
   public function findById($id) {
      $sql = self::$baseSQL . " WHERE PaintingID=?";
      $statement = DatabaseHelper::runQuery($this->pdo, $sql, Array($id));
      return $statement->fetch();
   }
   public function getAllForArtist($artistID) {
      $sql = self::$baseSQL . " WHERE Paintings.ArtistID=?";
      $statement = DatabaseHelper::runQuery($this->pdo, $sql, Array($artistID));
      return $statement->fetchAll();
   }
   public function getAllForGallery($galleryID) {
      $sql = self::$baseSQL . " WHERE Paintings.GalleryID=?";
      $statement = DatabaseHelper::runQuery($this->pdo, $sql,       Array($galleryID));
      return $statement->fetchAll();
   }
   // add methods for updating, inserting, and deleting records if needed
}

The code to use this gateway class might look like the following:

// we could alternately put try...catch in the gateway methods
try {
  $gate = new PaintingDB($conn);
  $paintings = $gate->getAll();
  foreach ($paintings as $p) { ... }
}

Now all the PDO and SQL have been removed from our PHP pages. If we need to make changes to the SQL, we will only need to change the gateway classes.