Accessing Databases with Class

PHP offers a large number of native interfaces to database servers from different vendors. All of them are accessed in a similar, but subtly different way, which has its reason in the differences of the underlying C API and in the historic development of PHP’s database support. In this article, you will learn how to create an easier interface to SQL databases by employing the class and object features of PHP.

If you are starting to create a library of reuseable PHP functions, you will soon encounter some typical problems. For example, you will experience name clashes if you start mixing your own components with that of other developers: sooner or later some foreign function will have the same name as one of yours or will use a global variable that you are using, too.

But you may even experience problems if you are using only selfmade components. Imagine for example a set of functions that is manipulating a database link. Example 1 shows such a set of functions which shares a common variable named $Link_ID.

Example 1: A hypothetical set of functions for accessing a database

<?php $Link_ID = 0; // ID of current database link $Query_ID = 0; // ID of currently active query $error = 0; // current database error state function connect() { … } function query() { … } function next_record() { … } function num_rows() { … } ?>
If you define an include with these functions, you could easily write the query() function in a way that it checks for a valid $Link_ID. If there is no valid link, query() should call connect() to establish that link. query() would update the $Query_ID and next_record() would automatically reference that variable to work with it. To make this work, all of these functions would have to share common variables. Because PHP does not know pointer or reference variable types, it is necessary for these variables to be global.

This is going to be a problem as soon as you have a page that needs two concurrently active queries, because these queries would fight for the global variables. If you had PHP pointer or reference types, you could call connect(), query() and next_record() with the with references to the appropriate variables. But in this case you would have gained nothing, because you would be back to dealing with $Link_IDs and $Query_IDs yourself.

PHP offers a different approach to solve this problem: You may group a number of variables and functions together into a package and name that package. The package itself uses no names in your global namespace. You may then create copies of the packages and insert them under any variable name into your global namespace, much like you can mount disks anywhere in a directory hierarchy. Creating a package of variables and functions is called “declaring a class” in PHP and mounting a package copy in your namespace is called “creating an object as an instance of a class”. Example 2 shows how a class is defined using the “class” keyword and how objects are created using the “new” operator. Compare this to the definition shown in Example 1 and see how they match one to one.

Example 2: Definition of a class DB_MiniSQL with call properties of Example 1. Creation of two object instances $db1 and $db2 of that class.

<?php class DB_MiniSQL { var $Link_ID = 0; // ID of current database link var $Query_ID = 0; // ID of currently active query var $error = 0; // current database error state function connect() { … } function query() { … } function next_record() { … } function num_rows() { … } } $db1 = new DB_MiniSQL; $db2 = new DB_MiniSQL; ?>

A declaration of a class does not use names in the global variable namespace - the class declaration only establishes a plan how to build DB_MiniSQL variables, but does not actually build such variables. PHP now knows what makes up a DB_MiniSQL object, if it were asked to make one.

We ask PHP to make such objects using the “new” operator and name them by assigning them to a variable. We can have multiple objects of the same type under different names – $db1 and $db2 in our example. Unlike the situation in Example 1, this does not lead to name clashes, because both variables differ in their “pathnames” (Remember the disk mountpoint analogy!): $db1->Link_ID and $db2->Link_ID are obviously different variables. With function calls it is the same: $db1->query() sends a query via one link, $db2->query() via the other link.

For library developers this is an important feature, since it allows us to encapsulate the definition of our functionality in a non-intrusive way. We leave it to the user of our functions to decide how many copies of them are needed and under what name. For users of such a library it is easy to handle this: They just have to get used to choose an appropriate name for the imported functions (for example by writing “$db1 = new DB_MiniSQL”) and then always use the functions under that name prefix (for example by writing “$db1->query()”).

But that’s the view from the outside, from the users side of the code. From the inside it is a little bit different. Why? Imagine the query() function wanted to check the value of $Link_ID. It would have to know its own name, because it would have to decide whether to access $db1->Link_ID or $db2->Link_ID or another, completely different object. That would be quite inconvenient to code. All local object variables and functions are available under the prefix $this instead, independent of the actual name of the object. So in our case, query() could simply access its own Link_ID as $this->Link_ID and call its own connect() function as $this->connect(). Note that the variable name is “this->Link_ID” and thus it is written as “$this->Link_ID”, not as “$this->$Link_ID”. This is a very common beginners error. {mospagebreak title=A database access class as example} We will be coding a class DB_Sql for access to a MySQL database as an example. [1] Our class shall have variables $Host, $Database, $User and $Password, which define the server to connect and contain all necessary information to log on to the database server. The result of that logon will be a $Link_ID, which must be saved by the class, too.

Queries to the database will either produce a result set referenced by a $Query_ID or error codes, which will be kept in $Error and $Errno for the error text and number respectively. While we read the result set of the query, we will keep the current row in a hash named $Record and we will keep the current row number in $Row. To do all this, our class will have to create the variables show in Example 3 – the functions working with these variables are still missing, though.

Example 3: Definition and explaination of all variables used in DB_Sql.

class DB_Sql { var $Host = “”; // Hostname of our MySQL server. var $Database = “”; // Logical database name on that server. var $User = “”; // User und Password for login. var $Password = “”; var $Link_ID = 0; // Result of mysql_connect(). var $Query_ID = 0; // Result of most recent mysql_query(). var $Record = array(); // current mysql_fetch_array()-result. var $Row; // current row number. var $Errno = 0; // error state of query… var $Error = “”; // insert functions here. }

To be able to work with this class you will have to add at least code that establishes a database connection. This can fail, if the server is not reachable, the database is not present or username and password are wrong. The class must have a way to signal an error and stop the program. We define a function halt(), which prints an error message and stops the program. We also define a function connect(), which tries to get hold of a valid $Link_ID. The code is shown in Example 4.

Example 4: The functions halt() and connect() are to be added to the class DB_Sql.

function halt($msg) { printf(“</td></tr></table><b> Database error:</b> %s<br>n”, $msg); printf(“<b>MySQL Error</b>: %s (%s)<br>n”, $this->Errno, $this->Error); die(“Session halted.”); } function connect() { if ( 0 == $this->Link_ID ) { $this->Link_ID=mysql_connect($this->Host, $this->User, $this->Password); if (!$this->Link_ID) { $this->halt(“Link-ID == false, connect failed”); } if (!mysql_query(sprintf(“use %s”,$this->Database),$this->Link_ID)) { $this->halt(“cannot use database “.$this->Database); } } }
The first thing halt() does it to emit a bunch of closing tags. This is the futile attempt to close an HTML table that is still open, if the error occurs within such a table. Many browsers (Netscape for instance) do not render the text of the error message, if the error occurs within a table. You will only see the text of the error message in the HTML source view of the page in such cases. The error message is shown along with the current values of $Errno and $Error. To prevent further damage, the program is then stopped.

The connect() function tests for a valid link. If this is not the case, we try to establish such a link using the values of $Host, $User and $Password. If this fails, we signal an error and stop processing, otherwise we try to change the current database to $Database employing a MySQL “use” command.{mospagebreak title=Extending classes} Our class is now useable, albeit not productively. We are able to establish a database link, but without query() and next_record() functions we are unable to make use of it. We are going to use the class anyway, to show how to set it up for a production environment. Example 5 shows a workable, but inconvenient method how you could deploy the class.

Example 5: A non-recommended method to configure and deploy DB_Sql.

<?php // The include file contains the definition of DB_Sql. require(“”); // $db is our database object. $db = new DB_Sql; // Overwrite the connection parameters as needed. $db->Host = “localhost”; $db->User = “kris”; $db->Password = “” $db->Database = “sampleserv”; // Try to connect to the database server process. $db->connect(); ?>

This is not a recommended method to configure a class for use, because after creation of the object you have to set up all variables within that object manually and you have to do it over and over on each page where you are using it. It would be much nicer if we were able to define a class that is just like DB_Sql, but with different connect parameters. In fact we can easily do this: We can extend any given class and base the definition of a new class on any single existing class. Example 6 shows the definition of a class DB_Sample, which performs exactly the same connect as Example 5. Example 6b shows how to use this class.

Example 6: Definition of a new class DB_Sample, based on DB_Sql

// DB_Sample is just like DB_Sql, only different.. :-) class DB_Sample extends DB_Sql { var $Host = “localhost”; var $User = “kris”; var $Password = “”; var $Database = “sampleserv”; }

Example 6b: Using DB_Sample.

// This include file contains the definiton of DB_Sql require(“”); // This include file contains the definition of DB_Sample require(“”); // Create a database connection object $db = new DB_Sample; // Connection to database… $db->connect();

DB_Sample is not empty, but contains exactly the same variables and functions as DB_Sql, although these are not written down explicitly in the class definition. The magic is in the class definition: DB_Sample extends DB_Sql, that is, DB_Sample starts as a simple copy of DB_Sql. Within the class body of DB_Sample certain definitions of DB_Sql are overwritten, specifically we redefine the database connection parameters.

On use of DB_Sample as shown in Example 6b, the database connection will be created using these redefined parameters. Unlike Example 5 we do not have to mention these parameters on each page, but DB_Sample “automatically” knows the appropriate parameters and does the right thing. If we had to change the connection parameters, we could do so by editing a single file, This is very convenient, especially in larger projects. {mospagebreak title=Queries and query results} Example 7: Adding the functions query(), next_record() and seek() to DB_Sql.

function query($Query_String) { $this->connect(); # printf(“Debug: query = %s<br>n”, $Query_String); $this->Query_ID = mysql_query($Query_String,$this->Link_ID); $this->Row = 0; $this->Errno = mysql_errno(); $this->Error = mysql_error(); if (!$this->Query_ID) { $this->halt(“Invalid SQL: “.$Query_String); } return $this->Query_ID; } function next_record() { $this->Record = mysql_fetch_array($this->Query_ID); $this->Row += 1; $this->Errno = mysql_errno(); $this->Error = mysql_error(); $stat = is_array($this->Record); if (!$stat) { mysql_free_result($this->Query_ID); $this->Query_ID = 0; } return $stat; } function seek($pos) { $status = mysql_data_seek($this->Query_ID, $pos); if ($status) $this->Row = $pos; return; }

Example 7 adds three functions to our DB_Sql class which make the class actually useful: Finally we are able to make use of the database link for sending queries to the database and retrieving the results. For this purpose, query() calls connect() internally to create the database link. This saves you a manual call to connect if you are using the database class later in your pages.

If you activate the disabled printf() statement within query(), you get a list of all queries on a page as they are made on the page. This is very useful for debugging your SQL and to get a feeling how expensive the creation of a certain page actually is.

When you send a query, a new $Query_ID is being generated and the current row number is reset to zero. After that we check the error state to see if the query was legal. If not, we raise an error and halt the program. If the query was legal, we return the query id to the caller.

The next_record() function can be used to retrieve the query result. The function reads the current result row, increments the row counter and checks for errors. If the result set has been read completely, we call mysql_free_result() to save application memory. next_record() returns “true” as long as there are still result records so that you may use the function as the condition of a while()-loop.

Using seek() you may move within the current result set and read a single result multiple times (unless it has been freed) or skip certain records at the beginning of the result set. Example 8 shows how to use query() and next_record() to get data from a table. Example 8: Query to the table ad_customers within the database sampleserv.

<?php require(“”); // DB_Sql require(“”); // DB_Sample $db = new DB_Sample; $query = “select name, graphics, link, desc from ad_customers”; $db->query($query); ?> <html> <body bgcolor=”#ffffff”> <table border=1 bgcolor=”#eeeeee”> <tr> <th>ID</th> <th>Graphics</th> <th>Link</th> <th>Desc</th> </tr> <?php while($db->next_record()): ?> <tr> <td><?php print $db->Record["name"] ?></td> <td><?php print $db->Record["graphics"] ?></td> <td><?php print $db->Record["link"] ?></td> <td><?php print $db->Record["desc"] ?></td> </tr> <?php endwhile ?> </table> </body> </html>

Example 8b: Definition of table ad_customers.

CREATE TABLE ad_customers ( id int(11) DEFAULT ’0′ NOT NULL auto_increment, name varchar(127) DEFAULT ” NOT NULL, graphics varchar(127) DEFAULT ” NOT NULL, link varchar(127) DEFAULT ” NOT NULL, desc varchar(127) DEFAULT ” NOT NULL, PRIMARY KEY (id), KEY name (name), ); CREATE TABLE banner_rotate ( pos int(11) DEFAULT ’0′ NOT NULL, );

Many webservers keep rotating banner ads at the top or bottom of their pages. These banners are present as GIF images with known path names. In our sample database we keep a table named ad_customers, which lists information about each banner. We keep a banner name, the pathname to the GIF image on disk, a link target that is to be activated when the banner is clicked and a description text for the images alt-attribute.

Example 8 shows, how to read this table using the DB_Sample class. We are generating an HTML table with all banner names and related data. Example 8b shows the database table definitions involved. The second table, banner_rotate, contains just a single row with a single column with the currently active ad banner number. The rotation program uses this information to control the banner rotation.

The actual banner rotation program (Example 9) is just a single function banner_rotate(), which does nothing more than incrementing the pos counter from the banner_rotate table and produces the appropriate image tag. The locking shown in that function is specific to MySQL (MySQL does not do proper transactions).

The function is pretty linear: It locks the banner_rotate table and updates the counter using an SQL update statement. After that it uses a SQL select statement to read that counter value and unlocks the table. Using the counter value corrected modulo the number of actual ad customers the appropriate customer data from the ad_customers table is selected and an image tag is created which is embedded into a link. We do not directly jump into the customers presentation using this link, but we refer the user to another local program which registers the click and additional data about the users browser. It is that second program which generates a Location header to redirect the user to the final external destination. This is the only way to measure the efficiency of a banner and to get provable data for the customer. Example 9: Function banner_rotate() to rotate banner ads.

<?php function banner_rotate() { global $db; // Assumes that a global object with that name exists. $max_ads = 4; // CONFIGURE ME! $db->query(“lock tables banner_rotate”); // Set lock. $db->query(“update banner_rotate set pos = pos + 1″); // Increment counter. $db->query(“select pos from banner_rotate”); // Read counter. $db->next_record(); $pos = $db->Record["pos"]; $db->query(“unlock tables”); // Drop lock. // Find matching customer (mod $max_ads). $query = sprintf(“select * from ad_customers where id = ‘%s’”, $pos % $max_ads); $db->query($query); $db->next_record(); // Link and Image generation printf(“<a href=”jump.php3?name=%s”> <img src=”%s” alt=”%s” width=468 height=60 border=0></a>”, $this->Record["name"], $this->Record["graphics"], $this->Record["desc"]); } ?>

The jump.php3 script is not shown. It receives a parameter name identifying the clicked banner. Using that parameter the script can extract the link information from ad_customers and create a Location header to redirect the users browser to the final location. Also, it does record the click in another table named banner. Example 10: More functions for DB_Sql

<?php function num_rows() { return mysql_num_rows($this->Query_ID); } function num_fields() { return mysql_num_fields($this->Query_ID); } function f($Name) { return $this->Record[$Name]; } function p($Name) { print $this->Record[$Name]; } function affected_rows() { return @mysql_affected_rows($this->Link_ID); } ?>

To complete DB_Sql, we add the functions shown in Example 10. These are intended to ease access to query results: num_rows() and num_fields() return the width and height of the result set. The functions f() and p() are useful to access single result set values. And affected_rows() can be used to test the success of SQL insert or update statments.

The class DB_Sql is a basic building part of PHPLIB The version of DB_Sql from PHPLIB contains some additional functions which are not relevant for this tutorial.

Google+ Comments

Google+ Comments