I’ve put together a database admin application using the database functionality provided by the Zend Framework. Basically, it’s a generic set of classes that allows people to quickly add, edit, delete, and view data in a database table. The nice thing is that this uses the Zend_DB_Table classes, so it should presumably work with just about any database supported by the Zend Framework. I’ve tried this on mySQL and sqlite, and it worked just fine on both. There are some limitations of course. Right now this will only work on tables that have auto-increment primary keys. I don’t quite know how this would work with databases that don’t have auto-increment such as Oracle – maybe this could have support for defining the sequence that should be used when inserting data. This is a PHP5 only class due to it’s reliance on the Zend Framework.

The intention of Zdbform is to have a quick way to put up a database admin page for webapp control panel that is easier to use than tools such as phpMyAdmin. Something that can provide validation, customization of the view, integration within other pages,

You can see an example of this running or check out the API docs.

Zdbform has the following features:

  • Simple maintenance of database tables
  • View table data with pagination, searching, and sorting of columns
  • Ability to change the display names of columns
  • Ability to hide columns in view, add, and edit
  • Extendable set of widgets to show data in table / forms
  • Extendable set of validators to validate column data
  • Ability to add row validators that validate the full contents of a newly inserted or edited row

Other items that I would like to add in the future are:

  • Custom callbacks on add / edit / delete
  • Think about defining Zdbform as extension of Zend_DB_Form
  • If definition of class settings is externalized it could open up opportunities for export of data, ajax editing, ajax validation, etc.
  • Rename classes to fit within naming standards of Zend Framework. Think about how this fits into the MVC parts of the Zend Framework.

The page that defined the example is shown below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

<?php
require_once 'Zend/Db.php';
require_once 'Zend/Db/Table.php';
require_once 'zdbform.class.php';
require_once 'zdbform_widgets.class.php';
require_once 'zdbform_validations.php';
$params = array (
    'host'     => 'localhost',
    'username' => 'test',
    'password' => 'test',
    'dbname'   => 'delicious.db'
);
$db = Zend_Db::factory('pdoSqlite', $params);
Zend_Db_Table::setDefaultAdapter($db);
class Posts extends Zend_Db_Table {}
 
$dbform = new Zdbform('Posts');
$dbform->setWidget('posted', 'timestamp');
$dbform->setWidget('description', 'html');
$dbform->setWidget('href', 'link');
$dbform->setDisplayName('id', 'ID');
$dbform->setDisplayName('href', 'Link');
$dbform->hideColumns('posted');
$dbform->setWidgetOption('href','width',70);
$dbform->setHelpText('posted', 'm/d/y format');
$dbform->setHelpText('tags', 'Enter one or more tags, separated by commas');
$dbform->tableTitle = "Backed up delicious links";
$dbform->editTitle = "Edit link information";
$dbform->addTitle = "Add new link";
$dbform->addColumnValidator('tags','validate_minlength',array('minlength' => 4));
$dbform->addColumnValidator('href,description','validate_required',array());
$dbform->rowsPerPage = 15;
$dbform->processForms();
 
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>ZDBFORM: Manage Posts Table</title>
<link type="text/css" rel="stylesheet" href="zdbform.css" />
</head>
 
<body>
<?php
$dbform->showForms();
$dbform->showTable();
?>
</body>
</html>

Here are the main steps:

  • Lines 2-6 include the required libraries for this to work. It’s important to note that you need to have the Zend Framework classes in your include path for PHP. Alternatively, you can use the set_include_path at the top of the script.
  • Lines 7-13 sets up database parameters and then creates a Zend DB Adapter using the options provided. In this case it’s a sqlite database. Typically this stuff would probably just be defined in one place in your application, and then included, but for this case I’ve put everything on one page
  • Lines 14-15 set the newly created Zend DB Adapter to be the default adapter for Zend DB Table and then defines ‘Posts’ a class that extends Zend_DB_Table. The name of the class is important – it is the name of the database table we want to manage.
  • Line 17 is the first place where we see Zdbform. Here, we create an instance of Zdbform, passing it the name of the table we want to manage.
  • Lines 18-32 set various options for our table. For example, Line 18 sets the posted column’s widget to timestamp, which will show a dropdown for selection of a date. Line 19 sets the description column’s widget to html, which will use FCKeditor (a JavaScript HTML editor) when adding and editing data. Lines 30 and 31 set various validators that will validate data on edit or insert. All of these steps are optional and only enhance the behavior / look and feel of Zdbform.
  • Line 33 calls Zdbform::processForms(), which will process any data that has been submitted for add, edit, or delete
  • Finally, lines 47 and 48 display the forms and display the table of data

Download the code and example