Skip to content

Auto Form Fields

Jump to bottom Edit New page
World Wide Web Server edited this page Jul 4, 2012 · 9 revisions

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): [code]

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 ;

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; [/code]

Example usage: [code] // 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()?> [/code]

And the helper:

[code] 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
    $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 .= '&lt;input type="'.($col-&gt;name == 'pass' ? 'password' : 'text').'" name="'.$col->name.'" value="'.$field_db_record.'" class="" />';
            }
            elseif($col->type == 'text')
            // TEXT
            {
                $field_output .= '&lt;textarea name="'.$col-&gt;name.'" class="">'.$field_db_record.'&lt;/textarea&gt;';
            }
            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 .= '&lt;input type="text" name="'.$col-&gt;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;

} [/code]

Add a custom footer
Clone this wiki locally