Knowledge

PHP Fundamentals: PDO

Published on July 18, 2013 under How-To, Web Development

PDO

Last year, I helped get your feet wet in PHP, especially in regards to adding and editing data in a MySQL database. The code is solid, but before you go using it as a template for your own projects, I unfortunately have to tell you one piece of it has been depreciated by the PHP community.

This is nothing new, and it’s something to expect when you get into writing code. With the past examples I’ve created, the varied MySQL codes used to connect back and forth to the database have been depreciated in favor of the new MySQLi or PHP Data Objects (PDO). I made PDO my weapon of choice, and I’m going to show you how to use it.

Why PDO?

My choice to use PDO over MySQLi really came about when I found code examples that matched my own style of PHP coding. That was my start, but I’ve found that PDO suits me better also because of its flexibility, ease of use, and the fact I can use it to connect to almost any kind of database...not just MySQL.

Now there are advantages to MySQLi. Speed is one of them if you’re planning on a very complex website and feel PDO causes things to load too slow. However, for my work, I’m sticking with PDO, especially since it was created by the same community that oversees the PHP API development.

Reconnecting to your database

With the new standards being implemented, old functions like mysql_connect and mysql_select_db have been depreciated. We used to connect to a MySQL database like this:

$Host = "Host_Address";
$User = "Database_User";
$Password = "Database_Password";
$Link = mysql_connect ($Host, $User, $Password);
mysql_select_db('Database_Name', $Link);

With PDO, you’re creating an object and connecting now like this:

$User = "Database_User";
$Password = "Database_Password";
$Link = new PDO('mysql:host=Host_Address;dbname=Database_Name', $User, $Password);

It’s not much of a different logic outside of using less code. PDO lets you create one object to handle the whole database connection. As you can see, it’s really feeding all the variables at once into the object. I like to set up the user name and password as outside variables, but the combo of the host address and database name can’t be brought in that way. So if you tried this (and I have tried):

$Host = "Host_Address";
$DBName = “Database_Name”;
$User = "Database_User";
$Password = "Database_Password";
$Link = new PDO('mysql:host=$Host;dbname=$DBName’, $User, $Password);

You’ll have problems. You won’t even get an error message, but you just won’t connect and thus see empty pages when you try to pull data.

Pulling data from the dabatase

Now that we’ve gotten connected, there is also a change to how we pull data. I’m still sticking with my methodology of running a Query and then filtering the results through a while loop, like this:

$Query = "SELECT * from phpDemo";
$Result = mysql_query ($Query, $Link);
while ($Row = mysql_fetch_array ($Result)) {
}

This will soon be made obsolete, as the functions mysql_query and mysql_fetch_array are being depreciated. With PDO we now use query and fetch functions:

$Query = "SELECT * from phpDemo";
$Result = $Link->query($Query);
while($Row = $Result->fetch(PDO::FETCH_ASSOC)) {
}

The query() functions runs the MySQL statement similar to how we did with mysql_query. The reason for the removal of the “mysql_” is that PDO was built to work on a variety types of databases, including MySQL.

The arrow operator (->) is a means to access an object, like the one we use here. In laymen’s terms, the $Link variable is your PDO object we created in the previous section, and now you’re feeding it into the query function along with a MySQL statement ($Query). You then end up with $Result.

Now we can’t just pull $Result and just paste it down. In our example above it’s literally pulling all the data and saving it as $Result. In order to churn things down to separate items we can use, we use the while loop as I did before, but with the fetch() function.

The fetch() function will go into $Result, pull down, and organize the data. The while loop is how we go through each row of data and pull the results of that specific row before going on to the next. Again, the arrow operator is used to send the full $Result into the fetch() function.

PDO::FETCH_ASSOC is actually a parameter where you specify how you want data fetched. Depending on how you want results, you would specify your style there. Check out the PHP manual entry on fetch() to see what you can do. For most data pulls, PDO::FETCH_ASSOC is your choice.

Adding, editing, or deleting data

Thankfully, there isn’t much change at all to how I had to writing your add, edit, or deletion codes. Again, the problem is mainly in that the mysql_query function is being depreciated:

$Result = mysql_query("Your MySQL statement");

So we simply replace it with the query() function:

$Result = $Link->query("Your MySQL statement");

It’s that simple. Here’s the example from the previous article on adding to the database with the change:

$Result = $Link->query("INSERT into dbTableName (dbVar01, dbVar02, dbVar03) values ('$phpVar01', '$phpVar02', '$phpVar03')");

Counting rows

This might not seem important to your needs, or you might have a different means to go about this, but I often will count rows of results to use in different scenarios, like when I have the PHP code cycle through a database table and either edit or delete rows. In the end, you need a count of the rows to tell the for loop where to stop.

In the past, I used the mysql_num_rows function:

$num_rows = mysql_num_rows($Result);

However, it’s been depreciated, so the fetchColumn() function will take care of your needs:

$num_rows = $Result->fetchColumn();

You’ll also need to set up a separate database query. In the past, I had the one query that pulled the data to display. The old mysql_num_rows function would take care of the count. With this new system, a separate query with a COUNT() is needed, like this:

SELECT COUNT(*) from phpDemo

Then as you’ll see in the included example, I run a query to pull and display the data.

MOST IMPORTANTLY...

Just like you did in the past with mysql_close, you still need to close up your database connection each time you set up a page. This is a very important security function so when you fully assemble your final HTML through PHP, you’re not leaving your database open for hackers to dive into.

Since mysql_close has been depreciated, we instead close the database connection like this:

$Link = null;

You simply erase the connection variables from your $Link variable. If you’re putting your connection variables at the top of a page, they will be reopened when a user encounters the page, but then be closed once the page is assembled. It’s just smart security for your back-end.

So what now?

Well, I’ve given you the basics of PDO, it’s up to you how you want to use them, or if you even want to research how to use PDO to match your own style of coding. If PDO isn’t for you, then try MySQLi, but I forewarn you this is only for MySQL databases, and you never know when a job will pull data from another place that isn’t MySQL.

Now all this doesn’t mean you have to run out and change every website you have built. The PHP community isn’t going to pull the plug on those mysql functions for a while, but it is suggested you code all new projects using PDO or MySQLi. If you use third-party setups like Wordpress, chances are they already updated this. I wouldn’t be in a rush to change it all, but i would not do any new websites in the old standards. This website is actually still in mysql functions and XHTML...but an update to PDO and HTML5 is coming.

As an added bonus, I’ve taken all the example files from before and fixed them in the new PDO standard. Try them out and please ask questions if you have them.

Download example files Download the example files from this article

Tags: php fundamentals, php, pdo, mysql

comments powered by Disqus