, ,

Working with PHP Data Objects

pdo

 

In the past we have seen a lot of technology to interact with databases. PHP is a dynamic language which is usually used in conjunction with a database. In the past we used different technologies to work with different technologies, for example SQLite would use the SQLite functions to read the database or the MySQLi extension to work with a MySQL database. As of PHP 5.1 and above we have something to consolidate everything into one extension.

PDO(PHP data objects)

PHP data objects or PDO as will be referred to from now on, is an extension built into PHP. What this brings us is an extension that works with every database and uses its own library to work with. It is lightweight and creates a database abstraction layer between your code and the database being queried.

There are some commands that are new to PDO that we will be going over in this post. I will show you these as well how to enable PDO if not enabled. We will need a few things to get started: PHP5.1 or above and a database (we will be using MySQL because it’s the only database that I’m familiar with as of this writing and I have one setup for a scanner library app I’m making), and some knowledge of SQL.

Enabling PDO

SO the first thing is making sure that PDO is enabled (which it should be by default) as well as the correct driver for your type of database. So assuming you have PHP 5.1 or higher installed lets go into our php.ini file and scroll down to to our “Dynamic Extensions” section. We want to see the following line (if not there feel free to copy and paste it over):

Windows:

extension=php_pdo.dll

Unix(Linux):

extension=pdo.so

We also need to scroll down and make sure the line for our database driver is also there:

Windows:

extension=php_pdo_mysql.dll

Unix(Linux)

extension=pdo_mysql.so

There are a bunch of other supported databases. The full list can be found at this link (http://www.php.net/manual/en/pdo.drivers.php).

Connecting to a database

Now we are going to go over connecting to our database. We will be working with a database named “scanner_lib” which will be stored on my localhost.

First thing I did was to declare some constants since these values probably won’t change anytime soon. They are the database name, the database host, the user who has read/write access to the database, and the password. One caveat that I would point out is that we would not publish these values in plain text on your server, your database files should be encrypted or at least your database username and password should be.

<?php
//database objects
define('DBHOST', '127.0.0.1');
define('DBNAME', 'scanner_lib');
define('DBUSER', 'user');
define('DBPASS', '****');

After we create our constants we create a try/catch block to house our database object creation:

//create connection to the incoming database
try {
	$db = new PDO('mysql:' . DBHOST . ';port=3307;dbname=' . DBNAME, DBUSER, DBPASS, array(
		PDO::ATTR_PERSISTENT => true
		));
} catch (PDOException $e) {	//exception for the database
	print "Error!:" . $e -> getMessage() . "\n";
	print 'There was an error’;
}
?>

We wrap our code for connecting to our database in a try/catch block should there be any issues with the database. We can use the PDOException that takes the value of $e. We use the catch that way we can throw our own exception without revealing database information. The PDOException has a function called the getMessage() function that we can pass to have the exception message from the database put on the screen.

In the try section we create a new PDO object and prefix it with the mysql: driver, after that we concatenate our constants. To make it less confusing looking without the use of constants it would look like the following:

$db = new PDO(‘mysql:127.0.0.1;port3307dbname=scanner_lib,user,****’);

I also have the ATTR_PERSISTENT => true set which makes the connection to the database a persistent connection, so the database connection stays open. I did that and loaded a closing database line at the end of each file used by my config file. I have read some articles about persistent connections but feel free to read some of them and make the judgement for yourself about whether or not to make this a practice (http://www.mysqlperformanceblog.com/2006/11/12/are-php-persistent-connections-evil/).

Adding records using PDO

Now that we are connected to our database we can start adding records into our database. I have already setup the database and added a table called “scanners” with three columns: “scanner_name, serial_number, ip_address”. So now I am going to add a scanner named “Scanner1” with a serial number of “123456” and an IP address of “192.168.50.12”, I’ve create a form that looks like this:

<form action="includes/add_scanner.php" method="POST">
		<label>Scanner Name</label>
		<input type="text" name="scanner_name" class="input">
		<div class="clearfix"></div>
		<br>
		<label>Scanner Serial Number</label>
		<input type="text" name="serial_number" class="input">
		<div class="clearfix"></div>
		<br>
		<label>IP Address</label>
		<input type="text" name="ip_address" class="input">
		<br>
		<input type="submit">
	</form>

And a php file that looks like this:

<?php
require_once('config.php');
// variables for the form and validations
if (!empty($_POST['scanner_name']) ) {
	$scanner_name = $_POST['scanner_name'];
} else {
	$scanner_name = NULL;
	print "Please enter in Scanner Name";
}
if (!empty($_POST['serial_number']) ) { 
	$serial_number = $_POST['serial_number'];
} else {
	$serial_number = NULL;
	print "Please enter in Serial Number";
}
if (!empty($_POST['ip_address']) ) {
    $ip_address = $_POST['ip_address'];
} else {
    $ip_address = NULL;
    print "Please enter IP Address";
}
// SQL Statement
    $STH = $db->prepare("INSERT INTO `scanners` (`scanner_name`, `serial_number`, `ip_address`) VALUES (:scanner_name, :serial_number, :ip_address)");
    // validation for inserting into the table
    if (!$STH) {
        echo "\nPDO::errorInfo():\n";
        print_r($db->errorInfo());
    }
    // binds the scanner objects and makes them strings
    $STH->bindParam(':scanner_name', $scanner_name,PDO::PARAM_STR); 
    $STH->bindParam(':serial_number', $serial_number,PDO::PARAM_INT);
    $STH->bindParam(':ip_address', $ip_address,PDO::PARAM_STR);
    if (!$STH->execute()) {
        echo "\nPDO::errorInfo():\n";
        print_r($db->errorInfo());
    }
    // close the databse connection
    $db = null;   
?>

This is a long one so I’ll break it down by the two sections. The first is some validation so that people enter some information. I would like to point out that this validation does not include any sanitization of database input so it may make you vulnerable to SQL injections. Unfortunately that’s beyond the scope of this tutorial and I can write one about SQL injections soon.

After that we some new stuff to look into. The first is creating a query using the PDO:

$STH = $db->prepare("INSERT INTO `scanners` (`scanner_name`, `serial_number`, `ip_address`) VALUES (:scanner_name, :serial_number, :ip_address)");

We created the database object in our config file and names it $db we then pass in the prepare() function which will take our string and turn it into a query object that will pass into the abstraction layer.

if (!$STH) {</p>
     echo "\nPDO::errorInfo():\n";</p>
     print_r($db->errorInfo());</p>
}

After that we have some validation that simply makes sure that there is some values in our statement and if not it will throw an error.

$STH->bindParam(':scanner_name', $scanner_name,PDO::PARAM_STR);</p>
   $STH->bindParam(':serial_number', $serial_number,PDO::PARAM_INT);</p>
   $STH->bindParam(':ip_address', $ip_address,PDO::PARAM_STR);</p>
   if (!$STH->execute()) {</p>
       echo "\nPDO::errorInfo():\n";</p>
       print_r($db->errorInfo());</p>
   }

We then take our values that were put into the form and convert them into the applicable parameters. We use the bindParam() function to bind the values into what we need them to be, like the “Scanner Name” is converted to a string using the PDO::PARAM_STR argument. We also do the same thing for the “Serial Number” accept we use the PDO::PARAM_INT argument. A full list of what we can use is featured here (http://www.php.net/manual/en/pdo.constants.php).

$db = null;

We set the $db variable to NULL  at the end of the script to close the database connection.

Conclusion

After going through the PDO section of php.net I learned a lot more about PDO maybe too much for just one blog post. There is much more to learn about this subject, and I suggest you check it out.

In conclusion PDO is a great way to interface with a database. It is simple, easy, and can work with a lot of different database technologies. The biggest CMS systems have adopted it, WordPress, Drupal. Also the latest iterations of MVC frameworks such as Codeigniter, and Laravel have also built in PDO. I have enjoyed it myself since it makes working with databases a breeze, I suggest you pick it up as well!

Other Sources for learning PDO

Article By: Daniel Lewis

 

 

PHP.net

 

 

Article By: Erik Wurzer

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply