Generate a form from a DB table

Derek Jones edited this page Jul 5, 2012 · 9 revisions
Clone this wiki locally

As is, this depends on XAJAX CI library. If you just want to produce forms based on DB tables then use the “_formBuilder” function by itself by commenting-out/removing one line of code for it to be independent (there is a comment beside the line in the code below).

This code is released under the same license under which CI is released.

How to use this code:

1)Copy the functions below into your controller, 2)Create the tables described in the SQL towards the bottom of this page, 3)Create a view named "managetables" and paste the view code from the bottom of this page into it. 4)??? 5)Profit!

Here are the functions required:

The _manageFields function is an internal function called using xajax:


/**
 * @author        Bob Beauchamp
 * @link        http://www.codeigniter.com/wiki/Generate_a_form_from_a_DB_table/
 */

function _manageFields($table){
  $table = $this->input->xss_clean($table);
  $fields = $this->db->list_fields($table);
  foreach($fields as $field){
    $fieldQuery = $this->db->getWhere('Management_fields',array('FieldName' => $field, 'InTable' => $table));
    if($fieldQuery->num_rows() > 0){
      $fieldRow = $fieldQuery->result();
      $fieldRow = $fieldRow[0];
      if($fieldRow->AllowManagement){
        $displayFields[$field][$field] = '<div id="'.$field.'manageToggle">'
                                        .anchor('#','Make Unmanaged','onclick="xajax_toggleManagement(\''.$table.'\',\''.$field.'\');return false;"')
                                        .'</div>';
      }else{
        $displayFields[$field][$field] = '<div id="'.$field.'manageToggle">'
                                        .anchor('#','Make Managed','onclick="xajax_toggleManagement(\''.$table.'\',\''.$field.'\');return false;"')
                                        .'</div>';
      }
    }
  }
  $objResponse = new xajaxResponse();
  $objResponse->addAssign($table."manageFields","innerHTML", ul($displayFields));
  return $objResponse->getXML();
}

The _addTabletoManagementTable function in an internal function called using xajax:


/**
 * @author        Bob Beauchamp
 * @link        http://www.codeigniter.com/wiki/Generate_a_form_from_a_DB_table/
 */

function _addTableToManagementTable($table){
  $table = $this->input->xss_clean($table);
  $tableQuery = $this->db->getWhere('Management_tables',array('TableName' => $table));
  if($tableQuery->num_rows() == 0){
    $dbInsert = array('TableName'       => $table,
                      'AllowManagement' => 1);
    $this->db->insert('Management_tables',$dbInsert);
    $fields = $this->db->list_fields($table);
    foreach($fields as $field){
      $dbInsert = array('FieldName'       => $field,
                        'InTable'         => $table,
                        'AllowManagement' => 1);
      $this->db->insert('Management_fields',$dbInsert);
    }
  }
  $display = anchor('#','Make Unmanaged','onclick="xajax_toggleManagement(\''.$table.'\');return false;"');
  $objResponse = new xajaxResponse();
  $objResponse->addAssign($table."manageToggle","innerHTML", $display);
  return $objResponse->getXML();
}

Simple xajax toggle for table management


/**
 * @author        Bob Beauchamp
 * @link        http://www.codeigniter.com/wiki/Generate_a_form_from_a_DB_table/
 */

function _toggleManagement($table,$field = FALSE){
  $objResponse = new xajaxResponse();
  if(!$field){
    $tableQuery = $this->db->getWhere('Management_tables',array('TableName' => $table));
    if($tableQuery->num_rows() > 0){
      $tableRow = $tableQuery->result();
      $tableRow = $tableRow[0];
      if($tableRow->AllowManagement){
        $this->db->set('AllowManagement',0);
        $display = anchor('#','Make Managed','onclick="xajax_toggleManagement(\''.$table.'\');return false;"');
        $objResponse->addAssign($table.'manageFields',"innerHTML", '');
      }else{
        $this->db->set('AllowManagement',1);
        $display = anchor('#','Make Unmanaged','onclick="xajax_toggleManagement(\''.$table.'\');return false;"');
        $fieldDisplay = anchor('#','Manage Fields','onclick="xajax_ManageFields(\''.$table.'\');return false;"');
        $objResponse->addAssign($table.'manageFields',"innerHTML", $fieldDisplay);
      }
      $this->db->where('TableName',$table);
      $this->db->update('Management_tables');
    }
    $objResponse->addAssign($table."manageToggle","innerHTML", $display);
  }else{
    $fieldQuery = $this->db->getWhere('Management_fields',array('FieldName' => $field, 'InTable' => $table));
    if($fieldQuery->num_rows() > 0){
      $fieldRow = $fieldQuery->result();
      $fieldRow = $fieldRow[0];
      if($fieldRow->AllowManagement){
        $this->db->set('AllowManagement',0);
        $display = anchor('#','Make Managed','onclick="xajax_toggleManagement(\''.$table.'\',\''.$field.'\');return false;"');
      }else{
        $this->db->set('AllowManagement',1);
        $display = anchor('#','Make Unmanaged','onclick="xajax_toggleManagement(\''.$table.'\',\''.$field.'\');return false;"');
      }
      $this->db->where('FieldName',$field);
      $this->db->where('InTable',$table);
      $this->db->update('Management_fields');
    }
    $objResponse = new xajaxResponse();
    $objResponse->addAssign($field."manageToggle","innerHTML", $display);
  }
  return $objResponse->getXML();
}

This is the function you want to reference from your browser in order to start the magic rolling.


/**
 * @author        Bob Beauchamp
 * @link        http://www.codeigniter.com/wiki/Generate_a_form_from_a_DB_table/
 */

function managetables(){
  $this->load->library('xajax');
  $this->xajax->registerFunction(array('toggleManagement',&$this,'_toggleManagement'));
  $this->xajax->registerFunction(array('addTableToManagementTable',&$this,'_addTableToManagementTable'));
  $this->xajax->registerFunction(array('ManageFields',&$this,'_ManageFields'));
  $this->xajax->processRequests();

  $tables = $this->db->list_tables();
  foreach($tables as $table){
    if($table != 'Management_tables' && $table != 'Management_fields'){
      $tableQuery = $this->db->getWhere('Management_tables',array('TableName' => $table));
      if($tableQuery->num_rows() > 0){
        $tableRow = $tableQuery->result();
        $tableRow = $tableRow[0];
        if($tableRow->AllowManagement){
          $displayTables[$table][$table] = '<div id="'.$table.'manageToggle">'
                                          .anchor('#','Make Unmanaged','onclick="xajax_toggleManagement(\''.$table.'\');return false;"')
                                          .'</div>'
                                          .'<div id="'.$table.'manageFields">'
                                          .anchor('#','Manage Fields','onclick="xajax_ManageFields(\''.$table.'\');return false;"')
                                          .'</div>';
        }else{
          $displayTables[$table][$table] = '<div id="'.$table.'manageToggle">'
                                          .anchor('#','Make Managed','onclick="xajax_toggleManagement(\''.$table.'\');return false;"')
                                          .'</div>'
                                          .'<div id="'.$table.'manageFields">'
                                          .'</div>';
        }
      }else{
        $displayTables[$table][$table] = '<span id="'.$table.'manageToggle">'
                                         .anchor('#','Add to Management_tables table','onclick="xajax_addTableToManagementTable(\''.$table.'\');return false;"')
                                         .'</span>';
      }
    }
  }
  $data['xajax_js'] = $this->xajax->getjavascript(null, '/_js/xajax.js');
  $data['content'] = ul($displayTables);
  $this->load->view('managetables',$data);
}

I have made this an internal function so I can just call it from other functions and have it return the basic form elements. Can work as a url-addressable function with some minor tweaks. This function depends on the table being passed into to already being in the management tables (see SQL code below). I have debated making it automatically suck the table and fields into the management tables but have not yet taken that step since I like to be a bit more hands-on with this kind of thing.


/**
 * @author        Bob Beauchamp
 * @link        http://www.codeigniter.com/wiki/Generate_a_form_from_a_DB_table/
 */

function _formBuilder($table,$values=array(),$valuesAsHidden=FALSE){
  $columns = $this->db->query($this->db->_list_columns($table));
  if($columns->num_rows() > 0){
    foreach ($columns->result_array() as $columnInfo){
      foreach($columnInfo as $key => $val){
        if($key == 'Field'){
          $fieldName = $val;
          if(!$this->data->getManagementStatus($table,$fieldName)) break;  //comment this out if you are using only this function and not the supporting "management" fuctions
        }
        $arrTableInfo[$fieldName][$key] = $val;
      }
    }
    $formDisplay = '';
    foreach($arrTableInfo as $fieldName => $arrInfo){
      $arrFormField = '';
      if(isset($values[$fieldName])){
        $arrFormField[$fieldName] = $values[$fieldName];
        $formFunction = 'form_hidden';
      }else{
        if($fieldName == 'InCity' && $table == 'minisites'){
          $selectName = $fieldName;
          $selectExtra = ' class="cms_form_dropdown"';
          $retval[$fieldName] = form_dropdown($selectName,$this->data->getCityArray(),'',$selectExtra);
          continue;
        }
        $arrFormField['name'] = $fieldName;
        $arrFormField['id']   = $fieldName;
        foreach($arrInfo as $key => $val){
          switch($key){
           case 'Type':
             $formType = explode('(',$val);
             switch($formType[0]){
              case 'varchar': //always has a size
              case "char":
                $formFunction = 'form_input';
                $arrFormField['maxlength'] = str_replace(')','',$formType[1]);
                break;
              case "tinyint":
              case "smallint":
              case "mediumint":
              case "int":
              case "bigint":
                $formFunction = 'form_input';
                $numericInfo = explode(' ',$formType[1]);
                $arrFormField['maxlength'] = str_replace(')','',$numericInfo[0]);
                if(isset($numericInfo[1])){
                  $validationExtras = $numericInfo[1];
                }
                break;
              case "datetime":
              case "timestamp":
                $formFunction = 'form_input';
                $arrFormField['maxlength'] = '19';
                break;
              case "float":
                $formFunction = 'form_input';
                $arrFormField['maxlength'] = '23';
                break;
              case "double":
                $formFunction = 'form_input';
                $arrFormField['maxlength'] = '53';
                break;
              case "decimal":
                $formFunction = 'form_input';
                $arrFormField['maxlength'] = '64';
                break;
              case "date":
                $formFunction = 'form_input';
                $arrFormField['maxlength'] = '10';
                break;
              case "year":
                $formFunction = 'form_input';
                $arrFormField['maxlength'] = '4';
                break;
              case "time":
                $formFunction = 'form_input';
                $arrFormField['maxlength'] = '8';
                break;
              case 'tinytext':
                $formFunction = 'form_input';
                if(isset($formType[1])){
                  $arrFormField['maxlength'] = str_replace(')','',$formType[1]);
                }else{
                  $arrFormField['maxlength'] = '255';
                }
                break;
              case 'text':
                $formFunction = 'form_textarea';
                if(isset($formType[1])){
                  $arrFormField['maxlength'] = str_replace(')','',$formType[1]);
                }else{
                  $arrFormField['maxlength'] = '65535';
                }
                break;
              case 'mediumtext':
              case 'longtext':
                $formFunction = 'form_textarea';
                if(isset($formType[1])){
                  $arrFormField['maxlength'] = str_replace(')','',$formType[1]);
                }else{
                  $arrFormField['maxlength'] = '16777215';
                }
                break;
              case 'set':
                $formFunction = 'form_dropdown';
                $selectExtra = 'multiple="multiple"';
                $setVals = explode(',',$val);
                $totalVals = count($setVals);
                $setVals[0] = str_replace("set('",'',$setVals[0]);
                $setVals[($totalVals - 1)] = str_replace("')",'',$setVals[($totalVals - 1)]);
                foreach($setVals as $key => $val){
                  $setVals[$key] = str_replace("'",'',$setVals[$key]);
                  $arrFormField[$setVals[$key]] = $setVals[$key];
                }
                break;
              case 'enum':
                $formFunction = 'form_dropdown';
                $selectExtra = '';
                $enumVals = explode(',',$val);
                $totalVals = count($enumVals);
                $enumVals[0] = str_replace("enum('",'',$enumVals[0]);
                $enumVals[($totalVals - 1)] = str_replace("')",'',$enumVals[($totalVals - 1)]);
                $arrFormField['selectone'] = 'Select One';
                foreach($enumVals as $key => $val){
                  $enumVals[$key] = str_replace("'",'',$enumVals[$key]);
                  $arrFormField[$enumVals[$key]] = $enumVals[$key];
                }
                break;
             }
             break;
           case 'Null':
             //todo: should there be a "required" validation rule based on this?
             //      currently I'm leaning towards "no" and making them specify
             //      validation rules separately and deliberately
             break;
           case 'Key':
             if($val != ''){
               if(@$validationExtras != ''){
                 $validationExtras .= '|'.$val;
               }else{
                 $validationExtras = $val;
               }
             }
             break;
           case 'Default':
             //$arrFormField['value'] = $val; //considering leaving this out since the default is going
             //to be populated if there is no value.  Would have to unset
             //the value before inserting/updating the DB record
             break;
           case 'Extra':
             break;
          }
        }
      }
      if($formFunction != "form_dropdown"){
        if($formFunction != 'form_hidden'){
          $arrFormField['class'] = 'css_'.$formFunction;
        }
        $retval[$fieldName] = $formFunction($arrFormField);
      }else{
        $selectName = $arrFormField['name'];
        unset($arrFormField['name']);
        unset($arrFormField['id']);
        $selectExtra .= ' class="css_'.$formFunction.'"';
        $retval[$fieldName] = $formFunction($selectName,$arrFormField,'selectone',$selectExtra);
      }
    }
  }else{
    $retval = FALSE;
  }
  return $retval;
}

Here is the SQL (exported from mySQL) to create the management tables:


-- 
-- Table structure for table `Management_fields`
-- 

CREATE TABLE `Management_fields` (
  `FieldName` varchar(255) NOT NULL,
  `InTable` varchar(255) NOT NULL,
  `AllowManagement` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`FieldName`,`InTable`)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table `Management_tables`
-- 

CREATE TABLE `Management_tables` (
  `TableName` varchar(255) NOT NULL,
  `AllowManagement` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`TableName`)
) TYPE=MyISAM;

My simple view for the "managetables" function:


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
&lt;html xmlns="http://www.w3.org/1999/xhtml"&gt;
&lt;head&gt;
&lt;?=$xajax_js;?&gt;
&lt;/head&gt;
&lt;body&gt;
&lt;?=$content;?&gt;
&lt;/body&gt;
&lt;/html&gt;