Indices

Relational databases are traditionally biased toward read operations; this means that a certain amount of efficiency is sacrificed when data is written to a table so that future read operations can perform better. In other words, databases are designed so that data can be searched and extracted as efficiently as possible.

This is accomplished by means of indices, which make it possible to organize the data in a table according to one or more columns. Indices are one of the cardinal elements of relational databases and, if properly used, can have a significant impact on the ability of your applications to manipulate data efficiently. Misuse or lack of indices is one of the most common causes of performance problems in database-driven applications.

Indices can usually be created on one or more columns of a table. Generally speaking, they should be created on those columns that you are going to use later in search operations; fewer columns will often cause the engine to ignore the index (thus wasting it), and more columns will require extra work, thus reducing the effectiveness of the index.

An index can also be declared as unique, in which case it will prevent the same combination of values from appearing more than once in the table. For example, if you create a unique index on the columns ID and FirstName, the combination ID = 10 and FirstName = 'Davey' will be allowed only once, without preventing its individual components from appearing any number of times. For example, ID = 11 and FirstName == 'Ben' would be perfectly acceptable. Even ID = 12 and FirstName == 'Davey' would be acceptable in a compound index.

Primary keys are a special type of unique index that is used to determine the “natural” method of uniquely identifying a row in a table. From a practical perspective, primary keys differ from unique indices only in the fact that there can only be one primary key in each table, while an arbitrary number of unique indices can exist.

Relationships

As we mentioned at the beginning of this chapter, data relationships are one of the most important aspects of relational databases. Relationships are established between tables to ensure the consistency of data at all times; for example, if your database contains a table that holds the names of your customers and another table that contains their addresses, you don’t want to be able to delete rows from one if there are still corresponding rows in the other.

Relationships between tables can be of three types:

• One-to-one: only one row in the child table can correspond to each row in the parent table
• One-to-many: an arbitrary number of rows in the child table can correspond to any one row in the parent table
• Many-to-many: an arbitrary number of rows in the child table can correspond to an arbitrary number of rows in the parent table

It’s interesting to note that the SQL language only offers facilities for directly creating one-to-one and one-to-many relationships. Many-to-many relationships require a bit of trickery and the use of an intermediate table to hold the relationships between the parent and child tables.

SQL Data Types

SQL is the most common database manipulation language (DML) used in relational databases, and SQL-92, as defined by the American National Standards Institute (ANSI), is its most commonly used variant. Although SQL is considered a “standard” language, it is somewhat limited in relation to the real-world needs of almost any application. As a result, practically every database system in existence implements its own “dialect” of SQL while, for the most part, maintaining full compatibility with SQL-92. This makes writing truly portable applications very challenging.

SQL supports a number of data types, which provide a greater degree of flexibility than PHP in how the data is stored and represented. For example, numeric values can be stored using a variety of types:

To these, most database systems add their own, nonstandard variants; for example, MySQL supports a data type called tinyint, which is represented as a one-byte signed integer number.

Clearly, all of these data types are converted into either integers or floating-point numbers when they are retrieved into a PHP variable, which is not normally a problem. However, you need to be aware of the precision and range of each data type when you write data from a PHP script into a database table, since it’s quite possible that you will cause an overflow (which a database system should at least report as a warning).

This is even more apparent—and, generally, more common—when you deal with string data types. SQL-92 defines two string types:

The only difference between these two data types is that a char string will always have a fixed length, regardless of how many characters it contains (the string is usually padded with spaces to the column’s length). In both cases, however, a string column must be given a length (usually between 1 and 255 characters, although some database systems do not follow this rule), which means that any string coming from PHP, where it can have an arbitrary length, can be truncated, usually without even a warning, thus resulting in the loss of data.

Most database systems also define an arbitrary-length character data type (usually called text) that more closely resembles PHP’s strings. However, this data type usually comes with a number of constraints (such as a maximum allowed length and severe limitations on search and indexing capabilities). Therefore, you will still be forced to use char and (more likely) varchar, with all of their limitations.

Strings in SQL are enclosed by single quotation strings:

'This is a string, and here''s some escaping: ''Another String'''

There a few important items to note: first of all, standard SQL does not allow the insertion of any special escape sequences like . In addition, single quotation marks are normally escaped using another quotation mark; however, and this is very important, not all database systems follow this convention. Luckily, however, almost every database access extension that supports PHP also provide specialized functions that will take care of escaping the data for you.

SQL character strings act differently from PHP strings. In most cases, the former are “true” text strings, rather than collections of binary characters; therefore, you won’t be able to store binary data in an SQL string. Most database systems provide a separate data type (usually called “BLOB”, for Binary Large OBject) for this purpose.

The data type that perhaps causes the most frequent problems is datetime, which encapsulates a given time and date. In general, a database system’s ability to represent dates goes well beyond PHP’s, opening the door to all sorts of potential problems, which are best solved by keeping all of your date-manipulation operations inside the database itself. Only extract dates in string form when they are needed.

The last data type that we will examine is NULL. This is a special data type that has a distinct meaning that is not directly interchangeable with any other value of any other data type; NULL is not the same as 0, or an empty string. A column is set to NULL to indicate that it does not contain any value. When defining your tables, you can specify whether a column should allow NULL.

Columns that allow NULL values cannot be used as part of a primary key.

Creating Databases and Tables

The creation of a new database is relatively simple:

CREATE DATABASE <dbname>;
CREATE SCHEMA <dbname>;

These two forms are equivalent to each other—<dbname> is the name of the new database you want to create. As you can see, there is no mechanism for providing security or access control—this is, indeed, a shortcoming of SQL that is solved by each database system in its own way.

The creation of a table is somewhat more complex, given that you need to declare its structure as well:

CREATE TABLE <tablename> (
    <col1name> <col1type> [<col1attributes>],
    [...
    <colnname> <colntype> [<colnattributes>]]
);

As you can see, it is necessary to declare a column’s data type; as you probably have guessed, most database systems are very strict about data typing, unlike PHP. Here’s the declaration for a simple table that we’ll use throughout the remainder of this chapter:

CREATE TABLE book (
    id INT NOT NULL PRIMARY KEY,
    isbn VARCHAR(13),
    title VARCHAR(255),
    author VARCHAR(255),
    publisher VARCHAR(255)
);

Here, we start by declaring a column of type INT that cannot contain NULL values and is the primary key of our table. The other columns are all VARCHARs of varying length (note how we are using 255 as the maximum allowable length; this is a safe bet and it is true in many, but not all, database systems).

Creating Indices and Relationships

Indices can be created when the table is created, as we did with the primary key above; alternatively, you can create them separately:

CREATE INDEX <indexname>
ON <tablename> (<column1>[, ..., <columnn>]);

For example, suppose we wanted to create a unique index on the isbn column of the book table we created earlier:

CREATE INDEX book_isbn ON book (isbn);

The name of the index is, of course, entirely arbitrary and only significant when you are deleting it; however, it must still be unique and abide by the naming rules described above.

Foreign-key relationships are created either when a table is created or at a later date with an altering statement. For example, suppose we wanted to add a table that contains a list of all of the chapter titles for every book:

CREATE TABLE book_chapter
    isbn VARCHAR(13) REFERENCES book (id),
    chapter_number INT NOT NULL,
    chapter_title VARCHAR(255)
);

This code creates a one-to-many relationship between the parent table book and the child table book_chapter based on the isbn field. Once this table is created, you can only add a row to it if the ISBN you specify exists in book. In this way, foreign keys help you maintain the relationship between a chapter and a book. In this case a row in book_chapter can not be orphaned—that is, stored without a parent row in book.

To create a one-to-one relationship, simply make the connective columns of a one-to-many relationship the primary key of the child table.

Dropping Objects

The act of deleting an object from a schema—be it a table, an index, or even the schema itself—is called dropping. It is performed by a variant of the DROP statement:

DROP TABLE book_chapter;

A good database system that supports referential integrity will not allow you to drop a table if doing so would break the consistency of your data. Thus, the book table cannot be deleted until book_chapter is dropped.

The same technique can be used to drop an entire schema:

DROP SCHEMA my_book_database;

Adding and Manipulating Data

While most of the time you will be retrieving data from a database, being able to insert it is essential to using it later. This is done by means of the INSERT statement, which takes two forms:

INSERT INTO <tablename> VALUES (<field1Value>[, ..., <fieldNValue>]);

INSERT INTO <tablename>
(<field1>[, ..., <fieldN>])
VALUES
(<field1Value>[, ..., <fieldNValue>]);

The first form is used when you want to provide values for every column in your table; in this case, the column values must be specified in the same order in which the columns appear in the table declaration. This form is almost never ideal; for one thing, you may not even be able to specify a value for each column—some of the columns may be calculated automatically by the system, and forcing a value onto them may actually cause an error to be thrown. In addition, using this form implies that you expect the order of the columns to never change—this is never a good idea if you plan for your application to run for more than a month!

In its second form, the INSERT statement consists of three main parts. The first part tells the database engine which table to insert the data into; the second part indicates the columns for which we’re providing a value; and the third part contains the actual data to insert. Note how string values are enclosed in single quotes and single quotes within the string are escaped with a . Here’s an example:

INSERT INTO book (isbn, title, author) 
VALUES ('0812550706', 'Ender's Game', 'Orson Scott Card');

Adding records to the database is, of course, not very useful without the ability to modify them. To update records, you can use the UPDATE statement, which can alter the value of one or more columns for all rows, or for a specific subset of rows by means of a WHERE clause. For example, the following UPDATE statement updates the publisher for all records in the book table to a value of 'Tor Science Fiction':

UPDATE book SET publisher = 'Tor Science Fiction';

Since it is not likely that all books in the table will have the same publisher (and, if they did, you wouldn’t need a database column to tell you), you can restrict the range of records over which the UPDATE statement operates:

UPDATE book 
SET publisher = 'Tor Science Fiction'
    , author = 'Orson S. Card'
WHERE isbn = '0812550706';

This UPDATE statement will update only the record (or records) for which isbn is equal to the value '0812550706'. Notice also that this statement illustrates another feature of the UPDATE statement: it is possible to update multiple columns at a time using the same statement.

Removing Data

In a dynamic application, data never remains constant. It always changes—and, sometimes, it becomes superfluous and needs to be deleted. SQL database engines implement the DELETE statement for this purpose:

DELETE FROM book;

This simple statement will remove all records from the book table, leaving behind an empty table. If you have foreign keys defined, this may even delete records in other tables that reference the parent record that is being deleted. At times, it is necessary to remove all records from tables, but most of the time, you will want to provide parameters limiting the deletion to specific records. Again, a WHERE clause achieves this:

DELETE FROM book WHERE isbn = '0812550706';

Retrieving Data

As we mentioned earlier, relational databases are biased toward read operations; therefore, it follows that the most common SQL statement is designed to extract data from a database.

To retrieve data from any SQL database engine, you use a SELECT statement; SELECT statements can be very simple or incredibly complex, depending on your needs. The most basic form, however, is simple:

SELECT * FROM book;

The statement begins with the verb or action keyword SELECT, followed by a comma-separated list of columns to include in the dataset retrieved. In this case, we use the special identifier *, which is equivalent to extracting all of the columns available in the dataset. Following the list of columns is the keyword FROM, which is itself followed by a comma-separated list of tables. This statement retrieves data from only one table, the book table.

The format in which the dataset is returned to PHP by the database system depends largely on the system itself and on the extension you are using to access it; for example, the “traditional” MySQL library returns datasets as resources from which you can extract individual rows in the form of arrays. Newer libraries, on the other hand, tend to encapsulate result sets in objects.

You will rarely need to gain access to all of the records in a table—after all, relational databases are all about organizing data and making it easily searchable. Therefore, you will most often find yourself limiting the rows returned by a SELECT statement using a WHERE clause. For example, for the book table, you may wish to retrieve all books written by a specific author. This is possible using WHERE:

SELECT * FROM book WHERE author = 'Ray Bradbury';

The recordset returned by this SELECT statement will contain all books written by the author specified in the WHERE clause (assuming, of course, that your naming convention is consistent). You may also list more than one parameter in a WHERE clause to further limit or broaden the results, using a number of logical conjunctions:

SELECT * 
    FROM book
    WHERE author = 'Ray Bradbury'
        OR author = 'George Orwell';

SELECT *
  FROM book
  WHERE author = 'Ray Bradbury'
        AND publisher LIKE '%Del Ray';

The first example statement contains an OR clause and, thus, broadens the results to return all books by either author, while the second statement restricts the results with an AND clause specifying all books by the author that were also published by a specific publisher. Note, here, the use of the LIKE operator, which provides a case-insensitive match and allows the use of the % wildcard character to indicate an arbitrary number of characters. Thus, the expression AND publisher LIKE '%Del Ray' will match any publisher that ends in the string del ray, regardless of case.

Inner Joins

An inner join returns rows from both tables only if keys from both tables can be found that satisfy the join conditions. For example:

SELECT * 
   FROM book
   INNER JOIN book_chapter ON book.isbn = book_chapter.isbn;

As you can see, we declare an inner join that creates a link between book and book_chapter; rows are returned only if a common value for the isbn column can be found for both tables.

Note that inner joins only work well with assertive conditions—negative conditions often return bizarre results. For example:

SELECT *
  FROM book
  INNER JOIN book_chapter ON book.isbn <> book_chapter.isbn;

You would probably expect this query to return a list of all the records in the book table that do not have a corresponding set of records in book_chapter. However, the database engine actually returns a dataset that contains an entry for each record in book_chapter that does not match each record in book; the end result is a dataset that contains every line in book_chapter repeated many times over (the actual size of the set depending on the number of rows between the two tables that do have matching values for their respective isbn columns).

Outer Joins

Where inner joins restrict the results returned to those that match records in both tables, outer joins return all records from one table while restricting the other table to matching records, which means that some of the columns in the results will contain NULL values. This is a powerful, yet sometimes confusing, feature of SQL database engines.

Left joins are a type of outer join in which every record in the left table that matches the WHERE clause (if there is one) will be returned regardless of a match made in the ON clause of the right table. For example, consider the following SQL statement with a LEFT JOIN clause:

SELECT book.title, author.last_name 
    FROM author
    LEFT JOIN book ON book.author_id = author.id;

The table on the left is the author table because it is the table included as the primary table for the statement in the FROM clause. The table on the right is the book table because it is included in the JOIN clause. Since this is a LEFT JOIN and there is no further WHERE clause limiting the results, all records from the author table will be in the returned results. However, only those records from the book table that match the ON clause where book.author_id = author.id will be among the results.

Author table

Book table

Join Results

Right joins are analogous to left joins, only reversed: instead of returning all results from the “left” side, the right join returns all results from the “right” side, restricting results from the “left” side to matches of the ON clause. Beware, however, that the type of join used will impact the data returned, so be sure to use the correct type of join for the job.

The following SQL statement performs a task similar to that shown in the left join example. However, the LEFT JOIN clause has been replaced with a RIGHT JOIN clause:

SELECT book.title, author.last_name
    FROM author 
    RIGHT JOIN book ON book.author_id = author.id;

Here, the table on the left is still the author table, and the right table is still the book table, but, this time, the results returned will include all records from the book table and only those from the author table that match the ON clause where book.author_id = author.id.

Transactions

Many database engines implement transaction blocks, which are groups of operations that are committed (or discarded) atomically, so that either all of them are applied to the database, or none. Database engines that implement transactions are often said to be ACID-compliant. That is, they offer atomicity, consistency, isolation, and durability, or ACID. This ensures that any work performed during a transaction will be applied safely to the database when it is committed. Transactions may also be undone, or rolled back, before they are committed, allowing you to implement error checking and handling before data is applied to the database.

A transaction begins with a START TRANSACTION statement. From here on, all further operations take place in a sandbox that does not affect any other user—or, indeed, the database itself—until the transaction is either completed using the COMMIT statement or undone using ROLLBACK. If any of the statements in the block fail for any reason, the entire transaction block will fail; none of the changes will be made to the database. Alternatively, the ROLLBACK statement may be used to discard any changes made since the transaction block began.

Here are two examples:

START TRANSACTION;
DELETE FROM book WHERE isbn LIKE '0655%';
UPDATE book_chapter SET chapter_number = chapter_number + 1;
ROLLBACK;

START TRANSACTION;
UPDATE book SET id = id + 1;
DELETE FROM book_chapter WHERE isbn LIKE '0433%';
COMMIT;

The first transaction block will essentially cause no changes to the database, since it ends with a rollback statement. Keep in mind that this condition usually takes place in scenarios in which multiple operations are interdependent and must all succeed in order for the transaction to be completed. Typically, this concept is illustrated with the transfer of money from one bank account to another: the transaction, in this case, isn’t complete until the money has been taken from the source account and deposited in the destination account. If, for any reason, the second part of the operation isn’t possible, the transaction is rolled back so that the money doesn’t just disappear.

Prepared Statements

For the most part, the only thing that changes in your application’s use of SQL is the data in the queries you pass along to the database system; the queries themselves almost never do. This means at the very least that your database system has to parse and compile the SQL code that you pass along every time. While this is not a large amount of overhead, it does add up—not to mention the fact that you do need to ensure that you escape all of your data properly every time.

Many modern database systems allow you to short-circuit this process by means of a technique known as a prepared statement. A prepared statement is, essentially, the template of an SQL statement that has been pre-parsed and compiled and is ready to be executed by passing it the appropriate data. Each database system implements this in a different way, but, generally speaking, the process works in three steps: first, you create the prepared statement, replacing your data with a set of markers such as question marks or named entities. Next, you load the data into the statement, and finally, you execute it. This process allows you to avoid mixing data and SQL code in the same string, which reduces the opportunity for improper escaping and, therefore, for security issues caused by malicious data.

PHP Data Objects (PDO)

The standard distribution of PHP 5.1 and greater includes PDO and the drivers for SQLite by default. However, there are many other database drivers for PDO, including Microsoft SQL Server, Firebird, MySQL, Oracle, PostgreSQL, and ODBC. Refer to the PDO documentation on the PHP website for details on how to install each of these drivers. Once the driver is installed, the process for using it is, for the most part, the same because PDO provides a unified data access layer to each of these engines. There is no longer a need for separate mysql_query() or pg_query() functions, as PDO provides a single object-oriented interface to all of these databases.

The difference comes in the SQL used for each database; each engine provides its own specialized keywords, and PDO does not provide a means for standardizing statements across database engines. Thus, when switching an application from one database to another, you will need to pay careful attention to the SQL statements issued from your application to ensure they do not contain keywords or functionality that the new database engine does not recognize.

$dsn = 'mysql:host=localhost;dbname=library';
$dbh = new PDO($dsn, 'dbuser', 'dbpass');

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);

try {
   $dsn = 'mysql:host=localhost;dbname=library';
   $dbh = new PDO($dsn, 'dbuser', 'dbpass');
   $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);
   $dbh->setAttribute(PDO::ATTR_ERRMODE,
                      PDO::ERRMODE_EXCEPTION);

   // All other database calls go here

} catch (PDOException $e) {
    echo 'Failed: ' . $e->getMessage();
}

// Filter input from $_GET
$author = "";
if (ctype_alpha($_GET['author'])) {
    $author = $_GET['author'];
}

// Escape the value of $author with quote()
$sql = 'SELECT author.*, book.* FROM author
        LEFT JOIN book ON author.id = book.author_id
        WHERE author.last_name = ' . $dbh->quote($author);

// Execute the statement and echo the results
$results = $dbh->query($sql);
foreach ($results as $row) {
    echo "{$row['title']}, {$row['last_name']}
";
}

$results = $dbh->query($sql);
$results->setFetchMode(PDO::FETCH_OBJ);
foreach ($results as $row) {
  echo "{$row->title}, {$row->last_name}
";
}

$sql = "
INSERT INTO book (isbn, title, author_id, publisher_id)
  VALUES ('0395974682', 'The Lord of the Rings', 1, 3)";

$affected = $dbh->exec($sql);
echo "Records affected: {$affected}";

$stmt = $dbh->prepare($sql);
$stmt->setFetchMode(PDO::FETCH_OBJ);
$stmt->execute();

$results = $stmt->fetchAll();
foreach ($results as $row) {
    echo "{$row->title}, {$row->last_name}
";
}

// Filter input from $_GET
$author1 = "";
if (ctype_alpha($_GET['author1'])) {
    $author1 = $_GET['author1'];
}

$author2 = "";
if (ctype_alpha($_GET['author2'])) {
    $author2 = $_GET['author2'];
}

// Set a named placeholder in the SQL statement for author
$sql = 'SELECT author.*, book.* FROM author
        LEFT JOIN book ON author.id = book.author_id
        WHERE author.last_name = :author';

$stmt = $dbh->prepare($sql);
$stmt->setFetchMode(PDO::FETCH_OBJ);

// Fetch results for the first author
$stmt->bindParam(':author', $author1);
$stmt->execute();

$results = $stmt->fetchAll();
foreach ($results as $row) {
    echo "{$row->title}, {$row->last_name}
";
}

// Fetch results for the second author
$stmt->bindParam(':author', $author2);
$stmt->execute();

$results = $stmt->fetchAll();
foreach ($results as $row) {
    echo "{$row->title}, {$row->last_name}
";
}

// Filter input from $_GET
$author1 = "";
if (ctype_alpha($_GET['author1'])) {
    $author1 = $_GET['author1'];
}

$sql = 'SELECT author.*, book.*
        FROM author
          LEFT JOIN book ON author.id = book.author_id
        WHERE author.last_name = ?';

$stmt = $dbh->prepare($sql);
$stmt->bindParam(1, $author1, PDO::PARAM_STR, 20);

try {
    $dsn = 'mysql:host=localhost;dbname=library';
    $dbh = new PDO($dsn, 'dbuser', 'dbpass');
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);
    $dbh->setAttribute(PDO::ATTR_ERRMODE,
                       PDO::ERRMODE_EXCEPTION);

    $dbh->beginTransaction();
    $dbh->exec("
        INSERT INTO book
            (isbn, title, author_id, publisher_id)
        VALUES
            ('0395974682', 'The Lord of the Rings', 1, 3)");
    $dbh->exec("INSERT INTO book (title)
                  VALUES ('Animal Farm', 3, 2)");
    $dbh->commit();
} catch (PDOException $e) {
    $dbh->rollBack();
    echo 'Failed: ' . $e->getMessage();
}

MySQL Improved Extension (mysqli)

PHP and MySQL have developed a close relationship over the years; when MySQL introduced new features such as transactions, prepared statements, and a more efficient client library API, PHP introduced the MySQL improved (or mysqli) extenstion. The mysqli extension provides access to the features of MySQL 4.1.3 and later and has been available since PHP 5. Since mysqli provides prepared statements, bound parameters, and transactions, among other advanced features, it is an important and essential database driver for any programmer using PHP 5 and MySQL 4.1.3 or later.

The mysqli extension provides both a procedural approach for those who are accustomed to using the now deprecated mysql_* functions and an object-oriented interface for those interested in and comfortable with object-oriented programming (OOP). For this reason, I will show both procedural and OOP examples when demonstrating database concepts with mysqli. This section appears similar to that of the previous PDO section, since the topics covered are the same, but all examples and discussion use mysqli to illustrate the concepts.

$mysqli = new mysqli(
    'localhost', 'dbuser', 'dbpass', 'library'
);

if (mysqli_connect_errno()) {
    echo 'Connect failed: ' . mysqli_connect_error();
    exit;
}

// All other database calls go here

$mysqli->close();

$dbh = mysqli_connect(
    'localhost', 'dbuser', 'dbpass', 'library'
);

if (!$dbh) {
    echo 'Connect failed: ' . mysqli_connect_error();
    exit;
}

// All other database calls go here

mysqli_close($dbh);

// Filter input from $_GET
$author = '';
if (ctype_alpha($_GET['author'])) {
  $author = $_GET['author'];
}

// Escape the value of $author with mysqli->real_escape_string()
$sql = 'SELECT author.*, book.* FROM author
        LEFT JOIN book ON author.id = book.author_id
        WHERE author.last_name = "'
        . $mysqli->real_escape_string($author) .'"';

// Execute the statement and echo the results
if (!$mysqli->real_query($sql)) {
    echo 'Error in query: ' . $mysqli->error;
    exit;
}

if ($result = $mysqli->store_result()) {
    while ($row = $result->fetch_assoc()) {
        echo "{$row['title']}, {$row['last_name']}
";
    }
    $result->close();
}

// Filter input from $_GET
$author = '';
if (ctype_alpha($_GET['author'])) {
  $author = $_GET['author'];
}

// Escape the value of $author with
// mysqli->real_escape_string().
// Unlike quote(), it does not add quotes around
// your string.
$sql = 'SELECT author.*, book.* FROM author
        LEFT JOIN book ON author.id = book.author_id
        WHERE author.last_name = "'
        . mysqli_real_escape_string($dbh, $author) . '"' ;

// Execute the statement and echo the results
if (!mysqli_real_query($dbh, $sql)) {
    echo 'Error in query: ' . mysqli_error();
    exit;
}

if ($result = mysqli_store_result($dbh)) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "{$row['title']}, {$row['last_name']}
";
    }
    mysqli_free_result($result);
}

// Filter input from $_GET
$author = '';
if (ctype_alpha($_GET['author'])) {
    $author = $_GET['author'];
}

// Set a named placeholder in the SQL statement for author
$sql = 'SELECT book.title FROM author
        LEFT JOIN book ON author.id = book.author_id
        WHERE author.last_name = ?';

if ($stmt = $mysqli->prepare($sql)) {
    $stmt->bind_param('s', $author);
    $stmt->execute();
    $stmt->bind_result($title);

    while ($stmt->fetch()) {
        echo "{$title}, {$author}
";
    }

    $stmt->close();
}

// Filter input from $_GET
$author = '';
if (ctype_alpha($_GET['author'])) {
    $author = $_GET['author'];
}

// Set a named placeholder in the SQL statement for author
$sql = 'SELECT book.title FROM author
        LEFT JOIN book ON author.id = book.author_id
        WHERE author.last_name = ?';

if ($stmt = mysqli_prepare($dbh, $sql)) {
    mysqli_stmt_bind_param($stmt, 's', $author);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($dbh, $title);

    while (mysqli_stmt_fetch()) {
        echo "{$title}, {$author}
";
    }

    mysqli_stmt_close($stmt);
}

$mysqli->autocommit(FALSE);

$mysqli->query(
    "INSERT INTO book
         (isbn, title, author_id, publisher_id)
     VALUES
         ('0395974682', 'The Lord of the Rings', 1, 3)"
);
$mysqli->query(
    "INSERT INTO book (title)
            VALUES ('Animal Farm', 3, 2)"
);

if (!$mysqli->commit()) {
    $mysqli->rollback();
}

mysqli_autocommit($dbh, FALSE);

mysqli_query(
    $dbh,
    "INSERT INTO book
         (isbn, title, author_id, publisher_id)
     VALUES
         ('0395974682', 'The Lord of the Rings', 1, 3)
");
mysqli_query(
    $dbh,
    "INSERT INTO book (title)
     VALUES ('Animal Farm', 3, 2)"
);

if (!mysqli_commit($dbh)) {
    mysqli_rollback($dbh);
}