Workshop - week 05

Main Page >> Web Frameworks >> Workbook >> CRUD by Simon Baker (0718432)

How to build a simple ‘CRUD’ operation by Simon Baker (0718432).

I bet most of the classes are struggling with this feature, so I’ve decided to help you all out. Previously you have used the wiki to build a guestbook scenario. I’m not going to continue with the guestbook because by now you should be starting your own web site ready for the assessment which should NOT include the code from the wiki exercises. I will show you how to produce a fully working CRUD (Create Read Update Delete) operation so you can follow the idea in your own assessment.

For this exercise I will be using the common directory structure from the zend manual.

<source lang="php"> application --controllers --models --views public --css </source>

.htaccess

QuickStart/public/.htaccess

<source lang="php"> RewriteEngine on RewriteCond %{REQUEST_FILENAME} -s [OR] RewriteCond %{REQUEST_FILENAME} -l [OR] RewriteCond %{REQUEST_FILENAME} -d RewriteRule ^.*$ - [NC,L] RewriteRule ^.*$ /~<YourStudentNumber>/QuickStart/public/index.php [NC,L]

1. Security: Don't allow browsing of directories

Options -Indexes

1. PHP settings

php_flag magic_quotes_gpc off php_flag register_globals off php_flag short_open_tag on </source>

Bootstrap file : index.php

You should all know by now what the contents of the bootstrap does -

QuickStart/public/index.php

<source lang="php"> <?php //public/index.php //View errors & setup current time zone error_reporting(E_ALL|E_STRICT); ini_set('display_errors', 1); date_default_timezone_set('Europe/London');

// directory setup and class loading set_include_path('.' . PATH_SEPARATOR . '../library/'

    . PATH_SEPARATOR . '../application/models'
    . PATH_SEPARATOR . get_include_path());

include "Zend/Loader.php"; Zend_Loader::registerAutoload();

// load configuration $config = new Zend_Config_Ini('../application/config.ini', 'general'); $registry = Zend_Registry::getInstance(); $registry->set('config', $config);

// setup database $db = Zend_Db::factory($config->db); Zend_Db_Table::setDefaultAdapter($db);

// setup controller $frontController = Zend_Controller_Front::getInstance(); $frontController->throwExceptions(true); $frontController->setControllerDirectory('../application/controllers'); Zend_Layout::startMvc(array('layoutPath'=>'../application/layouts'));

// run! $frontController->dispatch(); </source>

Setting up the Controller

Firstly we will need to setup a controller for the CRUD actions.

<source lang="php"> <?php // application/controllers/IndexController.php

//In each function, we assign a title variable to the view property

class IndexController extends Zend_Controller_Action {

   function indexAction()
   {
       $this->view->title = "Students";
       $students = new Students();
       $this->view->students = $students->fetchAll();   
   }
   

//The fetchAll() function returns a Zend_Db_Table_Rowset

   function addAction()
   {
       $this->view->title = "Add New Name";
       
       $form = new StudentForm();
       $form->submit->setLabel('Add');
       $this->view->form = $form;
       
       if ($this->_request->isPost()) {
           $formData = $this->_request->getPost();
           if ($form->isValid($formData)) {
               $students = new Students();
               $row = $students->createRow();
               $row->first = $form->getValue('first');
               $row->last = $form->getValue('last');
               $row->save();
               
               $this->_redirect('/');
           } else {
               $form->populate($formData);
           }
       }
   }
   
   function editAction()
   {
       $this->view->title = "Edit Name";
       
       $form = new StudentForm();
       $form->submit->setLabel('Save');
       $this->view->form = $form;
       
       if ($this->_request->isPost()) {
           $formData = $this->_request->getPost();
           if ($form->isValid($formData)) {
               $students = new Students();
               $id = (int)$form->getValue('id');
               $row = $students->fetchRow('id='.$id);
               $row->first = $form->getValue('first');
               $row->last = $form->getValue('last');
               $row->save();
               
               $this->_redirect('/');
           } else {
               $form->populate($formData);
           }
       } else {
           // name id is expected in $params['id']
           $id = (int)$this->_request->getParam('id', 0);
           if ($id > 0) {
               $students = new Students();
               $student = $students->fetchRow('id='.$id);
               $form->populate($student->toArray());
           }
       }
   }
   
   function deleteAction()
   {
       $this->view->title = "Delete Name";
       
       if ($this->_request->isPost()) {
           $id = (int)$this->_request->getPost('id');
           $del = $this->_request->getPost('del');
           if ($del == 'Yes' && $id > 0) {
               $students = new Students();
               $where = 'id = ' . $id;
               $students->delete($where);
           }
           $this->_redirect('/');
       } else {
           $id = (int)$this->_request->getParam('id');
           if ($id > 0) {
               $students = new Students();
               $this->view->student = $students->fetchRow('id='.$id);
           }
       }
   }

} </source>

We have now set up the four actions that we want to use. They won’t work yet until we set up the views. The URLs for each action are:

URL Action

<source lang="php"> http://localhost/QuickStart/public/ IndexController::indexAction() http://localhost/QuickStart/public/index/add IndexController::addAction() http://localhost/QuickStart/public/index/edit IndexController::editAction() http://localhost/QuickStart/public/index/delete IndexController::deleteAction() </source>

We now have a working router and the actions are set up for each page of our application. It’s time to build the view.

Setting up the View

We now have to create a view for each Action just created

<source lang="php"> <?php

// application/views/scripts/index/index.phtml 

?>

<a href="<?php echo $this->url(array('controller'=>'index', 'action'=>'add'));?>">Add New Name</a>

<?php foreach($this->students as $student) : ?> <?php endforeach; ?>

First Name	Last Name
<?php echo $this->escape($student->first);?>	<?php echo $this->escape($student->last);?>	<a href="<?php echo $this->url(array('controller'=>'index', 'action'=>'edit', 'id'=>$student->id));?>">Edit</a> <a href="<?php echo $this->url(array('controller'=>'index', 'action'=>'delete', 'id'=>$student->id));?>">Delete</a>

</source>

application/views/scripts/index/add.phtml

<source lang="php"> <?php echo $this->form ;?> </source>

application/views/scripts/index/edit.phtml

<source lang="php"> <?php echo $this->form ;?> </source>

application/views/scripts/index/delete.phtml

<source lang="php"> <?php if ($this->student) :?>

Are you sure that you want to delete '<?php echo $this->escape($this->student->first); ?>' by '<?php echo $this->escape($this->student->last); ?>'?

<form action="<?php echo $this->url(array('action'=>'delete')); ?>" method="post">

 <input type="hidden" name="id" value="<?php echo $this->student->id; ?>" />
 <input type="submit" name="del" value="Yes" />
 <input type="submit" name="del" value="No" />

</form> <?php else: ?>

Cannot find name.

<?php endif;?> </source>

Layout view

We now need a layout view script. By default, this is called layout.phtml and lives in the layouts directory. It looks like this:

application/layouts/layout.phtml

<source lang="php"> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head>

   <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
   <title><?php echo $this->escape($this->title); ?></title>
   <link rel="stylesheet" type="text/css" media="screen"
            href="<?php echo $this->baseUrl();?>/css/site.css" />

</head> <body>

<?php echo $this->escape($this->title); ?>

   <?php echo $this->layout()->content; ?>

</body> </html> </source>

application/views/helpers/BaseUrl.php

View helpers live in the application/views/helpers subdirectory and are named {Helper name}.php (the first letter must be uppercase) and the class inside must be called Zend_Controller_Helper_{Helper name} (again, uppercase first letter). There must be a function within the class called {helper name}() (lowercase first letter – don’t forget!). In our case, the file is called BaseUrl.php and looks like this:

<source lang="php"> <?php //application/views/helpers/BaseUrl.php class Zend_View_Helper_BaseUrl { function baseUrl() { $fc = Zend_Controller_Front::getInstance(); return $fc->getBaseUrl(); } } </source>

Not a complicated function. We simply retrieve an instance to the front controller and return its getBaseUrl() member function.

Styling

Finally, we need a CSS style:

public/css/site.css

<source lang="php"> body,html { margin: 0 5px; font-family: Verdana,sans-serif; } h1 { font-size:1.4em; color: #008000; } a { color: #008000; } /* Table */ th { text-align: left; } td, th { padding-right: 5px; } /* style form */ form dt { width: 100px; display: block; float: left; clear: left; } form dd { margin-left: 0; float: left; } form #submitbutton { margin-left: 100px; } </source> Just a simple piece of css code to get you started.

The Database

We will use an .INI file called config.ini containing database connection details and we will store it in the application/ directory:

application/config.ini

<source lang="php"> [general] db.adapter = PDO_MYSQL db.params.host = localhost db.params.username = simon db.params.password = 123456 db.params.dbname = db####### </source>

Obviously you should use your username, password and database name! dbname is simply ‘db+your student number’.

Setting up Db_Table

Create the Table

I’m going to be using MySQL and so the SQL statement to create the table is:

<source lang="php"> CREATE TABLE students ( id int(11) NOT NULL auto_increment, first varchar(100) NOT NULL, last varchar(100) NOT NULL, PRIMARY KEY (id) ); </source>

Run this statement in a MySQL client such as phpMyAdmin or the standard MySQL command-line client.

Insert Test Names

We will also insert a couple of rows into the table so that we can test the retrieval functionality <source lang="php"> INSERT INTO students (first, last) VALUES ('Simon', 'Baker'), ('Clark', 'Kent'); </source>

The Model

Zend_Db_Table is an abstract class, so we have to derive our class that is specific to managing student names. It doesn’t matter what we call our class, but it makes sense to call it the same as the database table. Thus, our class will be called Students as our table name is students. To tell Zend_Db_Table the name of the table that it will manage, we have to set the protected property $_name to the name of the table. Also, Zend_Db_Table assumes that your table has a primary key called id which is auto-incremented by the database. The name of this field can be changed too if required. We will store our Student class in a file called Students.php within the applications/models directory:

application/models/Students.php

<source lang="php"> <?php class Students extends Zend_Db_Table { protected $_name = 'students'; } </source>

Adding New Names

We can now code up the functionality to add new names. There are two bits to this part --Display a form for user to provide details --Process the form submission and store to database We use Zend_Form to do this. The Zend_Form component allows us to create a form and validate the input. We create a new model class StudentForm that extends from Zend_Form to define our form:

application/forms/StudentForm.php

<source lang="php"> <?php //application/forms/StudentForm.php class StudentForm extends Zend_Form { public function __construct($options = null) { parent::__construct($options); $this->setName('student'); $id = new Zend_Form_Element_Hidden('id'); $first = new Zend_Form_Element_Text('first'); $first->setLabel('First') ->setRequired(true) ->addFilter('StripTags') ->addFilter('StringTrim') ->addValidator('NotEmpty'); $last = new Zend_Form_Element_Text('last'); $last->setLabel('Last') ->setRequired(true) ->addFilter('StripTags') ->addFilter('StringTrim') ->addValidator('NotEmpty'); $submit = new Zend_Form_Element_Submit('submit'); $submit->setAttrib('id', 'submitbutton'); $this->addElements(array($id, $first, $last, $submit)); } } </source>

Within the constructor of StudentForm, we create four form elements for the id, first name, last name, and submit button. For each item we set various attributes, including the label to be displayed. For the text elements, we add two filters, StripTags and StringTrim to remove unwanted HTML and unnecessary whitespace. We also set them to be required and add a NotEmpty validator to ensure that the user actually enters the information we require. We now need to get the form to display and then process it on submission. This is done within addAction():

Now you can test your working database.

                                                                       © S.Baker

Ready to move on?

When you're happy you understand what you've done here, take a look at the Week 6 Workshop