PHP Hacks

Hack 37. Generate CRUD Database Code

Automatically generate the code to create, read, update, and delete (CRUD) records from your database tables.

This book presents several hacks that will help you speed up your database development by generating the required PHP and SQL code. In this hack, I show you how to build a generator that will create PHP 4 (or 5) classes that wrap database records. With these classes, you will be able to create, read, update, and delete individual records on any table, without spending lots of time writing the database code yourself.

Figure 5-4 shows the flow from the schema file into the generator, which in turn creates the output PHP code. I've rendered the output code as dashes because it's temporary and should never be altered manually.

Figure 5-4. The flow through the generator

5.5.1. The Code

Save the XML representing a database schema (shown in Example 5-14) as schema.xml.

Example 5-14. An XML document that maps to the database schema

<schema>
  <table name="book">
	<field name="id" type="int" primary-key="true" />
	<field name="title" type="text" />
	<field name="publisher_id" type="int" />
	<field name="author_id" type="int" />
  </table>
  <table name="publisher">
    <field name="id" type="int" primary-key="true" />
	<field name="name" type="text" />
  </table>
  <table name="author">
    <field name="id" type="int" primary-key="true" />
    <field name="name" type="text" />
  </table>
</schema>

Example 5-15 shows the generation code; I saved this script as gen.php.

Example 5-15. PHP that handles database code generation

<?php
$tables = array();

function start_element( $parser, $name, $attribs )
{
  global $tables;
  if ( $name == "TABLE" )
  {
    $table = array();
	$fields = array();
		$table['name'] = $attribs['NAME'];
	$table['fields'] = array();
		$tables []= $table;
  }
  if ( $name == "FIELD" )
  {
	$field = array();
	$field['name'] = $attribs['NAME'];
	$field['type'] = $attribs['TYPE'];
	$field['pk'] = ( $attribs['PRIMARY-KEY'] == "true" ) ? 1 : 0;
	$tables[count($tables)-1]['fields'] []= $field;
  }
}
function end_element( $parser, $name ) { }
	$parser = xml_parser_create();
xml_set_element_handler($parser, "start_element", "end_element" );
while( !feof( STDIN ) ) {
  $text = fgets( STDIN );
  xml_parse( $parser, $text );
}
xml_parser_free( $parser );

ob_start();

echo( "<?php\n" );
?>
require_once( "dbwrap.php" );

<?php

foreach( $tables as $table ) {
  $pk = null;
  $updsets = array();
  $updfields = array();
  $insfields = array();

  $insvalues = array();
  $insvars = array();

  foreach( $table['fields'] as $field ) {
		$insfields []= $field['name'];
	if ( $field['pk'] )
		{
	  $pk = $field['name'];
		  $insvalues []= 0;
	    }
		else
		{
	      $updsets []= $field['name']."=?";
		  $updfields []= '$this->'.$field['name'];
			
		  $insvalues []= '?';
		  $insvars []= '$this->'.$field['name'];
		}
  }

  $insvars = join( $insvars, ", " );
  $insvalues = join( $insvalues, ", " );
  $insfields = join( $insfields, ", " );
  $updfields []= '$this->'.$pk;
  $updfields = join( $updfields, ", " );
  $updsets = join( $updsets, ", " );
?>
class <?php echo( ucfirst( $table['name'] ) ) ?>
{
<?php
  foreach( $table['fields'] as $field ) {
?>
  var $<?php echo( $field['name'] ); ?>; <?php }
?>

  function <?php echo( ucfirst( $table['name'] ) ) ?>()
  {
    $this->id = null;
  }

  function load($id)
  {
	 $data = selectOne( "SELECT * FROM <?php echo( $table['name'] ) ?> WHERE <?php
echo( $pk ); ?> = ?", array( $id ) );
<?php
	 foreach( $table['fields'] as $field ) {
?>
	   $this-><?php echo( $field['name'] ); ?> = $data['<?php echo( $field['name']
); ?>'];

<?php
}
?>
  }

<?php
  foreach( $table['fields'] as $field ) {
?>
  function get_<?php echo( $field['name'] ) ?>( ) { return $this-><?php echo(
    $field['name'] ) ?>; }

  function set_<?php echo( $field['name'] ) ?>( $val ) { $this-><?php echo(
    $field['name'] ) ?> = $val; }

<?php
}
?>
  function update( )
  {
    if ( $this->id != null ) { $this->updateRecord( ); }
	else { $this->insertRecord( ); }
  }

  function insertRecord( )
  {
    return executeCommand( "INSERT INTO <?php echo( $table['name'] ) ?> ( <?php
		echo($insfields); ?> ) VALUES ( <?php echo($insvalues); ?> )",
	  array( <?php echo( $insvars ); ?> ) );
  }

  function updateRecord( )
  {	
    return executeCommand( "UPDATE <?php echo( $table['name'] ) ?> SET <?php
	  echo($updsets); ?> WHERE <?php echo( $pk ); ?>=?",
	array( <?php echo( $updfields ); ?> ) );
  }
	  
  function deleteRecord( $id )
  {
    return executeCommand( "DELETE FROM <?php echo( $table['name'] ) ?> WHERE
		<?php echo( $pk ); ?>=?", array( $id ) );
  }
}
	
<?php }
echo( "?>" );

	$php = ob_get_clean( );

$fh = fopen( "mydb.php", "w" );
fwrite( $fh, $php );
fclose( $fh );
?>

Believe it or not, though, there's more code to write; dbwrap.php (shown in Example 5-16) handles connecting to a specific database. You'll want to have one of these scripts for each database you connect to.

Example 5-16. A script that handles database-specific details

<?php
require_once( "DB.php" );
$dsn = 'mysql://root:password@localhost/books';
$db =& DB::Connect( $dsn, array( ) );
if (PEAR::isError($db)) { die($db->getMessage( )); }

function selectOne( $sql, $args )
{
  global $db;
  $res = $db->query( $sql, $args );
  $res->fetchInto($row, DB_FETCHMODE_ASSOC);
  return $row;
}

function selectBlock( $sql, $args )
{
  global $db;
  $res = $db->query( $sql, $args );
  $rows = array( );
  while( $res->fetchInto($row, DB_FETCHMODE_ASSOC) ) { $rows []= $row; }
  return $rows;
}

function executeCommand( $sql, $args )
{
  global $db;
  $sth = $db->prepare( $sql );
  return $db->execute( $sth, $args );
}
?>

Save the code in Example 5-17 as insert.php. It handles the process of testing database inserts.

Example 5-17. A script that tests database inserts

<?php
require_once( "mydb.php" );

$auth = new Author( );
$auth->set_name( "Jack" );
$auth->update( );
?>

Example 5-18 tests the process of loading data; save the script as load.php.

Example 5-18. A script that tests loading data from the database

<?php
require_once( "mydb.php" );

$auth = new Author( );
$auth->load( 1 );
?>
Name: <?php echo( $auth->get_name( ) ); ?>

Save the code in Example 5-19 as delete.php. It tests deletion using generated classes.

Example 5-19. A script that handles deletion testing

<?php
require_once( "mydb.php" );

$auth = new Author( );
$auth->deleteRecord( 1 );
?>

The largest portion of code for this hack is in the gen.php script. That code is the generator that builds the code. The code built relies on the dbwrap.php code and is tested by the insert.php, load.php, and delete.php scripts.

The gen.php script starts by reading in the XML document that maps to your database schema. In fact, a lot of the script is dedicated to reading the XML into an in-memory data structure. This is the section of code from the beginning to the xml_parse() line, which reads in the XML and calls the XML event handlers to parse up the tags.

After the XML is read in, code creation can begin. This kicks off with an initialization of the output buffering using ob_start(). Then the generator loops through the tables and fields, creating classes one by one. Once the classes are created, the output buffering is closed and the generated database access codenow stored in a stringis written out to the file.

5.5.2. Running the Hack

To generate the code for your database, first you must create a schema for the database in an XML file. I've provided an example file called schema.xml that defines a simple schema for a book database. To generate the code for this database, I use the command-line PHP interpreter:

	% php gen.php < schema.xml

The code generator then creates a file called mydb.php, which contains the PHP classes that will wrap the database records. With the example schema, the output looks like this:

	<?php
	require_once( "dbwrap.php" );

	class Book
	{
	  var $id;
	  var $title;
	  var $publisher_id;
	  var $author_id;
	  
	  function Book()
	  {
		$this->id = null;
	  }

	  function load($id)
	  {
		$data = selectOne( "SELECT * FROM book WHERE id = ?", array( $id ) );
		$this->id = $data['id'];
		$this->title = $data['title'];
		$this->publisher_id = $data['publisher_id'];
		$this->author_id = $data['author_id'];
	  }	  

	  function get_id( ) { return $this->id; }

	  function set_id( $val ) { $this->id = $val; }

	  function get_title( ) { return $this->title; }

	  function set_title( $val ) { $this->title = $val; }

	  function get_publisher_id( ) { return $this->publisher_id; }

	  function set_publisher_id( $val ) { $this->publisher_id = $val; }
	  
	  function get_author_id( ) { return $this->author_id; }

	  function set_author_id( $val ) { $this->author_id = $val; }

	  function update( )
	  {
		if ( $this->id != null ) { $this->updateRecord( ); }
		else { $this->insertRecord( ); }
	  }

	  function insertRecord( )
	  {
		return executeCommand( "INSERT INTO book ( id, title, publisher_id,
			author_id ) VALUES ( 0, ?, ?, ? )",
		  array( $this->title, $this->publisher_id, $this->author_id ) );
	  }
	
	  function updateRecord( )
	  {
		return executeCommand( "UPDATE book SET title=?, publisher_id=?, author
			id=? WHERE id=?",
		  array( $this->title, $this->publisher_id, $this->author_id, $this->
		    id ) );
	  }

	  function deleteRecord( $id )
	  {
	    return executeCommand( "DELETE FROM book WHERE id=?", array( $id ) );
	  }
	}

	class Publisher
	{
	  var $id;
	  var $name;

	  function Publisher( )
	  {
	    $this->id = null;
	  }
	  
	  function load($id)
	  {
	    $data = selectOne( "SELECT * FROM publisher WHERE id = ?", array
			( $id ) );
		$this->id = $data['id'];
		$this->name = $data['name'];
	  }

	  function get_id( ) { return $this->id; }

	  function set_id( $val ) { $this->id = $val; }

	  function get_name( ) { return $this->name; }

	  function set_name( $val ) { $this->name = $val; }

	  function update( )
	  {
		if ( $this->id != null ) { $this->updateRecord( ); }
		else { $this->insertRecord( ); }
	  }

	  function insertRecord( )
	  {
	    return executeCommand( "INSERT INTO publisher ( id, name ) VALUES
			( 0, ? )",
		  array( $this->name ) );
	  }

	  function updateRecord( )
	  {
	    return executeCommand( "UPDATE publisher SET name=? WHERE id=?",
		  array( $this->name, $this->id ) );
	  }

	  function deleteRecord( $id )
	  {
	    return executeCommand( "DELETE FROM publisher WHERE id=?", array
			( $id ) );
	  }
	}

	class Author
	{
	  var $id;
	  var $name;

	  function Author( )
	  {
	    $this->id = null;
	  }

	  function load($id)
	  {
	    $data = selectOne( "SELECT * FROM author WHERE id = ?", array( $id ) );
		$this->id = $data['id'];
		$this->name = $data['name'];
	  }

	  function get_id( ) { return $this->id; }

	  function set_id( $val ) { $this->id = $val; }
	  
	  function get_name( ) { return $this->name; }

	  function set_name( $val ) { $this->name = $val; }
	  
	  function update( )
	  {

	    if ( $this->id != null ) { $this->updateRecord( ); }
	    else { $this->insertRecord( ); }
	  }
	
	  function insertRecord( )
	  {
	    return executeCommand( "INSERT INTO author ( id, name ) VALUES
			( 0, ? )",
		  array( $this->name ) );
	  }

	  function updateRecord( )
	  {
	    return executeCommand( "UPDATE author SET name=? WHERE id=?",
		  array( $this->name, $this->id ) );
	  }

	  function deleteRecord( $id )
	  {
	    return executeCommand( "DELETE FROM author WHERE id=?", array( $id ) );
	  }
	}

	?>

There are three classes here, one for each table in the database. Each has member variables for all of the fields in the XML, a constructor that sets the ID to null, a set of get and set accessor methods, and functions to update or delete records.

To test these classes, run the insert.php file from the command line:

	% php insert.php

This adds a new author to the database. Now you can run the load.php script:

	% php load.php
	Name: Jack

That confirms that the new record went in as expected. Finally, delete the record with the delete.php script:

	% php delete.php

This generator, in combination with the other generators presented in this book, will allow you to generate redundant database access code much more quickly and accurately than you can by hand.

5.5.3. See Also

• "Generate Database SQL" [Hack #41]

• "Generate Database Select Code" [Hack #42]

• "Create Bulletproof Database Access" [Hack #35]

• "Design Better SQL Schemas" [Hack #34]

Origami Paper AirPlane