-
Notifications
You must be signed in to change notification settings - Fork 0
Generate a form from a DB table
This depends on XAJAX CI library. If you don't want to use that then it should be simple to eliminate this dependency.
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] = '
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] = '
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:
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;
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]