Skip to content

Generate a form from a DB table

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

This depends on XAJAX CI library. If you don't want to use that then it should be simple to eliminate this dependency.

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:

[code] /**

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] = '

' .anchor('#','Make Unmanaged','onclick="xajax_toggleManagement(''.$table.'',''.$field.'');return false;"') .'
'; }else{ $displayFields[$field][$field] = '
' .anchor('#','Make Managed','onclick="xajax_toggleManagement(''.$table.'',''.$field.'');return false;"') .'
'; } } } $objResponse = new xajaxResponse(); $objResponse->addAssign($table."manageFields","innerHTML", ul($displayFields)); return $objResponse->getXML(); } [/code]

The _addTabletoManagementTable function in an internal function called using xajax:

[code] /**

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

Simple xajax toggle for table management

[code] /**

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

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

[code] /**

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] = '

' .anchor('#','Make Unmanaged','onclick="xajax_toggleManagement(''.$table.'');return false;"') .'
' .'
' .anchor('#','Manage Fields','onclick="xajax_ManageFields(''.$table.'');return false;"') .'
'; }else{ $displayTables[$table][$table] = '
' .anchor('#','Make Managed','onclick="xajax_toggleManagement(''.$table.'');return false;"') .'
' .'
' .'
'; } }else{ $displayTables[$table][$table] = '' .anchor('#','Add to Management_tables table','onclick="xajax_addTableToManagementTable(''.$table.'');return false;"') .''; } } } $data['xajax_js'] = $this->xajax->getjavascript(null, '/_js/xajax.js'); $data['content'] = ul($displayTables); $this->load->view('managetables',$data); } [/code]

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.

[code] /**

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; } $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": case "year": $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; } [/code]

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

[code]

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

My simple view for the "managetables" function:

[code]

<html xmlns="http://www.w3.org/1999/xhtml"> <head> <?=$xajax_js;?> </head> <body> <?=$content;?> </body> </html> [/code]

Add a custom footer

Clone this wiki locally