DBA > Articles

Using MySQL with PDO

To read more DBA articles, visit http://dba.fyicenter.com/article/

Table of Contents

* 1. Prerequisites for Using PDO
* 2. Project Outline
* 3. The XML Document Format
* 4. Designing the Tables
o 4.1. The Classes Table
o 4.2. The Parent Interfaces Table
o 4.3. The Methods Table
o 4.4. The Parameters Table
o 4.5. The Data Members and Constants Tables
o 4.6. Creating the Tables
* 5. Creating a PDO Connection
* 6. Moving XML Data into a Database

1. Prerequisites for Using PDO

PDO is a PHP extension providing a data-access abstraction layer that can be used with a variety of databases. This gives you the flexibility of changing the database backend without having to alter your access methods. Even if you use MySQL exclusively, PDO can provide advantages; you can use the same data-access methods regardless of the MySQL version. This does away with the need for using the standard MySQL extension with older versions of MySQL and using the MySQLi extension for later versions. An additional advantage is the ability to use object-oriented code regardless of the version of MySQL.

PDO requires the object-oriented capabilities of PHP 5, so PHP 5.0 or higher is a prerequisite. The PDO extension ships with binary versions of PHP 5.1 and 5.2 and is very simple to implement on most operating systems.

Compiling PHP from source is the one sure way to customize PHP to your exact specifications and ensure that you have not only PDO but also the drivers you need. However, the package managers of most current Linux distributions make it easy to add support—if it's not already there. Under openSuSE (10.2) for instance, the php5-pdo module provides support for PDO and the php5-mysql module provides the PDO driver for MySQL. If you are already using MySQL with PHP you already have the PDO driver. You only need check that you have the PDO module loaded.

If you are running PHP 5 with Windows you'll need to do a little more work to enable the PDO extensions but the instructions given at http://www.php.net/manual/en/ref.pdo.php are simple and clear—add references to the extensions section of your Apache web server configuration file and make sure that the dll files are in the correct location. This URL also provides instructions for using PDO with PHP 5.0 regardless of your operating system.

The PDO extension is entirely object-oriented—there is no procedural version of this extension—so some knowledge of object-oriented programming is assumed.

2. Project Outline

This article examines a project that uses PDO to select from and insert into a MySQL database of PHP classes. The PDO classes are as follows:

* PDO – the PDO connection object
PDOStatement – the PDO statement object returned by the connection query method or the prepare method *
PDOException – the PDO-specific exception object *
PDORow – a representation of a result set row as an object

All PDO classes are used with the exception of the PDORow class.

XML representations of PHP classes, both internal and user-defined, are transformed into SQL INSERT statements and added to a MySQL database. This is done using the SimpleXMLElement class, one of the built-in PHP classes. Even if your grasp of XML is elementary, you'll find the SimpleXMLElement easy to understand and use.

The attached compressed file contains all the code and preserves the required directory structure. Find that file here. Any user-defined classes are found in the classes directory and XML files are found in the xml directory. The PHP scripts are found at the root directory.

3. The XML Document Format
Using PHP's reflection classes you can automate the process of converting a PHP class to XML. We won't concern ourselves here with the details of how to do this; we'll review an example file in this section and include other examples in the xml directory. The reflection classes allow you to introspect your own classes or built-in classes. They are useful tools for reverse engineering code and discovering the properties of objects at runtime. More importantly perhaps, they provide a way of generating documentation from source code.

If you haven't used the reflection classes before you can quickly get a sense of their capabilities by executing Reflection::export( new ReflectionClass( ClassName));. This static method of the Reflection class dumps all the methods, data members, and constants of the ReflectionClass object passed to it.

The following example is an XML representation of what reflection reveals about the mysqli_sql_exception class.
<?xml version="1.0"?>
<type final="" abstract="">class</type>
<origin mod_date="" num_lines="">internal</origin>
<method static="0" final="1" abstract="0" declaring_class="Exception" priority="2">
<method static="0" final="0" abstract="0" declaring_class="Exception" priority="2">
<param classtype="" defaultvalue="" byreference="" isoptional="1">message</param>
<param classtype="" defaultvalue="" byreference="" isoptional="1">code</param>
<datamember visibility="protected" static="0" defaultvalue="""">

Not all methods or data members of the mysqli_sql_exception class are included but there's enough detail here to form an idea of what any XML representation of a PHP class might look like.

Most of the tags and attributes are self explanatory. For example, by looking at the message data member you can readily determine that it is a protected, non-static data member with no default value. Details will become more apparent if you examine the DDL scripts used to create the database tables. Find these statements in the attached compressed file.

Full article...

Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/