Good application architecture is another key factor of an application, besides the correct data model. As the application that we are going to develop in this chapter, is relatively small, this task is not very complicated. First, we will create two pages that will list books and authors. To begin with, we should think about how these pages would look. To make our simple example small and compact, we will present a header on all pages that will contain links to the books list and the authors list. Later we will add two more pages that will allow us to add an author and a book.
Of course, we should create a common include fi le that will defi ne the common functions such as the header and footer display and the connection to the database. Our example is really small, so we will not be using any template system or even object-oriented syntax. (Indeed, these topics are beyond the scope of this book.) So, to summarize:
All common functions (including code to create the PDO connection object)
will be kept in an include fi le (called common.inc.php).
Every page will be held in a separate fi le, which includes the
common.inc.php fi le.
Every page will process data and display it (so that we have no separation
of data processing and data presentation, as one would expect from an
application designed with the model-view-controller pattern in mind).
Now that we have this small plan, we can begin with our common.inc.php fi le. As
we have just discussed, for now, it will contain the functions to display the header
and the footer, as well as the code to create the connection object. Let's keep the PDO
object in a global variable called $conn and call our header function showHeader(),
and the footer function showFooter(). Also, we will keep the database connection
string, user name, and password in this include fi le:
/**
* This is a common include file
* PDO Library Management example application
* @author Dennis Popel
*/
// DB connection string and username/password
$connStr = 'mysql:host=localhost;dbname=pdo';
$user = 'root';
$pass = 'root';
/**
* This function will render the header on every page,
* including the opening html tag,
* the head section and the opening body tag.
* It should be called before any output of the
* page itself.
* @param string $title the page title
*/
function showHeader($title)
{
?>
}
/**
* This function will 'close' the body and html
* tags opened by the showHeader() function
*/
function showFooter()
{
?>
}
// Create the connection object
$conn = new PDO($connStr, $user, $pass);
As you can see, the fi le is really simple, and you will just have to change the values of
the $user and $pass variables (on lines 9 and 10) to match your setup. For a SQLite
database, you would also have to change line 8 so that it contains an appropriate
connection string, for example:
$connStr = 'sqlite:/www/hosts/localhost/pdo.db';
Of course, you should change this to refl ect the path to where you created the SQLite
database. Also, the showHeader() function simply renders HTML code and passes
the value of the $title variable via the htmlspecialchars() function so that any
illegal characters (such as a less-than sign) are properly escaped.
Save the fi le to your web root directory. This again depends on your web server
setup. For example, it could be C:\Apache\htdocs or /var/www/html.
Now, let's create a page that lists the books. We will have to issue the query and then
iterate over the results to present each book in its own row. Later, we will create a
page that will list all the authors from the database that we created earlier. After we
fi nish this task, we will look at result set traversal.
Let's call our fi le books.php and create the code:
/**
* This page lists all the books we have
* PDO Library Management example application
* @author Dennis Popel
*/// Don't forget the include
include('common.inc.php');
// Issue the query
$q = $conn->query("SELECT * FROM books ORDER BY title");
// Display the header
showHeader('Books');
// now create the table
?>
// Now iterate over every row and display it while($r = $q->fetch(PDO::FETCH_ASSOC)) { ?> } ?>
// Display footer
showFooter();
This fi le should be saved to the directory where the common.inc.php fi le is located.
As you can see, there are more comments and HTML in the code, but there is
nothing very complicated here. As we decided earlier, the code includes the
common.inc.php fi le, then renders the page header, issues a query on the line
#10, renders the table header, and fi nally iterates over every row in the result set to
output every book's details.Just as in the fi rst chapter, we traverse the result set in a while row, using the
fetch() method of the PDOStatement object (held in the $q variable). We instruct
this method to return the rows as arrays indexed by table column names (by
specifying the PDO::FETCH_ASSOC parameter).
Inside the loop, we render the HTML of every row, inserting there the columns from
our table. After the loop quits, we close the table and display the footer.
Now it's time to test our fi rst PDO-powered application. Fire up your browser and
navigate to http://localhost/books.php. If you did everything correctly (so that
your web server and database are properly setup), you should see a table similar to
the following screenshot (although your page might look much wider, we resized the
window before taking a screenshot so that it fi ts on a printed page):
Once we have ensured that our application works with MySQL, let's see how it will
work with SQLite. To do this, we have to edit line 8 in the common.inc.php fi le so
that it contains the SQLite DSN:
$connStr = 'sqlite:/www/hosts/localhost/pdo.db';
If you did everything correctly, then after refreshing your browser, you should see
the same screen. As we discussed earlier—only one confi guration option has to be
changed when you start using another database system.
Now, let's create the code for the page that will list the authors. Create a fi le named
authors.php and place it in the directory where you saved the previous two fi les.
The code is practically identical to the books listing page:
/**
* This page lists all the authors we have
* PDO Library Management example application
* @author Dennis Popel
*/
// Don't forget the include
include('common.inc.php');
// Issue the query
$q = $conn->query("SELECT * FROM authors ORDER BY lastName,
firstName");
// Display the header
showHeader('Authors');
// now create the table
// Display footer
showFooter();
This fi le follows the same logic: include the common.inc.php fi le, and then issue the
query and traverse the result set. If you have done everything correctly, then you
simply click on the Authors link located on the books listing page in your browser to
get the following page:As you can see, the page correctly presents the three authors that we added at the
beginning of this chapter. If you want to test this with SQLite, change the line #10 to
contain the SQLite connection string. On refreshing your browser, you should see the
same page, but now based on the SQLite database contents.
Now that we have created these two pages and seen that using PDO is not
complicated, let's look at some theory before we extend the application.
