Dr. Dobb's is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.


Channels ▼
RSS

Database

DB Forms: PHP, MySQL, and PHPLIB


Aug00: DB Forms: PHP, MySQL, and PHPLIB

Darryl is a senior web programmer, and Con the CEO of Cybersource, an open source IT company based in Melbourne, Australia. They can be contacted at [email protected] and [email protected], respectively.


PHP is a server-side web scripting system. Although initially developed by Rasmus Lerdorf, it is now maintained by a world-wide group of open-source coders. PHP is powerful enough to make Perl or C coders feel at home, yet it has a syntax simple enough for Visual Basic programmers to quickly get up to speed. In this article, we'll examine PHP, as well as the MySQL database engine and PHPLIB class framework. To illustrate how you use these tools, we'll then present DB Forms, a reusable database framework for writing web applications.

PHP

The PHP (http://www.php.net/) engine can be invoked as either a CGI-callable interpreter from supported UNIX platforms (its most common OS platform is Linux) and Windows NT, or as an in-process Apache module. PHP scripts, such as ASP or Java Server Pages, are embedded within HTML files served up by the web server. In turn, the embedded PHP code is executed by the PHP interpreter and any printf()s it writes are incorporated into the outgoing HTTP stream from web server to browser. Thus, with PHP, you develop applications that can be viewed by all HTML-capable browsers.

PHP's syntax can be described as a cross between C, Perl, and shell, and by most accounts, is easier to learn than, say, ASP, Python, JSP, or Perl. It also has a wealth of functions -- over 700 and counting (see Table 1). Like many scripting languages, PHP is typeless, and variable type is decided by the interpreter at run time. PHP also offers object- oriented capabilities. Classes can incorporate variables and functions, have constructors, and can be derived and extended. PHP's arrays are powerful, and can act as associative arrays (hash tables) and standard indexed arrays. PHP arrays also hold data of different types and can be nested to arbitrary levels. Thus, you can use them to build complex data structures (similar to a C struct). There are also numerous functions for manipulating arrays and stepping through the data.

Listing One is a typical PHP script. The HTML "baggage" can be removed to reveal the "active element" in the script, which is:

<? echo "Hello World! (When you're on a good thing...)"; ?>

As with other web-scripting systems embedded in HTML, PHP code needs to be clearly denoted to the interpreter. In this case, PHP scripts are enclosed within the <? and ?> brackets, respectively.

PHP has extensive native support for most SQL databases (see Table 2), as well as for all ODBC-compliant databases. PHP also offers persistent database connections, which reduce the overhead required to undertake database work. A connection to your database needs to be opened only once; this connection is then shared between the various PHP processes that handle the incoming web requests. Since many database authentication and handshake negotiations take time, this helps ensure timely data delivery to users. Further, the feature has been designed to be drop-in replaceable for nonpersistent connections, without substantial changes to your scripts.

PHP Version 4 provides numerous improvements over previous versions, the most important being (from the perspective of most users) the speed improvement in script execution delivered by the Zend (http://www.zend.com/) engine core within PHP4. Reports of speed increases of up to an order of magnitude have been seen for some scripts. Almost full compatibility is claimed by the PHP development group for existing scripts. On NT, PHP 4 supports integration with ActiveX/COM objects.

MySQL

The database most often used with PHP is MySQL (http://www.mysql.com/), which is popular for a number of reasons. MySQL is perhaps the fastest general-purpose SQL-enabled RDBMS. It is also easy to install and maintain. Of course, it does have some idiosyncrasies, such as a lack of transactions and database triggers. However, this is not as much a problem for web-based database work, where operations are considerably more atomic, and large volume batch transactions are rare. Another missing feature is referential integrity, which, again is not a major problem if the web database is well designed.

Unlike a pure open-source project, MySQL is accompanied by a variety of open and semiopen licenses. The MySQL developers seem to like the licensing model pioneered by Ghostscript author, L. Peter Deutsch, and follow a process of releasing older code under the GPL, while newer code is under a slightly more restrictive license targeting revenue generation from commercial bundling of MySQL.

There are multiple methods and tools available to help create and maintain MySQL databases and metadata. To illustrate, we'll use the default method of the interactive command tool, mysqladmin. For instance, Example 1(a) creates a table, where the contents of example.sql would be listed in Example 1(b). Alternatively, you could run the mysql command-line client and type in the SQL directly.

MySQL has a powerful (but unusual) method of user, host, and database privileges. MySQL removes the concept of user access rights, introduces "user@host" access rights, and lets you assign SELECT, INSERT, UPDATE, DELETE, as well as bulk-insert privileges through the LOAD DATA INFILE command. You can add users in two different ways: by using GRANT statements or manipulating the MySQL grant tables directly. The preferred method is to use GRANT statements, because they are more concise and less error prone.

Example 2 shows how to create a user webuser, who has SELECT, DELETE, INSERT, and UPDATE privileges on the address table in the test database, using the password testing.

Once you can create and maintain MySQL databases, you can move on to web-based (phpMyAdmin) or GUI-based (for both UNIX and Windows) tools. But before you move to the easy path of GUI tools, consider the power and importance of being proficient with the command-line toolset. This provides you with substantial advantages, such as the ability to SSH(1) into a remotely hosted server to rebuild or repair a database, which may not have any GUI tools installed.

PHPLIB and Web- Application Development

Much application development with PHP and MySQL is done through the accumulation of numerous small, often self-contained scripts, with each script corresponding to a web page of output or data input. The unifying axle of these scripts is the data repository, and classes are used to synchronize naming and function use for clarity, and to encourage code reuse. Thus, the schema of the underlying data forms almost a protocol of discussion throughout the gaggle of scripts. One positive point about this style of development is that you can focus on small, self-contained code units that are simple to understand and maintain and which, in turn, increase the likelihood of making them solid and reliable. This model also lends itself to clean application partitioning, possibly across several programmers.

Boris Erdmann and Kristian Kuhntopp found that there were a number of requirements that all web-based apps needed that the base level of PHP3 didn't provide. They therefore set about building these components by way of an elegant class framework and called it PHPLIB (http://phplib.netuse.de/). Therein, they built provision for database independence, user and session management, and global variables, thus overcoming the limitations of the stateless HTTP protocol. PHPLIB's extension of PHP's database access is playing to one of PHP core strengths. While PHP can natively access most RDBMSs, the API calls differ between the different databases. PHPLIB provides a class-based generic wrapper for the disparate PHP DB API calls; see Listing Two.

OOH Forms

PHPLIB developers have also added other functionality such as object-oriented web-form elements. While many of us know how to create a web-based data entry form through the use of <BUTTON>- style markups, this isn't the coding style that programmers prefer. Through OOH forms, you can create web forms using object-oriented nomenclature that includes functionality such as text-entry validation based on powerful regular expression matching (see Listing Three), which is much more programmer friendly than standard HTML element syntax.

As an example of constructing a reusable framework for future PHP web apps, we present DB Forms -- a collection of PHP classes used to help build web-based user interfaces for relational databases. (The complete source code and related files for DB Forms are available from DDJ, see "Resource Center," page 5, and at ftp://ftp.cyber.com.au/ pub/cybertel/cyborg/db_forms.tar.gz.) The classes have been designed to integrate with PHPLIB and use the PHPLIB database and OOH Form classes. To use DB Forms, you need to have PHP, MySQL, and PHPLIB installed; then you simply place the DB Forms classes in the PHPLIB include path. DB Forms comes with four main classes; see Table 3. In essence, there are two sets of input and query classes -- one set of classes that uses the PHPLIB OOH forms functionality, and one that doesn't.

The class DBF_Edit generates queries to retrieve, save, and delete database records. DBF_Edit requires at least one key field that uniquely identifies a record. One field can be of type auto (increment), in which case it should be the primary key. The externally accessible instance variables for this class are in Table 4.

The class DBF_Query generates a SELECT query based on fields from one or more tables. If multiple tables are used, then foreign keys need to be supplied to generate links between tables. The query is built from PHP variables of the same name as the database fields. Any empty PHP variables are ignored in the query. If all variables are empty, then the query will not be executed. A URL can be supplied, which creates a hyperlink in the first column of the tabulated output for each record found by the query. The values of the key fields will be appended to the URL query for each record. Table 5 lists the instance methods for this class.

The source files that constitute the DB Forms framework are all .inc include files. To use these in your PHP project, enter include("dbf_edit.inc"); in the appropriate file into your source file. You can then instantiate objects from the class; see Listing Four.

Another Example

In the next example of a data edit form, several standard constructs of PHP and PHPLIB are obvious. Listing Five, the connection to the database, closely parallels the kinds of data that an ODBC connection string would require. Further down, past the function and class definitions, Listing Six instantiates a database object of the type templated above. DB Forms works with the notion of there being a primary_key, which it uses to bind data updates to. This has to be explicitly pointed out to the DBF_FormEdit class, as in Listing Seven, and from there, the fields can all be extracted from the database with code that looks like Listing Eight.

Another point to note about this particular script is that it is used to both populate the data entry form and to accept the updated data and refresh the database table. It does this through the if switch in Listing Nine.

Conclusion

We've presented the first step to building a generic web-based data-access system. It seems most plausible to take this part of a core end-user database system, couple it with a web-based table and index creation system (one exists in the phpMyAdmin project), and make a fully fledged but simple clone of Microsoft Access -- all web-based and built upon open and free tools.

As more and more focus is placed on the web-based application-service provider (ASP) model of application deployment, tools such as PHP and MySQL are going to become more popular. And since they are open source, you can be assured of their longevity. Finally, they are perhaps the easiest serious web tools to learn and are a great way into the web-app development paradigm.

DDJ

Listing One

<html>
    <head>
        <title>My First PHP Script</title>
    </head>
    <body>
        <? echo "Hello World! (When you're on a good thing...)"; ?>
    </body>
</html>  

Back to Article

Listing Two

<?php
  # Include the class to handle database connections and queries (from PHPLIB)
  include("db_mysql.inc");
  # Extend the database class and overide the connection parameters
  class DB_Example extends DB_Sql {
    var $Host     = "localhost";              
    var $Database = "test";            
    var $User     = "webuser";                  
    var $Password = "testing";                     
  }
  $db = new DB_Example;
  $db->query("SELECT * FROM address");
  while ($db->next_record()) {
    printf("%s %s<br>\n", $db->f("name"), $db->f("phone"));
  }
?>

Back to Article

Listing Three

<?php  
  require("oohforms.inc");         # include the library
  $f = new form;                   # create a form object
  # set up form elements
  $f->add_element(array("type"=>"text", "name"=>"foo", 
                        "valid_regex"=>"^[a-z]*$",
                        "valid_e"=>"Letters only", "icase"=>1, 
                        "value"=>"bar"));     
  $f->add_element(array("type"=>"submit", "name"=>"submitname"));
  if ($submitname)                 # Is there data to process?
    if ($err = $f->validate()) {   # Is the data valid?
      echo $err;                   # No; Display error
      $f->load_defaults();         # Load form with submitted data
    }  
    else {
      printf("<b>%s</b><br>\n", $foo);         # Data ok; Do something with it
    }
  $f->start("example");                        # Start displaying form
  # Show elements
  $f->show_element("foo");    
  $f->show_element("submitname", "submit");    # Show elements
  $f->finish();                     # Finish form
?>

Back to Article

Listing Four

<?php
  # Include the class to handle database connections and queries (from PHPLIB)
  include("db_mysql.inc");
  # Include the DBF_FormEdit class
  include("dbf_form.inc");
  include("dbf_form_edit.inc");

  # Extend the database class and overide the connection parameters
  class DB_Example extends DB_Sql {
    var $Host     = "localhost";              
    var $Database = "test";            
    var $User     = "webuser";                  
    var $Password = "testing";                     
  }
  # Extend the DBF edit class and overide the database class
  class DBE_Example extends DBF_Edit {
    var $database_class = "DB_Example";
  }
  # Extend the DBF form edit class
  class DBF_Example extends DBF_FormEdit {
    var $dbf_edit_class = "DBE_Example";    # Which dbf_edit class to use
  }  
  # Create an instance of the DBF edit class
  $dbf = new DBF_Example;
  $dbf->init(); # set up the DBF_FormEdit object

  # Add table and the primary key
  $dbf->add_table(array("name"=>"address", "key"=>array("name")));

  # Add fields
  $dbf->add_field(array("type"=>"text","name"=>"name"));
  $dbf->add_field(array("type"=>"text","name"=>"address"));
  $dbf->add_field(array("type"=>"text","name"=>"city"));
  $dbf->add_field(array("type"=>"text","name"=>"state"));
  $dbf->add_field(array("type"=>"text","name"=>"postcode"));
  $dbf->add_field(array("type"=>"text","name"=>"phone"));
  $dbf->add_field(array("type"=>"text","name"=>"email"));

  # Add a form element
  $dbf->add_element(array("type"=>"submit","name"=>"submit"));
  $dbf->add_element(array("type"=>"submit","name"=>"delete"));

  print("<html>\n");
  print("  <body>\n");

  if ($submit) { # Check if there was a submission 
    # Save the form data to the database table
    $dbf->save();
  }
  else if ($delete) {
    # delete the record from the database 
    $dbf->delete();
  }
  else {
    # Load form data with data from the database table
    # Requires the primary key field(s) to have valid values
    # Calls DBF_Edit load_defaults()
    $dbf->dbf_load_defaults(); 
  }
  # Calls form load_defaults(), this will load defaults for all for elements.
  $dbf->load_defaults(); 

  $dbf->start();
  $dbf->dbf_show_elements();
  $dbf->show_element("submit","Save");
  $dbf->show_element("delete","Delete");
  $dbf->finish();
  print("  </body>\n");
  print("</html>\n");
?>

Back to Article

Listing Five

# Extend the database class and overide the connection parameters
class DB_Example extends DB_Sql {
  var $Host     = "localhost";              
  var $Database = "test";            
  var $User     = "webuser";                  
  var $Password = "testing";                     
}

Back to Article

Listing Six

# Create an instance of the DBF edit class
$dbf = new DBF_Example;
$dbf->init(); # set up the DBF_FormEdit object  

Back to Article

Listing Seven

# Add table and the primary key
$dbf->add_table(array("name"=>"address", "key"=>array("name")));

Back to Article

Listing Eight

# Add fields
$dbf->add_field(array("type"=>"text","name"=>"name"));
$dbf->add_field(array("type"=>"text","name"=>"address"));
  ...

Back to Article

Listing Nine

if ($submit) { # Check if there was a submission 
  # Save the form data to the database table
  $dbf->save();
}
else if ($delete) {
  # delete the record from the database 
  $dbf->delete();
}
else {
  # Load form data with data from the database table
  # Requires the primary key field(s) to have valid values
  # Calls DBF_Edit load_defaults()
  $dbf->dbf_load_defaults(); 
}

Back to Article


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.