I recently purchased the PHP Cookbook 2ed, which is by far the best PHP book for the novice user. One of the areas in PHP that I haven't spent much time on is Database Access like getting rows from a table and binding them to an array or inserting a row and getting the new Primary key. Instead of really learning any of that I usually end up using what ever bits of code I find on the web and call it good. I have tried to read the Chapter on Databases in Programming PHP but I just can't get into reading about database access theory and what not anymore. (I have no excuse but laziness.) Anyhow a few weeks back I was staring a new project and I need to access the database and of course the first thing I did was start digging around for some code examples I had downloaded and grab the data access code. Then I ran into a snag and decide to not be lazy so I cracked the Cookbook open to the Database section. After looking at the code examples a bit I noticed they were looking the other MySQL bits of code I've seen. So I flip to the beginning of the chapter and I start reading. It was the usual bla bla bla PDO bla. PDO? Oh Great. More stuff I have neglected to learn.

As it turns out PDO is pretty cool and very easy to learn and it looks very much like PHPMySQL functions. There are a few things in PDO that I really like now that I understand it but getting to that point was a bit of a struggle.

Well enough of me droning on let's get to the code.

The Data Access Class
We'll start by building a Data Access class that all data related functions will go through to get a SQL Connection/Object.

class.dataaccess.php

Above we a class without a default constructor but we have defined 2 functions:__construct and __destruct. Both of these functions will be called automatically when the class is instantiated and disposed. When the class is first instantiated the __construct opens a MySQL DB connection and assigns it to the $conn variable. We do this so that any other function that we create that needs a DB Connection can use the already created $conn and we do not have to explicitly set a connection in each function. The same is true for the __destruct function which closes the DB Connection once all of the other functions in the class are finished processing. This is not required it's just good coding.

Now that we've got our selves the start of a Data Access Class we'll want to create some functions that access a MySQL DB.
To get started use the source code to set up a database and enter in a few records then we'll create a quick test function just to be sure everything is working. Below the __destruct function add this code:

testdb()

*Be sure to change the TableName and the ColumnName to match your DB setup.
The first line of code is doing a lot of stuff. First its creating a variable $query which will hold the results of the SQL Query, second it is accessing the connection object using $this->conn and from $conn it is accessing the PDO query function and passing in a SQL String. Once it's done with that it loops over the results of the query using the PDO fetchAll() function.

Now create an index.php page and add a reference to the class, make a new instantiation of dataaccess and then call the testdb() function
instantiate dataaccess

If every thing went as planned you should see a list of data from the referenced column but if not try adding this code to the testdb function after the foreach loop:
PDO ErrorInfo

This will print out any error information.

All set?

Accessing and Rendering Data
There a few different ways to gather data and bind it to an array depending on what you need to do with the data.

If you want to bind all of the columns in a SQL Query to an array you can do this:
function getAllUsers()

This is just like the testdb() except we are cutting out the middle man by not assigning the results to a variable.

Lets say you want to pull a random record primary key from a data table.

First note the use of the PDO prepare() function, you'll be using this a lot once you start passing in variable to SQL statements. prepare() does just what the name implies, it prepares a SQL statement for execution. Which brings us to the PDO execute() function which takes either an array of values or nothing. Last is the PDO fetch() function which has a value of PDO::FETCH_ASSOC. This tells it to return the results as a single value per column and in this case we only have one column userID. The result will be an array with one item that can be access by name $result['userID'].

Now lets pull a single row from tblUsers using a userID.

The first thing to notice is the use of '?' in the SQL statement. The '?' represents a parameter that we will be supplying in the execute() function. Like I said before execute() takes either an array of values or noting. If you supply an array the values will be executed in the order that are found in the array. For this example we have one value in the array but what if we have more? We'll get to that in a moment. First lets try this out.

Back in the index.php we'll call the getUserByID function and display the results like this:

Name: Boba Fett
Email: thefett@jabbas.com
Phone: 123-254-6666
RoleID: 5
DepartmentID: 8

Pretty sweet if you ask me.

Now lets move on to multiple parameters in a SQL statement.

For this function we are going to get all Users based on their Role or their Department they belong to.

First you can see the 2 paramaters we are supplying for the Role ID and Department ID which are defined in $query->execute(array($userRoleID,$userDeptID)). Then we assign the results to an array and use the userID for the arrays' index. Using the userID as the array index allows you to access a specific User by simply supplying the userID.

Then to access these results we can use a foreach loop to get each User in the array. Do this in the index.php:

So far we have only covered Selecting data from that database in a very limited fashion. But to keep things short and moving we'll jump into Inserting, Updating and Deleting.

Inserting Data using PDO
Inserting data is very easy and the code is very similar to Selecting data.

You start with a connection object and a SQL statement with some parameters defined. You then execute the SQL by supplying an array of values and lastly you get the newly inserted primary key from the table.
INSERT

Updating data works very much the same way with the difference being the extra primary key parameter.

Look at the prepare() function, see the WHERE roleID = ?clause and then look at the execute() function and you'll the corresponding 3rd array value of $roleID . As an alternative approach to a multi-value array you could do this:

If you've made it this far then I'm sure you can figure out how Delete works but if not here it is:

Moving On
There you have it, a Data Access Class using PDO. Now start filling it full of data functions.

In Part 2 we'll create a sub-Class of User specific functions that extends the DataAccess base Class.