Kohana 3 ORM – Creating a Form Select List

Working with the Kohana 3 ORM, I went through the documentation, and didn't find anything on getting data back from the database, and creating a drop down select list in a form. This involved using a form, the ORM, and how to format the select list. I thought the combination might be helpful for readers.

I had to create a drop down list of department names in one of my forms. The department names were stored in a look-up table, depts, in my database. The look up table had two fields: dept_id, and dept_name. Here's what the table looks like in the database.

The database table

This is an ORM example. You need the ORM code set up for this to work. Set up the ORM code by downloading the codegen code from Github. Then put the unzipped code in your module directory, open your bootstrap file, and turn on the database, ORM, and codegen modules. Generate your ORM code into a temp directory, and then move the database table files into your application/model directory. Ok, were ready to go.

A word on the select list, or drop down menu. Almost every form you present to your users has a select drop down box. You do this to make the user select a value that is in the database, so there is no chance of a user putting in an incorrect entry. You use these a lot.

Here's the select list code in HTML:



The name is how you get back the value in your $_POST array, like so $_POST['theDept']. And what comes back is the value of the option the user selected from the drop down box. Most values are id's from your database table. What shows in the form's drop down box is on the right in the above HTML example.

Let's see how to get the same HTML code using Kohana, we put this in our view page.

echo Form::open ( 'searhcriteria', 
		array ('id'=>'theform'));

 echo Form::select('theDept', array(
  	'1'=>'Coach',
  	'2'=>'Cordinator',
  	'3'=>'Running Backs',
  ));

I threw in the form open statement so you could see that. The user when he hits the submit button on the web page will send the $_POST values to the "searhcriteria" controller. The id, "theform" sets up a div for CSS styling. As in the HTML example, "theDept" is the $_POST['theDept'] variable and the value is the key field in the array.

This is a working example. I've dropped the example code into my app to create the images on this page. Let's see what it will look like with with the drop down box closed and open, and with the database populated in the drop down.

Drop down not open

Drop Down Opened Up

The code to bring the data back from the database is done with an ORM call, like this:

$depts = ORM::factory('dept')->find_all()->as_array('dept_id','dept_name');

$data ['footballdepts'] = $depts;

The "ORM::factory" sets up an instance of an ORM object for the data coming from the database. The data will come from the "dept" table. The actual table name in my database is "depts" plural, the ORM file name is singular "dept". We use "dept" when referring to the table in the ORM call. The "find-all" gets all the data from the database table and brings it back as an array of objects.

We can't use objects in our drop down without converting them to values, normally done with a foreach loop. However the Kohana ORM has a nice method called "as_array" that will do this on the fly. The "as_array" method turns the object created with the "find_all" method into an array with the associative key field being the "dept_id" field, and the "dept_name" field being the name displayed in the drop down.

As a shameless plug, using my open-source variable checker, "newchk", that can be downloaded here. The below output shows the actual data that came back from the database. This was done with a call to, "new chk($depts);" on the next line of code after the ORM call.

Using newchk to check the data coming from the database

We have our data back from the database in an array. We assign it to the view data array with the next line, "$data['footballdepts'] = $depts;", and send it to the view page.

The Kohana form select call on the view page is done like so:

echo Form::select('theDept',$footballdepts);

Here's what it looks like in your form open and closed.

Dropdown not open

Same dropdown opened up

There's a problem here. If a user forgets to change the selection, the value will always come back "coach." Not good. We can make a blank top line with one call back in our controller, like so.

$depts = ORM::factory('dept')->find_all()->as_array('dept_id','dept_name');
array_unshift($depts, '' );
$data ['footballdepts'] = $depts;

The "array_unshift" adds a blank line at the top of the array, now the output looks like this:

With a blank line in the form

Opened up

Here's what the array looks like after the array_unshift($depts, '' ) call.

After array_unshift

Just what we want.

One gotcha when using array_unshift is that the keys in your array are renumbered, and may no longer match the database id's. Make sure to check your array keys after using array_unshift(). If your lookup table keys are in sequential order, this should not be a problem. If not, to get a blank line at the top of the dropdown, you have basically four options:

(1)You can use array_unshift, and make the key translation back in the "searchcriteria' controller when getting your data back from $_POST['theDept'] with a conditional. This is good for when only one or two keys change, and will never change again.

(2)You can use associative keys in your array, they are not reset when using array_unshift. This creates a lot of work with database updates and primary keys. I don't recommend this approach.

(3)You can NOT use the "as_array" method, just use find_all(), and create the array with a foreach loop. Before entering the foreach loop, create an array variable with a "0 => '' " for the first row, see below for an example.

(4)Finally, from a comment from one of our readers, Tomek Sulkowski. You can _add_ a new array with this empty element to the found array. The trick is to really add it with a "+" sign, not merge (array_merge also sorts keys). So:
$depts = array(0 => '') + $depts;

The "as_array" method in the ORM call will only set up one column. If you want, for example, "first name (space) last name" outputted in one column, or if you need to put a blank row at the top of the array, you'll have to use a foreach loop, like so.

$users = ORM::factory('user')->order_by('fname')->find_all();
// Put a blank row at the top of the array
$oneuser = array(0 => '');
// Create the array from the objects 
foreach($users as $user)
	{
	$oneuser[$user->user_id] = "$user->fname  $user->lname";
	}
// Send the data to the view
$data ['userarr'] = $oneuser;

If you really want to get fancy, you can create a drop down with one line of code in your view file, like this:

 

Form::select('theDept', ORM::factory('dept')->find_all()->as_array('dept_id','dept_name'));

One line of code, and with a lazy load, you've got your select list done. It can't get much easier than that.

If you would like to use the NewChk application to check your arrays in Kohana 3, download NewChk, drop the chk.php class file in your application/classes directory, and it will be autoloaded and available throughout every file in your application with a simple one line call.

Comments

Kohana 3 ORM – Creating a Form Select List — 8 Comments

  1. >>If you want, for example, “first name (space) last name” outputted in one column, or if you need to put a blank row at the top of the array, you’ll have to use a foreach loop, like so.

    ORM::factory(‘user’)->select(array(Db::expr(‘CONCAT(fname,” “,lname)’),’fullname’))->order_by(‘fname’)->find_all()->as_array(‘user_id’,’fullname’);

    We can use select() for create custom fields, and Db::expr for using sql expressions

  2. Form::select(‘theDept’, ORM::factory(‘dept’)->find_all()->as_array(‘dept_id’,’dept_name’));
    when i save it in the database, the id is saved instead of the name. how can i solve this? tia.

    • The Form::select method you called puts a drop down on your view page, not saves a value in the database.  It gets the table values from the database.  If a user selects a drop down name, and submits the form, the value of the drop down variable in the controller, will be the dept_id.  Since you are selecting the dept_id, and dept_name from the database for display in the form, the name is already in the database.  If that’s the case, it’s better to work with the id, than the name, which could have many duplicates.

  3. Don’t forget to validate your input, like this:

    In Model_Dept:

    public function rules() {  return array(    ‘dept_id’ => array(       array(‘in_array’, array(‘:value’, ORM::factory(‘dept’)->find_all()->as_array(‘id’, ‘id’)))    )   ); }

  4. There’s also 4th option: you can _add_ a new array with this empty element to the found array.
    The trick is to really add it with a “+” sign, not merge (array_merge also sorts keys). So:
    $depts = array(0 => ”) + $depts;

  5. There’s also 4th option: you can _add_ a new array with this empty element to the found array.
    The trick is to really add it with a “+” sign, not merge (array_merge also sorts keys). So:
    $depts = array(0 => ”) + $depts;