Zend Framework Database Admin

If you’re looking for a simple tool that uses Zend Framework’s robust database classes (such as Zend_Db and Zend_Db_Table), you can check out zdbform. It’s a short yet effective library that let’s you perform simple administration tasks on your database, with minimal coding.

It’s not a full blown phpMyAdmin, but it’s a simple way to view, edit and add your tables rows in a web interface. Also, don’t expect it to scale, because I am sure this library was written to serve some quick table administration needs, and that it is not ready to handle large datasets. But, it is very convenient if you have a small database to administer.

I implemented it with the Zend MVC components, and following is a brief overview.

In the front controller, or front plugin, or any class that your controller subclasses:

$this->db = Zend_Db::factory('Pdo_Mysql', array(
				'host'     => DB_HOST,
				'username' => DB_USER,
				'password' => DB_PASS,
				'dbname'   => DB_NAME
		));
Zend_Db_Table_Abstract::setDefaultAdapter($this->db);

Then set your controller and view scripts as necessary. Let’s say you have two tables to admin, “clients” and “history”. First make sure they are declared as subclasses of Zend_Db_Table:

require_once "Zend/Db/Table/Abstract.php";

class Clients extends Zend_Db_Table_Abstract
{
    protected $_name = 'clients';
}

class History extends Zend_Db_Table_Abstract
{
    protected $_name = 'history';
}

Your controller would look like:

require_once "Zend/Controller/Action.php";

class AdminController extends Zend_Controller_Action
{

	public function init()
	{
		require_once 'zdbform/zdbform.class.php';
		require_once 'zdbform/zdbform_widgets.class.php';
		require_once 'zdbform/zdbform_validations.php';

		parent::init();

		$this->view->headLink()->appendStylesheet('/zdbform/zdbform.css');
		$this->_helper->viewRenderer('index');
	}

	public function indexAction()
	{
	}

	public function clientsAction()
	{
		$this->view->dbform = new Zdbform('Clients');
		$this->view->dbform->setWidget('description', 'textarea');
		$this->view->dbform->processForms();
	}

	public function historyAction()
	{
		$this->view->dbform = new Zdbform('History');
		$this->view->dbform->processForms();
	}

}

And the single view script you need is admin/index.phtml:

<?php
echo $this->headLink();

include_once "Zend/Filter/Word/CamelCaseToDash.php";
include_once "Zend/Filter/Word/CamelCaseToUnderscore.php";
$cctd = new Zend_Filter_Word_CamelCaseToDash();
$cctu = new Zend_Filter_Word_CamelCaseToUnderscore();
$classes = get_declared_classes();

foreach ($classes as $class)
{
	if (is_subclass_of($class,'Zend_Db_Table_Abstract'))
	{
	?>
		<a href="/admin/<?= strtolower($cctd->filter($class)) ?>"><?= strtolower($cctu->filter($class)) ?></a>&nbsp;&nbsp;
	<?php
	}
}

if ($this->dbform)
{
	?>
	<h1>Table: <?= $this->dbform->tableName ?></h1>
	<?php
	$this->dbform->showForms();
	$this->dbform->showTable();
}
?>

There were also a couple things that needed changing in the zdbform class itself:

  • Replace all PHP_SELF with REQUEST_URL. On the mvc case, PHP_SELF is empty or index.php, and we don’t want all the forms posted there, we want them to go back to /admin/clients or /admin/history
  • After this line
    $this->pk = $tableInfo['primary'];

    I had to add this:

    if (is_array($this->pk))
    	$this->pk = $this->pk[1];
  • zdbform->orderBy is treated as a single column, of you want multiple column sorting you have to hack a bit with getAllRows().

That’s it, point your browser to /admin and you’re good to go. In a very short time and with a little bit of code, you can get something similar to a stripped down version of phpMyAdmin, using the power of Zend Framework.

About these ads

5 thoughts on “Zend Framework Database Admin

  1. please help me .
    I connected to my database , my page is ok when I get data form database .but when I use zdbform . I have a problem with zdbform .

    my page display error :

    Fatal error: Uncaught exception ‘Zend_Db_Statement_Exception’ with message ‘SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘Array’ in ‘order clause” in C:wampwww1libraryZendDbStatementPdo.php:238 Stack trace: #0 C:wampwww1libraryZendDbStatement.php(283): Zend_Db_Statement_Pdo->_execute(Array) #1 C:wampwww1libraryZendDbAdapterAbstract.php(433): Zend_Db_Statement->execute(Array) #2 C:wampwww1libraryZendDbAdapterPdoAbstract.php(230): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Table_Select), Array) #3 C:wampwww1libraryZendDbTableAbstract.php(1330): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Table_Select)) #4 C:wampwww1libraryZendDbTableAbstract.php(1158): Zend_Db_Table_Abstract->_fetch(Object(Zend_Db_Table_Select)) #5 C:wampwww1libraryzdbformzdbform.class.php(469): Zend_Db_Table_Abstract->fetchAll(NULL, ‘Array ASC’, 15, 0) #6 C:wampwww1libraryzdbformzdbform.class.php(478): Zdbform->getAllRows() #7 C:wampwww1applicationviewsscriptsadminfaq in C:wampwww1libraryZendDbStatementPdo.php on line 238

    thanks !

  2. It seems that in your case the “order by” in line 469 is passed as an array instead of a column name (notice the “Array ASC” in your trace dump.

    Did you add the 2 lines of code I wrote about in the end of the post to the zdbform class? It seems the original Zend_Db_Table wasn’t returning the primary key as an array, but it was probably enhanced to return an array at some version (you can dig through Zend Fisheye to try and track this change). I believe that adding these lines will solve the problem.

  3. add under line 148:
    if(is_array($this->orderBy)) $this->orderBy = implode(“,”,$this->orderBy);

    and edit line 140 to
    $this->pk=$tableInfo['primary'][1];

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s