Author: plavin
Date: 2007-08-07 15:52:05 +0200 (Tue, 07 Aug 2007)
New Revision: 7365
Log:
Outline and some details of PDO article
Added:
trunk/userguide/php-pdo.xml
Added: trunk/userguide/php-pdo.xml
===================================================================
--- trunk/userguide/php-pdo.xml (rev 0)
+++ trunk/userguide/php-pdo.xml 2007-08-07 13:52:05 UTC (rev 7365)
Changed blocks: 1, Lines Added: 330, Lines Deleted: 0; 11490 bytes
@@ -0,0 +1,330 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd">
+<chapter id="php-pdo">
+
+ <title>Using MySQL with PDO</title>
+
+ <section id="first">
+
+ <title>Outline</title>
+
+ <para>
+
+ </para>
+ </section>
+
+ <section>
+
+ <title>Prerequisites</title>
+ <para>
+ PDO ships with binary versions of PHP 5.1 and higher
+ </para>
+
+ <para>
+ If you are not compiling PHP from source most current Linux
+ distributions make it easy to using the package manager you can
+ load Ubuntu and SuSE the <literal>php2-mysql</literal> module
+ provides the PDO driver for MySQL
+ </para>
+
+ </section>
+
+
+ <section>
+
+ <title>The XML File Format</title>
+
+ <para>
+ Introspection using the Reflection classes can build an XML file
+ of a PHP class won't concern ourselves here with
+ </para>
+
+ <para>
+ Find below an example representation of what the
+ <literal>mysqli_sql_exception</literal> class might look like.
+ </para>
+
+<programlisting>
+ <?xml version="1.0"?>
+ <classobj>
+ <name>mysqli_sql_exception</name>
+ <documenting_date>2007-07-31</documenting_date>
+ <php_version>5.2.0</php_version>
+ <type final="" abstract="">class</type>
+ <origin mod_date="" num_lines="">internal</origin>
+ <parent_class>RuntimeException</parent_class>
+ <class_doccomments/>
+ <interfaces_list/>
+ <methods_list>
+ <method static="0" final="1" abstract="0" declaring_class="Exception" priority="2">
+ <method_name>__clone</method_name>
+ <method_origin>internal</method_origin>
+ <visibility>private</visibility>
+ <method_doccomment/>
+ </method>
+ <method static="0" final="0" abstract="0" declaring_class="Exception" priority="2">
+ <method_name>__construct</method_name>
+ <method_origin>internal</method_origin>
+ <visibility>public</visibility>
+ <param classtype="" defaultvalue="" byreference="" isoptional="1">message</param>
+ <param classtype="" defaultvalue="" byreference="" isoptional="1">code</param>
+ <method_doccomment/>
+ ...
+ </method>
+ </methods_list>
+ <datamembers_list>
+ <datamember visibility="protected" static="0" defaultvalue="""">
+ <datamember_name>message</datamember_name>
+ <datamember_doccomment/>
+ </datamember>
+ ...
+ </datamembers_list>
+ <constants_list/>
+ </classobj>
+</programlisting>
+
+ <para>
+ The ellipses in the preceding listing indicate missing methods and data
+ members but the The <literal>mysqli_sql_exception</literal> class
+ Most of the tags and attributes are self explanatory — for
+ example, the <literal>message</literal> data member is a protected
+ non-static data member with no default value.
+ </para>
+
+ </section>
+
+ <section>
+
+ <title>Create Database Script</title>
+
+ <para>
+ The most obvious table is the table of classes and interfaces. The
+ <literal>name</literal> field uniquely identifies each record so
+ could be used as a primary key but an AUTO_INCREMENT field is a
+ bit more convenient.
+ </para>
+
+<programlisting>
+CREATE TABLE `tblclasses` (
+ `id` INT(11) NOT NULL AUTO_INCREMENT,
+ `name` VARCHAR(70) NOT NULL default '',
+ `documenting_date` DATE default NULL,
+ `php_version` VARCHAR(10) NOT NULL default '',
+ `final` TINYINT(1) default '0',
+ `abstract` TINYINT(1) default '0',
+ `type` ENUM('class','interface') NOT NULL,
+ `origin` ENUM('internal','user-defined') NOT NULL,
+ `last_modified` DATE default NULL,
+ `num_lines` VARCHAR(10) NOT NULL default '',
+ `parent_class` VARCHAR(70) NOT NULL default '',
+ `class_doccomment` TINYTEXT,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `name` (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+</programlisting>
+
+ <para>
+ The <literal>type</literal> and <literal>origin</literal> fields
+ are naturals for ENUM data type since there are only two options.
+ The <literal>last_modified</literal>,
+ <literal>num_lines</literal>, and
+ <literal>class_doccomment</literal> fields only apply to
+ user-defined classes.
+ </para>
+
+ <para>
+ The AUTO_INCREMENT field for a unique identifier. The
+ <literal>name</literal> field will also be unique but manipulating
+ classes may be built in classes or may be user-defined
+ </para>
+
+ <para>
+ Multiple inheritance of classes isn't supported by PHP so a class
+ can inherit from only one parent class. On the other hand,
+ multiple interfaces can be implemented. hence the need for an
+ interfaces table.
+ </para>
+
+<programlisting>
+CREATE TABLE `tblparentinterfaces` (
+ `class_id` INT(11) NOT NULL default '0',
+ `interface_name` VARCHAR(70) NOT NULL default '',
+ PRIMARY KEY (`class_id`,`interface_name`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+</programlisting>
+
+ <para>
+ All that's needed is the id number of the derived class or
+ interface and the interface name.
+ </para>
+
+<programlisting>
+CREATE TABLE `tblmethods` (
+ `class_id` INT(11) NOT NULL default '0',
+ `method_name` VARCHAR(70) NOT NULL default '',
+ `method_origin` ENUM('internal','user-defined') NOT NULL,
+ `visibility` ENUM('public','private', 'protected') NOT NULL,
+ `static` TINYINT(1) NOT NULL default '0',
+ `final` TINYINT(1) NOT NULL default '0',
+ `abstract` TINYINT(1) NOT NULL default '0',
+ `declaring_class` VARCHAR(70) NOT NULL default '',
+ `priority` TINYINT(1) NOT NULL default '1',
+ `method_doccomment` TINYTEXT,
+ PRIMARY KEY (`class_id`,`method_name`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+</programlisting>
+
+ <para>
+ as in the table of classes, this table supports a
+ <literal>method_origin</literal> field The
+ <literal>declaring_class</literal> field simply
+ </para>
+
+<programlisting>
+CREATE TABLE `tblparameters` (
+ `class_id` INT(11) NOT NULL,
+ `method_name` VARCHAR(50) NOT NULL,
+ `param_name` VARCHAR(50) NOT NULL,
+ `class_type` VARCHAR(70) default '',
+ `default_value` VARCHAR(50) default '',
+ `byreference` TINYINT(1) default '0',
+ `isoptional` TINYINT(1) default '0',
+ PRIMARY KEY (`class_id`,`method_name`,`param_name`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+</programlisting>
+
+ <para>
+ Methods may have any number of parameters, hence the need for a
+ parameters table. parameters may have default values, may be
+ optional, and may be passed by value or by reference. As of PHP 5
+ parameters may also be type-hinted so a <literal>class_type</literal>
+ field is also necessary.
+ </para>
+
+<programlisting>
+CREATE TABLE `tbldatamembers` (
+ `class_id` INT(11) NOT NULL default '0',
+ `datamember_name` VARCHAR(50) NOT NULL default '',
+ `visibility` ENUM('public','private', 'protected') NOT NULL,
+ `isstatic` TINYINT(1) NOT NULL default '0',
+ `default_value` VARCHAR(50) NOT NULL default '',
+ `datamember_doccomment` TINYTEXT NOT NULL,
+ PRIMARY KEY (`class_id`,`datamember_name`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+ CREATE TABLE `tblconstants` (
+ `class_id` INT(11) NOT NULL default '0',
+ `constant_name` VARCHAR(50) NOT NULL default '',
+ `constant_value` VARCHAR(75) NOT NULL default '',
+ PRIMARY KEY (`class_id`,`constant_name`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='constants;';
+ </programlisting>
+
+ <para>
+ Unlike data members, any comments that accompany constants cannot
+ be retrieved so there is no <literal>doccomment</literal> field for the
+ table of constants.
+ </para>
+
+ <para>
+ These are all the tables we need to convert an XML version of a
+ PHP class into a database representation. Next we'll see how this
+ is done using PDO.
+ </para>
+
+ </section>
+
+ <section>
+
+ <title>Creating a PDO Connection</title>
+
+ <para>
+ want to create a drop-down list box of existing XML versions of
+ PHP classes that are not already included in our database. This
+ involves loading an array with the names of XML files and
+ comparing this to filenames already contained in our database.
+ </para>
+
+ <para>
+ connection parameters are contained in the <filename>connection.php</filename>
+ file
+ The names of existing XML class files are copied from the
+ <filename>xml</filename> directory into an array. This array is compared to
+ the classes already contained in the database to create drop-down
+ list of files that aren't yet included.
+ </para>
+
+<programlisting>
+<form name= "select_class" action="adddbrecord.php" method="get" style="padding:5px;">
+ <select style="max-width: 180px; min-width: 180px;" name = "xmlfilename" >
+ <?php
+ //get files in xml dir
+ //select only what not in db use array_diff
+ //auto load user-defined classes
+ function __autoload($class){
+ include 'classes/'.$class.'.php';
+ }
+ include 'connection.php';
+ $di = new DirectoryItems('xml');
+ $di->filter('xml');
+ $arr = $di->getFilearray();
+ //now get array of names from database
+ try{
+ $db = new PDO("mysql:host=$host;dbname=$database",
+ $username, $password);
+ $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+ $sql = "SELECT name FROM tblclasses";
+ $stmt = $db->query($sql);
+ //return a simple array of all the names
+ $arr2 = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
+ }catch(PDOException $e){
+ echo $e;
+ }
+ $not_in_db = array_diff( $arr, $arr2);
+ natcasesort($not_in_db);
+ foreach($not_in_db as $v){
+ echo "<option>$v</option>\n";
+ }
+ echo "</select><br /><br />";
+ ?>
+ <input type="submit" value="submit" />
+</form>
+</programlisting>
+
+ <para>
+ Creating the connection is as simple as specifying the database type and
+ the host name and database credentials.
+ </para>
+
+ <para>
+ The <literal>setAttribute</literal> method of the PDO class
+ lets you determine whether you want to
+ raise errors or throw exceptions. Throwing exceptions
+ seems to be the better choice since you can then enclose all your
+ code in a try block and deal with errors
+ in one place. The line
+ <literal>$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);</literal>
+ does just this. This flexibility comes with a price
+ The actual creation of the database connection can't throw an
+ exception because a connection is required in order to set ...
+ </para>
+
+ <para>
+ retrieving the entire result set into an array
+ </para>
+
+ </section>
+
+ <section>
+ <title>Moving XML Data into a Database</title>
+
+ <para>
+
+ </para>
+
+ </section>
+
+
+</chapter>
+<!-- END PHP-PDO CHAPTER -->
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r7365 - trunk/userguide | plavin | 7 Aug |
