But this involves writing CRUD functions and these, though conceptually quite easy, are fairly complex and time-consuming. So for our site, I've written a generalized CRUD model, using CI's classes and helpers to make it easier. You'll see how this model works and how to integrate it into our application.
The CRUD model does more than just CRUD. It validates user-entered data, and also checks it in several ways—e.g., to see that you don't do a 'delete' operation without limiting it to a specific table row, or that you don't accidentally repeat 'create' operations by going back to the entry form and reloading it in your browser.
Lastly, the CRUD model contains its own self-testing framework, so that you can perform development tests as you build or adapt your code.
There are other CRUD frameworks out there, which may be more comprehensive and possibly better code. However, this one does work
First of all, we look at the design philosophy.
Then we look at a standard controller to use with the model.
Then we look at the way database tables must be structured.
•
•
•
Then we'll look at the model itself: firstly, the array that holds information about the database, and then at the separate functions.
Lastly, we'll look at the self-test functions.
The CRUD Model: Design Philosophy
The idea behind this CRUD model is that it can be called by any controller for any table. The data about that table, and how you want its update form displayed is held once, in an array. Everything else is standard: the controller just identifies itself (and thereby the table it acts on) and if necessary, gives an ID number for a record. So all you have to do is write a few simple controllers, and all the work of setting out forms and making database connections is done for you.
Remember that the user can't talk to a model directly, so (s)he has to go through a controller every time. You could put all the code in the controller, but then you'd have to copy it all for each new controller. This way, there is only one set of CRUD code in the model, so only one set to update and maintain. The price is that you have to keep passing information backwards and forwards between controllers and model, which makes the code slightly more difficult to follow.
For the sake of simplicity, I've used two external functions that aren't defined in
this code:
failure(), which reports errors; however, I want this done.
A model called display—this creates menus and sets up base URLs and so on. So all the � � � � �� � � � � � � CRUD�DDDDDDDDDDDfunctions build up a pile of data, put it in the $data variable, and simply call:
I also want the CRUD model to be able to test itself, so it includes a self-test suite. Calling this during the design process allows me to check that the model will behave as I want it to, under any conditions I can think of. (It's surprising how writing the test suite makes you realize new things that can go wrong—but better now than when your clients use the site.)$this->display->mainpage($data);
Please bear in mind that every model like this is a compromise. The more you ask of it, the more it asks of you. For instance, this model won't work unless your database tables are laid out in specific ways. It will lay out forms in quite sophisticated ways, but it is not infinitely flexible. It doesn't include JavaScript for better control of the user's experience. It can't handle exceptions to its own rules. On the other hand, provided you only want to do a set of standard things (that are pretty common), it makes life a lot easier.
•
•
•
•
The Standard Controller Format
Firstly, for each database table, you need a standard controller. This is how the user will interact with your table—e.g., to add a new site, change the details of an existing one, etc. To add a new person, the user will interact with the people table, so we need a different controller: but it is almost the same as the sites controller.
This is the controller for our sites table:
As you see, this is pretty lean and completely generalized. If you wanted to make it the people controller instead of the sites controller—in other words, to allow you to create, read, update or delete entries in the people table, all you need to do is:<?phpclass Sites extends Controller {
/*the filename, class name, constructor function names and this variable are the only thing you need to change: to the name of the table/controller (First letter in upper case for the Class name and constructor function, lower case for the file and variable.lower case!)*/var $controller = 'sites';
/*constructor function*/function Sites()
{
parent::Controller();
$this->load->model('crud');
}
/*function to update an entry (if an ID is sent) or to insert a new one. Also includes validation, courtesy of CI */function insert($id)
{
$this->crud->insert($this->controller, $id);
}
/*interim function to pass post data from an update or insert through to Crud model, which can't receive it directly*/function interim()
{
$this->crud->insert2($this->controller, $_POST);
}
/*function to delete an entry, needs table name and id. If called directly, needs parameters passed to function; if not, from Post array*/function delete($idno=0, $state='no')
{
if(isset($_POST['id'])&& $_POST['id'] > 0)
{$idno = $_POST['id'];}
if(isset($_POST['submit']))
{$state = $_POST['submit'];}
$this->crud->delete($this->controller, $idno, $state);
}
/*function to show all entries for a table*/function showall()
{
$this->crud->showall($this->controller, $message);
}
/*function to show all data in a table, but doesn't allow any alterations*/function read()
{
$this->crud->read($this->controller);
}
/*function to set off the test suite on the 'crud' model. This function need only appear in one controller, as these tests are made on a temporary test table so that your real data is not affected*/function test()
{
$this->crud->test();
}
}
?>
Change the class name from Sites to People (upper-case initial letter!).
Change the $controller variable from sites to people (lower case).
Change the constructor function name from Sites to People (upper case initial letter).
Save the new controller as:
system/application/controllers/people.php.The name of the controller must be exactly the same as the name of the database table to which it relates—so for the people table, it must be people. The name must have an uppercase first letter in the class definition line and the constructor function, but nowhere else.
•
•
•
•
The Database Tables
There are three simple rules for your database tables:
1. The main ID field in each table must always be called 'id' and must be an auto-incrementing field. (This is a standard MySQL field type. It automatically creates a new, unique number each time you make a
new entry.)
2. There must also be a field called 'name' in every table, if you want to use it as the basis for a dynamic drop-down box on any form.
3. You also need a 'submit' field for holding states, and things like that.
Otherwise you can have whatever fields you like, and name them anything your database system is happy with. Everything else is handled by the CRUD model, for any controller/table pair designed along these lines.
The Heart of the Model: the Array
Preliminaries over. Let's start on the CRUD model.
First, you need to define the CRUD model and a constructor function. Standard stuff by now:
<?phpclass Crud extends Model {
/*create the array to pass to the views*/var $data = array();
var $form = array();
var $controller;
function Crud()
{
// Call the Model constructorparent::Model();
$this->load->helper('form');
$this->load->helper('url');
$this->load->library('errors');
$this->load->library('validation');
$this->load->database();
$this->load->model('display');
Save it as system/application/models/crud.php.
Then comes the boring bit, but you only have to do it once. You need to write a multi-dimensional array. (I started to learn PHP from a book—it had better be nameless—which said 'multi-dimensional arrays aren't encountered very often,
so we won't go into them in further detail here'. I seem to have been using them
ever since.)
The first dimension of our array is the list of tables. (sites, people, etc.)
The second dimension is the list of fields within each table. For the sites tables, these are id, name, url, etc.)
The third dimension describes each field and provides a set of parameters that control how it will be handled by the insert/update form. These are:
The text you want the user to see on the insert form: how this field is described to a human, rather than its actual name. (So the first one is
ID number of this site rather than just id.) This is to help you to make your forms user-friendly.
The type of form object you want to use to display this field on your insert/ update form: this might be an input box, or a text area, or a drop-down box. (This CRUD model covers some but not all of the options.)
Any CI validation rules you want to impose when the user fills out this form. This can be left blank.
If you want to display this field as a dynamic drop-down box, the name
of the table it will draw on. See below for explanation. This can also be
left blank.
We've already declared the array as a class variable $form, so ever afterwards we have to refer to it as $this->form). It is defined inside the constructor, that is, it follows on immediately from the previous code.
$this->form =You can see that, within the $form array, there are sub-arrays for each table (here, sites and domains, though I only just started the latter, for space reasons) which each contain their own sub-sub-arrays, one for each field ('id', 'name', etc). Each of these sub-sub-arrays is in turn an array that contains the three or four values we described above.
array
('sites' => array
(
'id' => array('ID number of this site',
'readonly', 'numeric'),
'name' => array('Name of site', 'textarea',
'alpha_numeric'),
'url' => array('Qualified URL,
eg http://www.example.com', 'input', ''),
'un' => array('username to log in to site',
'input', 'numeric|xss_clean'),
'pw' => array('password for site', 'input',
'xss_clean'),
•
•
•
•
'client1' => array('Main client',
'dropdown', '', 'people' ),
'client2' => array('Second client', 'dropdown',
'', 'people'),
'admin1' => array('First admin', 'dropdown',
'', 'people'),
'admin2' => array( 'Second Admin', 'dropdown',
'', 'people'),
'domainid' => array('Domain name', 'dropdown',
'numeric', 'domains'),
'hostid' => array( 'Host', 'dropdown',
'numeric', 'hosts'),
'submit' => array( 'Enter details', 'submit', 'mumeric')
),
'domains' => array
(
'id' => array('ID number of this domain',
'hidden', 'numeric'),
//etc etc etc!!
It can be fiddly to get the array syntax right, but conceptually it is simple.
For the complete set of tables in our application, this array takes about 120 lines to specify. But you only have to do it once! This is the heart of your model. End the constructor function with a closing bracket '}', and go on to the other functions in the CRUD�D� model.
If you ever need to change your database tables (add a new field, for instance) or you want to change your validation rules, then you need only change the values in this array. Everything else will change automatically: for instance, next time you try to add a new entry, you should see the entry form reflecting the change.
Function by Function: the CRUD Model
The various functions that make up the � � � � � CRUD�DDDDmodel are as follows:
Showall
This is the function that the user will go to most often. It acts as an entry point for all the other operations—make a new entry, update, or delete an entry. It shows you what is already in the table. With some test data in the sites table, it looks like this:
As you can see, from this screen you can update the entry for a site, or delete it. You can also make a new entry, or read all the data in the table.
By the way, please bear in mind that this model doesn't include any security provisions. In a real site, you might want to fine-tune users' options—e.g., allow them to update but not delete. And you would want to make sure that hackers couldn't access the functions of the CRUD model by typing in URLs like www.example.com/index.php/sites/delete/18. CI's URL-based structure makes it relatively easy to deduce how the system accesses these commands, so you might want to ensure that the user has to be logged in to the site before the CRUD model will activate at all.
Right, back to the CRUD mechanisms. Remember that humans can't call the model directly. In each case the option (to delete, update, etc.) is exercised via a call to the controller. Just to jump back, the sites controller that called up the showall function did so with this line of code:
$this->crud->showall($this->controller);in other words, it substituted sites for $this->controller, and passed that value as a parameter to the � � � � � � � � � � � CRUD�DDDDDDDDDDfunction, to tell it which controller it was acting for.
Let's now look at the showall function. It has been passed its first parameter, sites. We'll leave $message until later. Concentrate on the highlighted lines.
/*this function lists all the entries in a database table on one page. Note that every db table must have an 'id' field and a 'name' field to display!This page is a jumping-off point for the other functions - ie to create, read, update or delete an entry.When you've done any of these, you are returned to this page. It has a 'message' parameter, so you can return with a message - either success or failure.*/function showall($controller='', $message = '', $test ='no')
{
$result = '';
$mysess = $this->session->userdata('session_id');
$mystat = $this->session->userdata('status');
if(!$this->db->table_exists($controller))
{
$place = __FILE__.__LINE__;
$outcome = "exception:$place:looking for table $controller: it doesn't exist'";
/*test block: what if there is no controller by that name?*/if($test =='yes')
{
return $outcome;
}
else{
$this->failure($outcome, 'sites');
}
}
/*end test block*/$this->db->select('id, name');
$query = $this->db->get($controller);
if ($query->num_rows() > 0)
{
$result .= "<table class='table'>";
$result .= "<tr><td colspan='3'><h3>$controller</h3></
td></tr>";
$result .= "<tr><td colspan='3' class='message'>
$message</td></tr>";
$result .= "<tr><td colspan='3'>";
$result .= anchor("$controller/insert/0", 'New entry');
$result .= "</td></tr>";
$result .= "<tr><td colspan='3'>";
$result .= anchor("$controller/read",
'Show all entries in the table');
$result .= "</td></tr>";
foreach ($query->result() as $row)
{
$result .= "<tr><td>";
$result .= $row->id;
$result .= " ";
$result .= $row->name;
$result .= "</td><td>";
$result .= anchor("$controller/insert/
$row->id",'Update this entry');
$result .= "</td><td>";
$result .= anchor("$controller/delete/
$row->id",'Delete');
$result .= "</td></tr>";
}
$result .= "</table>";
$data['text'] = $result;
$this->display->mainpage($data, $this->status);
}
else
{$place = __FILE__.__LINE__;
$outcome = "exception: $place:
no results from table $controller";
/*test block: were there results from this table/ controller?*/if($test == 'yes')
{$place = __FILE__.__LINE__;
return $outcome;
}
/*end test block*/else{
$message = "No data in the $controller table";
/*note: this specific exception must return to another controller which you know does contain data…… otherwise, it causes an infinite loop! */$this->failure($message, 'sites');
}
}
}
It sets up a table, displaying some data (id and name) about each entry. Each entry line also gives you the option to update or delete the entry: this is achieved using
CI's anchor function to create hyperlinks to the appropriate functions in the appropriate controller.
There's also a single line that offers you the opportunity to create a new site, again by offering a hyperlink to the controller's insert function. (Note: I've called the insert function both for making new entries and updating old ones. This is because the model assumes that if insert is called with an ID number, it is to update the corresponding entry. If it's called without an ID number, it creates a new entry.)
A lot of the code is taken up with exception handling: what if the table doesn't exist, what if the query returns no information? Exceptions are passed to the failure function. There are also two test blocks to allow me to run self-tests.
In addition, there's a line that allows you to read (but not alter) all the data in the tables. Let's look at the read function first, as it's the simplest.
Reading the Data
I've used CI's HTML Table and Active Record classes to show just how simple this piece of functionality is. I want a simple formatted page that shows all the data in the database in an HTML table. It doesn't allow any changes: it is literally the 'read' page.
First there has to be a function in the controller to call the model and tell the model which controller/table is to be displayed. That's the read() function in the standard controller.
It calls the following function in the CRUD model:
The two highlighted lines do all the work of querying the database and formatting the results./*queries the table to show all data, and formats it as an HTML table.*/function read($controller)
{
$this->load->library('table');
$tmpl = array (
'table_open' => '<table border="1" cellpadding="4" cellspacing="0" width="100%">',
'row_alt_start' => '<tr bgcolor="grey">',
);
$this->table->set_template($tmpl);
$this->load->database();
$this->load->library('table');
$query = $this->db->get($controller);
$result = $this->table->generate($query);
$data['text'] = $result;
$this->display->mainpage($data);
}
I've used the mainpage function in the display class to provide formatting for the page: the read function here just builds the data and hands it on as part of an array.
The result is a page full of data from the test file:
Let's just remind ourselves how control passes between the controller, the CRUD model, and other parts of the program.
user
clicks‘read’hyperlinkController‘read’functioncallsmodelCRUDmodelreadfunctiongeneratestableDisplayModelgeneratesviewViewgeneratesHTMLpageforuser
Delete and Trydelete
Deleting is the most permanent operation! For this reason our delete function checks to make sure of two things:
1. That a state variable of 'yes' has been set in the 'submit' field: if not, it passes the request to a trydelete function. That asks the user if she or he really does want to do a delete. If she or he confirms, the trydelete function sets a state variable of 'yes' and sends the request back to the delete function, which now accepts the delete instruction.
2. Before doing the delete query, it checks that an ID number has been set (otherwise all the entries might be deleted). Then, it uses CI's Active Record to do the delete and to check that one line has indeed been removed from the table. If one line was removed, then it returns to the showall function. You'll notice that it passes back two parameters—the controller name, and a message reporting that the deletion has been successfully done. (This is the second parameter to showall. If it is set, it appears in a red box at the top of the table, letting the user know what is going on.)
First, here's the delete function. You'll notice this code is also complicated by a lot of 'test block' lines. Ignore these for now: just follow the highlighted code..
/*DELETE FUNCTION: given table name and id number, deletes an entry*/function delete($controller, $idno, $state='no', $test='no')
{
/*first check that the 'yes' flag is set. If not, go through the trydelete function to give them a chance to change their minds*/if(!isset($state) || $state != 'yes')
{
/*test block: are 'yes' flags recognised?*/if($test == 'yes')
{
$place = __FILE__.__LINE__;
$outcome = "exception at $place: sent state value $state to trydelete function ";
return $outcome;
}
else
/*end test block*/{$this->trydelete($controller, $idno, 'no');}
}
else{
/*'yes' flag is set, so now make sure there is an id number*/if(isset($idno) && $idno > 0 && is_int($idno))
/*test block: with this id no, am I going to do a delete?*/{
if($test == 'yes')
{
$place = __FILE__.__LINE__;
$outcome = "OK at $place:
doing delete on id of $idno ";
return $outcome;
}
else{
/*end test block*//*if there is an id number, do the delete*/$this->db->where('id', $idno);
$this->db->delete($controller);
$changes = $this->db->affected_rows();
}
if($changes != 1)
{
/*test block: did I actually do a delete? */$place = __FILE__.__LINE__;
$outcome = "exception at $place: cdnt do delete op on $controller with id no of $idno";
if($test == 'yes')
{return $outcome;}
else
/*end test block*//*if there was no update, report it*/{$this->failure($outcome);}
}
else{
/*test block: I did do a delete*/
if($test == 'yes')
{return 'OK';}
else{
/*end test block: report the delete*/$this->showall($controller,
"Entry no. $idno deleted.");}
}
}
else
/*test block: report id number wasn't acceptable'*/{
$place = __FILE__.__LINE__;
$outcome = "exception at: $place : id no of $idno set for delete op in $controller, expecting integer";
if($test == 'yes')
{return $outcome;}
else
/*endtest block: if I failed, report me*/{$this->failure($outcome);}
}
}
}
I promised to explain the $message parameter we used when we call showall. You can see it here: if this function is successful, it returns to the showall page, by calling it with an appropriate message:
$this->showall($controller, "Entry no. $idno deleted.");}It's important not only that the action is done, but that the user knows it has been.
Now, back to preventing accidental deletions. If the delete function wasn't
called with the state=yes parameter, it reroutes the request to the trydelete function—the 'second chance'. Actually, only the trydelete function will ever set this parameter to yes, so the delete form will always present an are you sure option to the user.
Let's look at the trydelete function. It creates a simple form, which looks like this:
Clicking on yes re-calls the delete function. (Notice again that the form can't return directly to crud/delete, because a form can't point to a model. It has to point to the sites/delete function in the controller, which simply passes everything straight on to the crud/delete function in the model again.)
The subtle change is that, if the user confirms the delete, the trydelete form adds (as a hidden field) the submit=yes parameter, which goes into the post array, and
is returned to the controller's delete function. The controller's delete function reads the submit=yes parameter from the post array, and puts together a call to the
crud/ delete function, which this time includes state=yes as a parameter, so the delete function moves on to the next step.
If the user doesn't want to do the delete, she or he clicks on the hyperlink created by the CI anchor function, and is passed back to the showall function, which is most probably where she or he came from.
Here's the code that does all this:
/*TRYDELETE FUNCION: interrupts deletes with an 'are you sure? screen'*/
function trydelete($controller, $idno, $submit = 'no')
{
if($state == 'yes')
{$this->delete($controller, $idno, 'yes');}
else{
$result .= "<table><tr><td>Are you sure you want to delete this entry?</td></tr>";
$result .= form_open("$controller/delete");
$result .= form_hidden('id', $idno);
$result .= "<tr><td>";
$result .= form_submit('submit', 'yes');
$result .= "</td></tr>";
$result .= form_close();
$result .= "</table>";
$result .= anchor("$controller/showall",
"No, don't delete");
$data['text'] = $result;
$this->display->mainpage($data);
}
}
Just for clarity, here's a diagram of how control passes during a delete operation.
user
clicks‘delete’hyperlinkController‘delete’functioncallsmodelCRUDmodeldeletefunctionchecksforpermissiontodelete(i.e.submitfieldis‘yes’)Nopermission:callstrydeletefunctionuserreceivestrydeleteview.Ifuserconfirms‘delete’action,submitfieldissetto‘yes’userreceivesshowallview:confirmationofdeletionDisplayModelgenerateseither‘trydelete’or‘showall’viewPermission:deletesentry,callsshowallfunctionViewgeneratesappropriateHTMLpageforuser
As you can see, this is quite complex, more so than our previous example. The
model is doing all the work, but the user can only talk to the controller, so if you need to go back and re-present the question to the user, you need to involve the controller again.
However, once you've sorted it out, it works well and is highly logical. CI imposes this framework on you, but in the long run that's an advantage. Your code is consistent, and modular. Note how the same display model and the same view is invoked each time: what they show the user depends on the CRUD model function that called them.
Insert
This is the most complex function, because it generates a form for users to fill out. (Interface with humans is always the most difficult thing…)
Rather than write two separate functions, one to insert and one to update, and have to build the form twice, I've written one function that does duty for both. If you supply a valid ID number, it updates the corresponding record; if not, it inserts a new entry.
To make this easier to follow, I haven't included the test blocks that we saw in the delete function.
This is where we use the array we defined at the beginning. The function sets up a form, using CI's form helper, and based on the type of form element we specified in the array (dropdown, textarea, etc.). At the heart of the function is a switch statement, which accomplishes this.
The code uses CI's validation class to help us check the incoming data: remember we set the validation rules in our initial array.
/*the most complex function. This creates an HTML form, based on the description of the fields in the form array. This is sent to our display model, which sets up a view and shows it to the user.
The view then sends a POST array back to the controller. The form can't call this model directly, so it has to call the controller, which refers it back to the model.
Note the function parameters:
1. The controller parameter is whichever controller/ table has called the model - eg the 'sites' controller, or the 'domains' controller. The controller has the same name as the table it manipulates.
2. The optional id parameter is the id of an individual entry in that table.
3. The optional 'test' parameter is so you can set the form up to make usable responses to self-test functions.
*/
function insert($controller='', $id=0, $test='no')
{
$myform = '';
$myid = 0;
$currentvalue = array();
/*test if the table exists*/
if(!$this->db->table_exists($controller))
{
$place = __FILE__.__LINE__;
$outcome = "exception: $place:looking for table $controller: it doesn't exist'";
if($test =='yes')
{
return $outcome;
}
else{
$this->failure($outcome, $controller);
}
}
else
{
if($test =='yes')
{
return 'OK';
}
}
/*end test block*/
/*next check if there is an id number. If there is, we need to get the values to populate the table fields*/
if(isset($id) && $id > 0)
{$myid = $id;
$this->db->where('id', $id);
$query = $this->db->get($controller);
if ($query->num_rows() > 0)
{
$row = $query->row();
//--------------work out the values we want!
foreach($row as $key =>$value)
/*
first of all work out what value you want to show as the existing value in each line of the form. In priority order these are:
1. the last value the user entered, from the post array
2. the value from the database
3. nothing, if neither of these is set.
if we got here, the id does exist and is returning values, so get the existing values into a value array. Or, if there is something in the validation array, use that instead*/
{
$_POST[$key] = $this->validation->$key;
if(isset($_POST[$key]))
{$currentvalue[$key] = $_POST[$key];}
else
{$currentvalue[$key] = $value;}
}
/*test block: there was an id number, so has the program gone for an update? if this is not a test, of course, just do the update*/
if($test == 'yes')
{
$place = __FILE__.__LINE__;
$outcome = "exception: $place: id of $id returned results from $controller table so have gone for update";
return $outcome;
}
/*end test block*/
$myform .= "<tr><td colspan='2'>Update existing entry number $id</td></tr>";
}
/*now catch situation where this query isn't returning results. We could only have got here with an integer set as our ID number, so
this probably means we are trying to delete an entry that doesn't
exist.*/
else{
$place = __FILE__.__LINE__;
$outcome = "exception: $place: despite id of $id cant get any results from $controller table";
if($test == 'yes')
/*test block: there was and ID but there were no results*/
{
return $outcome;
}
/*end test block*/
else
{$this->failure($outcome, $controller);}
}
}
/*there was no ID number, so this is a new entry*/
else{
/*If the user has filled in values, and has returned here because some of them didn't validate, we still need to repopulate the form with what he entered, so he only has to alter the one that didn't validate. Get these from the post array*/
if(isset($_POST))
{
foreach($_POST as $key => $value)
{
if(isset($_POST[$key]))
{$currentvalue[$key] = $_POST[$key];}
}
}
$myform .= "<tr><td colspan='2'>New entry</td></tr>";
/*test block: there was no ID, so this is a new entry*/
if($test == 'yes')
{
$place = __FILE__.__LINE__;
$outcome = "exception: $place: id of $id treated as no id, so going for new entry";
return $outcome;
}
/*end test block*/
}
/*the table exists, whether this is an update or new entry, so start to build the form*/
$myform .= "<table class='table'>";
$myform .= form_open("$controller/interim");
$myform .= '<p>This entry could not be made because...</P>';
$myform .= $this->validation->error_string;
/*the rest of this function is common to inserts or update.
Look up in the form array which form field type you want to display, and then build up the html for each different type, as well as inserting the values you want it to echo.*/
foreach($this->form[$controller] as $key => $value)
{
/*This switch statement develops several types of HTML form field based on information in the form array.
It doesn't yet cover checkboxes or radio or password fields. It adds a 'readonly' type, which is a field that only displays a value and doesn't let the user modify it*/
$fieldtype = $value[1];
$val_string = $this->validation->$key;
switch($value[1])
{
/*a simple input line*/
case 'input':
$data = array(
'name' => $key,
'id' => $key,
'value' => $currentvalue[$key],
'maxlength' => '100',
'size' => '50',
'style' => 'width:50%',
);
$myform .= "<tr><td>$value[0]</td><td>";
$myform .= form_input($data);
$myform .= "</td></tr>";
if($test == 'second')
{
return 'input';
}
break;
case 'textarea':
/*a text area field.*/
$data = array(
'name' => $key,
'id' => $key,
'value' => $currentvalue[$key],
'rows' => '6',
'cols' => '70',
'style' => 'width:50%',
);
$myform .= "<tr><td valign=
'top'>$value[0]</td><td>";
$myform .= form_textarea($data);
$myform .= "</td></tr>";
break;
case 'dropdown':
/*a drop-down box. Values are dynamically generated from whichever table was specified in the forms array. This table must have an id field (which is now entered in the form) and a name field (which is displayed in the drop-down box).*/
$dropbox = array();
if(isset($value[3]))
{
$temptable = $value[3];
$this->db->select('id, name');
$query = $this->db->get($temptable);
if ($query->num_rows() > 0)
{
foreach ($query->result() as $row)
{
$dropbox[$row->id] = $row->name;
}
}
}
$myform .= "<tr><td valign=
'top'>$value[0]</td><td>";
$myform .= form_dropdown($key, $dropbox, $currentvalue[$key]);
$myform .= "</td></tr>";
break;
case 'submit':
/*a submit field*/
$myform .= "<tr><td>$value[0]</td><td>";
$time = time();
$data = array(
'name' => 'submit',
'id' => 'submit',
);
$myform .= form_submit($data);
$myform .= "</td></tr>";
break;
case 'hidden':
/*generates a hidden field*/
$myform .= form_hidden($key, $currentvalue[$key]);
break;
case 'readonly':
/*generates a field the user can see, but not alter.*/
$myform .= "<tr><td>$value[0]</td><td>$currentvalue[$key]";
$myform .= form_hidden($key, $currentvalue[$key]);
$myform .= "</td></tr>";
break;
case 'timestamp':
/*generates a timestamp the first time it's set*/
// $myform .= "<tr><td>$value[0]</td><td>now()";
$timenow = time();
if($currentvalue[$key]==''||$currentvalue[$key]==0)
{$time = $timenow;}
else{$time = $currentvalue[$key];}
$myform .= form_hidden($key, $time);
$myform .= "</td></tr>";
break;
case 'updatestamp':
/*generates a timestamp each time it's altered or viewed*/
// $myform .= "<tr><td>$value[0]</td><td>now()";
$timenow = time();
$myform .= form_hidden($key, $timenow);
$myform .= "</td></tr>";
break;
default:
$place = __FILE__.__LINE__;
$outcome = "exception: $place:
switch can't handle $fieldtype";
/*test block: what if the switch doesn't recognise the form type?'*/
if($test == 'second')
{
return $outcome;
}
/*test block ends*/
else {
$this->failure($outcome, $controller);
}
}
/*end the foreach loop which generates the form*/
}
$myform .= form_hidden('submit',$time);
$myform .= form_close();
$myform .= "</table>";
/*Finally we've built our form and populated it! Now, stuff the form in an array variable and send it to the model which builds up the rest of the view.*/
$data['text'] = $myform;
$this->display->mainpage($data);
}
A couple of things to explain here. All the form field types are standard, except for readonly—which is a hidden form field that allows you to see, but not to alter, what it says. This is not secure, of course: a smart user can easily hack the value. It's just designed to simplify the choices the user faces.
You'll notice the form points to a function called interim, on whichever controller called it. Again, that's because you can't address a model directly via its URL. So, if it was set up by the 'sites' controller, the form points to 'sites/interim' and the values entered by the user, or from existing data, are packed in the $_POST array and sent there. As you'll recall from the beginning, that function just calls the crud function insert2, passing on the $_POST array to it as a parameter.
Insert2
Insert2 receives the $_POST array as a parameter and checks to see if it has an 'id' field set. If yes, it updates that entry. If not, it creates a new entry.
In order that CI's validation class, which requires a $_POST array, can work, our function renames the array it received as a parameter as $_POST.
function insert2($controller, $newpost, $test = 'no')
{
$myform = '';
/*test the incoming parameters*/
if(!$this->db->table_exists($controller))
{
//test here!
}
$this->load->library('validation');
/*handle the validation. Note that the validation class works from the post array, whereas this function only has a $newpost array: same data, but different name. So we re-create the $_POST array.
*/
$_POST = $newpost;
/*now build up the validation rules from the entries in our master array*/
$errorform = '';
$newtemparray = $this->form[$controller];
foreach($newtemparray as $key => $value)
{$rules[$key]= $value[2];}
$this->validation->set_rules($rules);
/*and the name fields*/
foreach($newtemparray as $key => $value)
{$fields[$key]= $value[0];}
$this->validation->set_fields($fields);
$this->validation->set_fields($fields);
/*now do the validation run*/
if ($this->validation->run() == FALSE)
{
/*if the validation run fails, re-present the entry form by calling the 'insert' function*/
$id = $_POST['id'];
$this->insert($controller, $id, 'no', $_POST);
}
else
{
/*The validation check was OK so we carry on. Check if there is an id number*/
if(isset($_POST['id']) && $_POST['id'] > 0)
{
/*if yes: this is an update, so you don't want the id number in the post array because it will confuse the autoincrement id field in the database. Remove it, but save it in $tempid to use in the 'where' condition of the update query, then do the update*/
$tempid = $_POST['id'];
unset($_POST['id']);
$this->db->where('id', $tempid);
$this->db->update($controller, $_POST);
if($this->db->affected_rows()== 1)
{$this->showall($controller, "Entry number $tempid updated.");}
else{$this->failure("Failed to update $controller for id no $tempid", __FILE__,__LINE__);}
/*if no id number, we assume this is a new entry: no need to unset the post array id as it isn't there! the database will create its own id number. Do the new entry*/
$this->db->insert($controller, $_POST);
if($this->db->affected_rows()== 1)
{$this->showall($controller,
"New entry added.");}
else{$this->failure("Failed to make new entry in $controller ", __FILE__,__LINE__);}
}
}
}
And that's it. A few hundred lines of code, which allow you to do CRUD on
any table.
The Test Suite
Remember those 'test blocks' in the delete function? Their purpose is simply to detect if the function is being run 'for real' or for a test, and, if the latter, to make sure that it returns a value we can easily test.
This is all because, at the end of the CRUD model, we have a 'self-test' suite. This is called by the test function in any controller (it doesn't matter which one) and performs generalized CRUD tests using a dummy table.
First in the CRUD class there is a master 'test' function, which only exists to call
the others.
/*now a suite of self-test functions.*/
/*first function just calls all the others and supplies any formatting you want. Also it builds/ destroys temporary data table before/ after tests on the database.*/
function test()
{
$return = "<h3>Test results</h3>";
$this->extendarray();
$return .= $this->testarray();
$this->reducearray();
$return .= $this->testarray();
$this->testbuild();
$return .= $this->testdelete();
$this->testdestroy();
$return .= $this->testinsert();
$return .= $this->testinsert2();
$return .= $this->testshowall();
$data['text'] = $return;
$this->display->mainpage($data);
}
This just assembles any tests you want, and runs them.
However, rather than go through all these functions, let's just show one: the test function called testdelete().
First, though, we need two functions: one to build, and one to destroy, our special dummy testing table, 'fred'. The first function destroys any existing 'fred' table, builds another, and puts a line of test data in it:
/*this function builds a new temporary table. 'fred', in your database so you can test the CRUD functions on it without losing real data*/
function testbuild()
{
$this->db->query("DROP TABLE IF EXISTS fred");
$this->db->query("CREATE TABLE IF NOT EXISTS fred (id INT(11) default NULL, name varchar(12) default NULL)");
$this->db->query("INSERT INTO fred VALUES (1, 'bloggs')");
}
Depending on the test you want to run, you can make this more elaborate—e.g., populate more fields, or have more rows of data.
The second destroys the table so we can start afresh. There ought not to be any value left in it after we've done the delete test, but in case that failed, or in case we write other tests, let's make sure:
/*this function destroys the temporary table, to avoid any confusion later on*/
function testdestroy()
{
$this->db->query("DROP TABLE IF EXISTS fred");
}
Now we can start to test the delete function:
function testdelete()
{
$result = '<p>Deletion test</p>';
The first test we might do is to make sure that the 'delete' function intercepts any delete call without a $state parameter of yes, and sends it to the trydelete function to ask 'are you sure?'
Remember that we want the test to return 'OK' if the program handles each possibility correctly—not if the possibility itself is 'right' or 'wrong'. So if the 'state' parameter says 'haggis', which is clearly 'wrong', the test should say 'OK' as long as the program treats it as 'not yes'! Ideally, we only want a short list highlighting the failures: if the tests are successful, we don't need to know the details.
First we set up an array, in which each key is an expression we might use in a test, and the corresponding value is the result we expect:
$states = array(
'no' => 'exception',
'1' => 'exception',
'haggis'=> 'exception',
'yyyes' => 'exception',
'yes' => 'OK'
);
foreach($states AS $testkey => $testvalue)
{$test = $this->delete('fred', 1, $testkey, 'yes');
/*if you got the value you want, preg_match returns 1*/
$result .= $this->unit->run(preg_match("/$testvalue/", $test), 1, $test);
}
Assuming our code is working properly, that will return:
Our next test is to see how, given a correct state, the delete function reacts to a series of ID values—including non-integers, negative values, etc. Be careful about the granularity of the tests. For instance 9999 is a valid ID number in that it is an integer and greater than 0, but it won't lead to a delete operation as we only have one record, with an ID of 1! You need to be clear which stage of the process you are testing.
All being well, that will return something like this:/*given $state set to 'yes', test another array of values for the id number. Start by building a test table*/$this->testbuild();
/*then another array of values to test, and the results you expect..*/$numbers = array(
'9999' => 'OK',
'-1' => 'exception',
'NULL' => 'exception',
'0' => 'exception',
'3.5' => 'exception',
'' => 'exception',
'1' => 'OK'
);
/*now do the tests*/foreach($numbers AS $testkey => $testvalue)
{$test = $this->delete('fred', $testkey, 'yes', 'yes');
$result .= $this->unit->run(preg_match("/$testvalue/", $test), 1, $test);
}
/*destroy the test table, just in case*/$this->testdestroy();
/*return the results of this test*/return $result;
}
You can add as many tests as you wish.
Testing helps the development process. As you think about different values to put
in your test arrays, you have to consider whether your code will in fact handle
them gracefully.
It may also help later on, if you change your code and accidentally introduce errors; and it will reassure you, once the code goes to a production site, to run a test now and then.
Summary
We've seen:
How to generalize CRUD operations so that you can do them with two classes: one for the controller, and one for the CRUD model. The former needs to be repeated for each table, the latter stays the same.
As a result we could built in various checks and safeguards, as well as tests, so we can be confident that our CRUD operations are done.
Using CI has allowed us to write all of this in a few hundred lines of (relatively) simple code, which we can reuse on almost any site we build, provided we obey a few simple naming and layout rules. To me, that's what frameworks are all about.
