Auto Form Fields

If you're like me, and you hate having to create forms by hand for every new project, you might appreciate this helper. It automatically generates your form fields based on your table fields. It even checks for lookup and bridge tables that are related to your form.

As of right now this only supports MySQL, but by no means is it a finished product. And undoubtedly you guys can think of a hundred ways to improve upon it.

IMPORTANT: The helper currently expects bridge tables (for many-to-many relationships) to end in "_bridge". So, for users_movies, the bridge table should be called users_movies_bridge. I'm not saying this is the best naming convention, it's just what I happen to use.

Sample database tables (used in example usage below):

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `group` int(11) NOT NULL DEFAULT '10',
  `role_id` int(11) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `pass` varchar(128) DEFAULT NULL,
  `fname` varchar(64) DEFAULT NULL,
  `about` text,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

CREATE TABLE IF NOT EXISTS `users_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `users_roles` (`id`, `name`) VALUES
(1, 'Board of Directors'),
(2, 'President'),
(3, 'Vice President'),
(4, 'Director'),
(5, 'Manager');

CREATE TABLE IF NOT EXISTS `users_tv_shows` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `users_tv_shows` (`id`, `name`) VALUES
(1, 'Seinfeld'),
(2, 'Curb Your Enthusiasm'),
(3, 'The Office'),
(4, 'Fringe'),
(5, 'Lost');

CREATE TABLE IF NOT EXISTS `users_tv_shows_bridge` (
  `id` int(11) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Example usage:

// Controller

$this->load->helper('form');
$data['user'] = $this->db->where('id',1)->get('users');
$this->load->view('form_view',$data);

// View

<?=form_open()?>
<?=form_auto_fields('users',$user)?>
<?=form_close()?>

And the helper:

function form_auto_fields($table = null, $db_record = null, $exceptions = array(), $vocab = array())
{
    // table name required
    if(!is_null($table))
    {
        // get CI super object
        $ci =& get_instance();

        // load 'information_schema' database
        $ci->info_db = $ci->load->database('info_sch', TRUE);
        
        // retrieve field comments, and simplify array (this is for future usage of field comments, not currently implemented)
        $info_query = $ci->info_db->select('column_name, column_comment')->where('table_name',$table)->get('columns');
        $comments = array();
        foreach($info_query->result_array() as $info_field)
        {
           
           $comments[$info_field['column_name']] = $info_field['column_comment'];
        }
        
        // retrieve all table fields
        $ci->db = $ci->load->database('default', TRUE);
        $q = $ci->db->query("DESCRIBE `$table`");
        
        // default EXCEPTIONS
        // is merged with passed $exceptions array
        $default_exceptions = array(
            'group',
            'add_date',
            'exp_date'
        );
        $exceptions = (!empty($exceptions) AND is_array($exceptions)) ? array_merge($exceptions,$default_exceptions) : $default_exceptions;
        
        // default VOCAB list
        // is merged with passed $vocab array
        $default_vocab = array(
            'email' => 'Email',
            'pass' => 'Password',
            'fname' => 'First Name',
            'lname' => 'Last Name',
            'addr' => 'Address',
            'city' => 'City',
            'st' => 'State',
            'zip' => 'Zip',
            'phone' => 'Phone',
            'about' => 'About'
        );
        $vocab = (!empty($vocab) AND is_array($vocab)) ? array_merge($vocab,$default_vocab) : $default_vocab;
        
        // db record
        if(!is_null($db_record))
        {
            $db_record = $db_record->row_array();
        }
        
        // create new column object and default variables
        $col = new StdClass;
        $count = 0;
        $fields = array();
        $form_multipart = false;
        $joined_tables = array();
            
        foreach($q->result() as $field)
        {
            // get field name, type, key, auto_incrementing and comments
            $col->name = $field->Field;
            $col->type = preg_replace('/\(.+?$/',"",$field->Type);
            $col->primary = ($field->Key == 'PRI') ? 1 : 0;
            $col->auto_increment = ($field->Extra == 'auto_increment') ? 1 : 0;
            $col->comments = $comments[$col->name];
            
            // get field length
            preg_match('/\((.*?)\)/', $field->Type, $matches);
            $col->length = (isset($matches[1])) ? $matches[1] : '';
            
            // default variables
            // which need to be reset with each loop
            $field_output = '';
            $short_name = '';
            
            // get field's db record (if passed)
            $field_db_record = (isset($db_record[$col->name])) ? $db_record[$col->name] : '';
            
            // exclude EXCEPTIONS, primary keys, auto_incrementing fields, timestamps
            if($col->primary == 0 AND $col->auto_increment == 0 AND $col->type != 'timestamp' AND !in_array($col->name,$exceptions))
            {
                if($col->type == 'varchar')
                // VARCHAR
                {
                    $field_output .= '<input type="'.($col->name == 'pass' ? 'password' : 'text').'" name="'.$col->name.'" value="'.$field_db_record.'" class="" />';
                }
                elseif($col->type == 'text')
                // TEXT
                {
                    $field_output .= '<textarea name="'.$col->name.'" class="">'.$field_db_record.'</textarea>';
                }
                elseif($col->type == 'int')
                // INTEGER
                {
                    $short_name = rtrim($col->name,'_id');
                    $join_table = false;
                    
                    // check if field has corresponding table
                    // example: cat_id will have table "cats" or "{$table}_cats"
                    if($ci->db->table_exists($short_name.'s'))
                    {
                        $join_table = $short_name.'s';
                    }
                    elseif($ci->db->table_exists($table.'_'.$short_name.'s'))
                    {
                        $join_table = $table.'_'.$short_name.'s';
                    }
                    elseif($ci->db->table_exists($table.'_'.$short_name))
                    {
                        $join_table = $table.'_'.$short_name;
                    }
                    
                    // if table found, create dropdown
                    if($join_table)
                    {
                        $joined_tables[] = $join_table; // this is used AFTER the loop to exlude this table when looking for lookup tables
                        
                        $q = $ci->db->get($join_table);
                        $field_output .= form_dropdown($col->name,$q,$field_db_record);
                    }
                    // otherwise, create input field
                    else
                    {
                        $field_output .= '<input type="text" name="'.$col->name.'" value="" class="numeric" />';
                    }
                }
                elseif($col->type == 'tinyint' AND $col->length == 1)
                // TINYINT (bool)
                {
                    $field_output .= "<ul>\n";
                    $field_output .= '<li>&lt;input type="radio" name="'.$col-&gt;name.'" value="1" class="checkbox"'.($field_db_record === '1' ? ' checked="checked"' : '').' /><span>Yes</span></li>'."\n";
                    $field_output .= '<li>&lt;input type="radio" name="'.$col-&gt;name.'" value="0" class="checkbox"'.($field_db_record === '0' ? ' checked="checked"' : '').' /><span>No</span></li>'."\n";
                    $field_output .= "</ul>\n";
                }
                elseif($col->type == 'decimal' OR $col->type == 'float')
                // DECIMALS and FLOATS
                {
                    
                }
                elseif($col->type == 'date' OR $col->type == 'timestamp')
                // DATES and TIMESTAMPS
                {
                    $field_output .= '&lt;input type="text" name="'.$col-&gt;name.'" value="'.$field_db_record.'" class="date" />';
                }
                
                // after all that, if field has been created, build final <li> output
                if($field_output != '')
                {
                    $fields[$count] = "<li>\n";
                    $fields[$count] .= '<label>';
                    if($short_name != '')
                    {
                        $fields[$count] .= ucwords($short_name);
                    }
                    elseif(isset($vocab[$col->name]))
                    {
                        $fields[$count] .= $vocab[$col->name];
                    }
                    else
                    {
                        $fields[$count] .= ucwords($col->name);
                    }
                    $fields[$count] .= "</label>\n";
                    $fields[$count] .= "<div>\n".$field_output."\n</div>\n";
                    $fields[$count] .= "</li>\n";
                    
                    $count++;
                }
            }
        }
        
        // before we wrap this up, let's check for
        // lookup tables related to this form
        $tables = $ci->db->list_tables();
        foreach($tables as $t)
        {
            // related tables will include original table's name
            // but we obviously want to exclude the original table,
            // already joined tables and bridge tables
            if(strpos($t,$table) !== FALSE AND $t != $table AND !in_array($t, $joined_tables) AND strpos($t,'_bridge') === FALSE)
            {
                $lookup_field_output = "<ul>\n";
                $q = $ci->db->get($t);
                if($q->num_rows() > 0)
                {
                    $bridged = array();
                            
                    // if db record passed, check bridge table for relational data
                    if(isset($db_record['id']) AND $ci->db->table_exists($t."_bridge"))
                    {
                        // get bridged records
                        $bridge = $ci->db->where($table[0]."id",$db_record['id'])->get($t."_bridge");
                        if($bridge->num_rows() > 0)
                        {
                            $bridged = array();
                            foreach($bridge->result() as $b)
                            {
                                $bridged[] = $b->id;
                            }
                        }
                    }
                    
                    foreach($q->result() as $cb)
                    {
                        $checked = (in_array($cb->id,$bridged)) ? 'checked="checked" ' : '';
                        $lookup_field_output .= '<li>&lt;input type="checkbox" value="'.$cb-&gt;id.'" class="checkbox" '.$checked.'/><span>'.$cb->name.'</span></li>'."\n";
                    }
                    $lookup_field_output .= "</ul>\n";
                
                    $fields[$count] = "<li>\n";
                    $fields[$count] .= "<label>".ucwords(str_replace('_',' ',ltrim(ltrim($t,$table),'_')))."</label>\n";
                    $fields[$count] .= "<div>\n".$lookup_field_output."\n</div>\n";
                    $fields[$count] .= "</li>\n";
                
                    $count++;
                }
            }
        }
        
        // ok, we're done
        $output = "\n<ul>\n";
        $output .= implode("\n",$fields);
        $output .= "\n".'<li class="btnHolder">&lt;input type="submit" value="Submit" /&gt;&lt;/li>'."\n";
        $output .= '</ul>';
        
        return $output;
        
    }

}

// Modified form_dropdown() helper function

function form_dropdown($name = '', $options = array(), $selected = array(), $extra = '')
{
    if ( ! is_array($selected))
    {
        $selected = array($selected);
    }

    if ($extra != '') $extra = ' '.$extra;

    $multiple = (count($selected) > 1 && strpos($extra, 'multiple') === FALSE) ? ' multiple="multiple"' : '';

    $form = '<select name="'.$name.'"'.$extra.$multiple.">\n";
    
    // check if $options is an object, if not, proceed with array
    if(!is_object($options))
    {
        // create first blank option
        $form .= '<option value=""></option>'."\n";
        
        foreach ($options as $key => $val)
        {
        
            $key = (string) $key;
        
            if (is_array($val))
            // optgroup (multidimensional array passed)
            {
                $form .= '<optgroup label="'.$key.'">'."\n";

                foreach ($val as $optgroup_key => $optgroup_val)
                {
                    $sel = (in_array($optgroup_key, $selected)) ? ' selected="selected"' : '';

                    $form .= '<option value="'.$optgroup_key.'"'.$sel.'>'.(string) $optgroup_val."</option>\n";
                }

                $form .= '</optgroup>'."\n";
            }
            // no optgroup
            else
            {
                $sel = (in_array($key, $selected)) ? ' selected="selected"' : '';

                $form .= '<option value="'.$key.'"'.$sel.'>'.(string) $val."</option>\n";
            }
        }
    }
    // $options is an object (likely a DB object)
    else
    {
        // create first blank option element
        $form .= '<option value=""></option>'."\n";
        
        foreach ($options->result_array() as $row)
        {
            /**
             * need to get first 2 values in row's associative array.
             * in order to do so we must first get the keys of the array,
             * and use them later to reference numeric positions.
             */
            $row_keys = array_keys($row);
            
            // check if the first value in the array (likely the row id) is in the $selected array
            $sel = (in_array($row[$row_keys[0]], $selected)) ? ' selected="selected"' : '';
            
            $option_value = $row[$row_keys[0]];
            $option_display = $row[$row_keys[1]];
            
            $form .= '<option value="'.$option_value.'"'.$sel.'>'.$option_display."</option>\n";
        }
    }

    $form .= '</select>';

    return $form;
}