Writing MySQL Scripts with PHP and PDO Posted by R@hUl | at 9:04 AM

PHP makes it easy to write scripts that access databases, enabling you to create dynamic web pages thatincorporate database content. PHP includes several specialized database-access interfaces that take theform of separate sets of functions for each database system. There is one set for MySQL, another for Inter-Base, another for PostgreSQL, and so forth. However, having a different set of functions for each databasemakes PHP scriptsnon-portable at the lexical (source code) level. For example, the function for issuing anSQL statement is named mysql_query(), ibase_query(), or pg_exec(), depending on whetheryou are using MySQL, InterBase, or PostgreSQL.In PHP 5 and up, you can avoid this problem by using the PHP Data Objects (PDO) extension. PDO sup-ports database access in an engine-independent manner based on a two-level architecture:• The top level provides an interface that consists of a set of classes and methods that is the same for alldatabase engines supported by PDO. The interface hides engine-specific details so that script writersneed not think about which set of functions to use.• The lower level consists of individual drivers. Each driver supports a particular database engine and translates between the top-level interface seen by script writers and the database-specific interface required by the engine. This provides you the flexibility of using any database for which a driver exists,without having to consider driver-specific details.This architectural approach has been used successfully with other languages—for example, to develop theDBI (Perl, Ruby), DB-API (Python), and JDBC (Java) database access interfaces. It’s also been used with PHP before: PHPLIB, MetaBase, and PEAR DB are other packages that provide a uniform database-inde-pendent interface across different engines.I hav e written elsewhere about using the PEAR DB module for writing PHP scripts that perform database processing in an engine-independent manner (see ‘‘Resources’’). This document is similar but covers PDO instead. The examples use the driver for MySQL. Preliminary Requirements PDO uses object-oriented features available only in PHP 5 and up, so you must have PHP 5 or newer installed to use PDO for writing scripts that access MySQL.PDO uses classes and objects to present an object-oriented interface. This article assumes that you are familiar with PHP’s approach to object-oriented programming. If you are not, you may wish to review the‘‘Classes and Objects’’ chapter of the PHP Manual. Writing PDO Scripts Scripts that use the PDO interface to access MySQL generally perform the following operations:1. Connect to the MySQL server by calling new PDO() to obtain a database handle object.2. Use the database handle to issue SQL statements or obtain statement handle objects.3. Use the database and statement handles to retrieve information returned by the statements.4. Disconnect from the server when the database handle is no longer needed.The next sections discuss these operations in more detail.

Download Writing MySQL Scripts with PHP and PDO.