Accessing Databases with Class - Extending classes
( Page 3 of 3 )

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_mysql.inc");

  // $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("db_mysql.inc");
  // This include file contains the definition of DB_Sample
  require("local.inc");

  // 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, local.inc. 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_mysql.inc"); // DB_Sql
  require("local.inc");    // 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.

>>> More PHP Articles          >>> More By Kristian Kohntopp