PDO with MySQL DB Usage Example

Overview of Examples

This is just a quick example on using PDO for connecting to a MySQL
DB in your PHP code. There are just a few high level reasons why I
recommend using PDO, which I’ll go into detail in later post, but for
now at a high level PDO:

• Encourages use good Object Oriented programming
• Provides easy way to protect against MySQL injection for most common attack vectors
• Allows for easy switching of DB backend (ex. mysql to postgres) providing you use DB agnostic SQL in your queries
• For massive inserts/update of many rows, PDO leverages the power of server-side prepared statements (Driver based, Y.M.M.V)

Before being, on your server run “php -m” and make sure you have the
pdo module listed. If you don’t have command line access, create a php
file with

Browse to this page and see if PDO is listed. Then delete the file
you just created with phpinfo() in it–don’t want that rascal lying
around!

For the example, I’m going to make a table to track my books by title, nothing more.

Here’s the DB SQL table creation code for the example, I just put this into a DB named ‘testdb’ on
my localhost:

Quick note regarding designing DB tables… this will cross into a
religious battle, but here’s my advice which I use unless I’m using a
ORM or tool that specifies otherwise:

1. Always keep table names lower case
2. Avoid numbers in table names
3. Avoid punctuation in table names
4. Use the underscore to separate word like ‘access_log’
5. !!!DOGMA ALERT !!! Use the single case name for
   your objects as the table name. For this example, I have a list of
   books, so the table is ‘book’, not ‘books’. Heated arguments occur on
   this topic, but whichever way you fall, at least be consistent!!!
6. Unless you have a reason not to have a auto incrementing integer
   column to use as a primary key (if unsure, use one) name the column
   ‘id’.

Following these rules will save your karma and reputation many curses against them.

I’m going to assume for simplicity, all the code below will be written to files in the same directory.

I like to have a separate, mostly static function class to handle my
DB connections–especially if I have a number of databases and want to
keep connection info in a config file. Here’s a class for this example.

File DatabasePDO.php

I like to use this way of creating central place to manage
connections, registering them with a name, and using them as needed
without concern for creating extraneous connections to the DB.

Now for some PHP advice for new coders, once again, always obey the
rules and guidlines of any framework or library if working in one. Even
ones that require you to ignore generally accepted coding standards:

• Name your class starting with upper case letter, then all lowercase,
  using camelcase for subsequent words in name: Book, Car, CarFactory.
  Sometimes you have to make a tough call with Acronyms… in this case PDO
  is an acronym, and they always use it uppercase within their libraries,
  so I mimic that.
• Name your class files EXACTLY like your class name, with ‘.php’ added to the end.
• Do not put a closing php tag on your class files
• Learn to autoload, unlike my example…
• Learn and love getters and setters and private member
  parameters–especially valuable in weakly typed languages like php and
  also in code on hitting a DB where column names could change

Here is the Book class to support the table:

File Book.php

Finally, here is some a quick test code to play with the new object. Below, I create a brand new book,
set the title and save it to the DB. Then I pull the id from the book (added after saving) and
assign it to a temp variable. Then I unset the old book, since I don’t
need it. Then I do a fresh lookup of the book from the DB, change the
title, and save it–this time an update is done to the existing DB
record.

File Test.php

The test code should run via command line or browser.

Just a simple example, you’ll probably want a way to delete a book,
but you can add the functionality based on this example. Good luck!