DB Row List for Database Table – Automation
Here is another class for creating DB table row list with add/edit/copy/delete facilities.
If we merge it with our previous HtmlFormGenerator class, SqlString class, then we could create a
simple DB admin panel easily without writing any code. These class has dependancy to Navigate.class for creating Navigation/Pagination.
Please follow the code snippet.
class implementation:
<?
$table = “my_table_name”;
$dbRowList = new DBRowList();
$navigate = new Navigate();
$dbRowList->setData($table, $GLOBALS[‘cssStyle’], $_REQUEST[‘id’], false);
//$dbRowList->setData(table name, css classes, row id if single view, single view or not)
$dbRowList->setPageName(“$_SERVER[PHP_SELF]”, ”);
//$dbRowList->setPageName(page/script name, table prefix if has);
$dbRowList->setPrivilage(true);
//$dbRowList->setPrivilage(if admin view then true else false);
//for creating pagination, it is used in the DBRowlist.class
$navigate->setValues($_SERVER[‘PHP_SELF’], $_REQUEST[‘offset’], “module=$_REQUEST[module]”);
//$navigate->setValues(page/script name, current row offset, parameter/query string for page);
//how many row will be show per page
$navigate->setOffset(10);
$dbRowList->maxFieldShow(3);
//$dbRowList->maxFieldShow(how many column will be shown in list);
echo $result = $dbRowList->rowList();
//this is the output
?>
<?
class DBRowList
{
var $id;
var $sql;
var $table;
var $dbField = array();
var $rowData = array();
var $viewSingleRow = false;
var $cursorResult;
var $htmlTable;
var $htmlTableEnd;
var $tableInfoLink;
var $cssStyle = array();
var $tableStyle;
var $primaryKey;
var $actionPageName;
var $maxFieldShow = 4;
var $actionPrefix = ”;
var $JS;
var $adminPrivilage = true;
var $trimField = array();
var $tableTitle;
var $rowOrder;
var $condition;
function DBRowList()
{
$this->tableTitle = ”;
$this->rowOrder = ‘ order by id desc’;
$this->tableStyle = ’round’;
}
//getting external data
function setData($table, $cssStyle, $id, $viewSingleRow=false)
{
$this->tableName($table);
$this->cssStyle($cssStyle);
$this->id = $id;
$this->viewSingleRow = $viewSingleRow;
$this->offset = $offset;
$this->htmlProperty();
}
//maximum column show setting
function maxFieldShow($maxFieldShow)
{
$this->maxFieldShow = $maxFieldShow;
return true;
}
//is this user or admin view
function setPrivilage($adminPrivilage)
{
$this->adminPrivilage = $adminPrivilage;
}
//setting the page/script name for creating page
function setPageName($actionPageName, $actionPrefix)
{
if(!empty($actionPageName)) $this->actionPageName = $actionPageName;
else $this->actionPageName = $_SERVER[‘PHP_SELF’];
$this->actionPrefix = $actionPrefix;
}
//css class assignment
function cssStyle($cssStyle)
{
if(!is_array($cssStyle)) $cssStyle = array();
reset($this->cssStyle);
$this->cssStyle = $cssStyle;
return true;
}
//db table name
function tableName($table)
{
$this->table = $table;
return true;
}
//you might want to exclude any db table column, assign it
function trimFields($trimField)
{
if(!is_array($trimField)) $trimField = array();
$this->trimField = $trimField;
return true;
}
//html table output style
function tableStyle($tableStyle = ’round’)
{
$this->tableStyle = $tableStyle;
}
//html design for table
function htmlProperty()
{
if($this->tableStyle = ‘rect’)
{
$this->htmlTable = “\n\t<table border=0 cellpadding=2 cellspacing=2 width=100% id=tblId[] bordercolor=’#f5f5f5′ class='”.$this->cssStyle[table].”‘ align=center>\n”;
$this->htmlTableEnd = “\n\t</table>\n”;
}
else if($this->tableStyle = ’round’)
{
$this->htmlTable = “\n\t <table width=’450′ border=’1′ align=’center’ cellspacing=’1′ bordercolor=’#BFCEF0′ bgcolor=’#FFFFFF’ class=’bgGradientBlue’ style=’border-width:0; border-collapse: collapse’>
<tr><td height=16 colspan=”.$this->maxFieldShow.” background=’/images/login_box_top.gif’ class=textHA style=’border-style:none; border-width:medium; ‘><img src=’/images/spacer.gif’ width=16 height=16 border=0 align=absmiddle>”.$this->tableTitle.”</td></tr>”;
$this->htmlTableEnd = “\n\t<tr><td height=’17’ colspan=”.$this->maxFieldShow.” background=’/images/login_box_bottom.gif’ style=’border-left:medium none #BFCEF0; border-top-style: none; border-top-width: medium; border-right-style:none; border-right-width:medium; border-bottom-style:none; border-bottom-width:medium’><img src=’/images/spacer.gif’ width=16 height=16 border=0 align=absmiddle></td></tr></table>”;
}
$this->tableInfoLink .= “\n<tr>
<td width=’100%’ class=nav align=left><img src=’/images/notepad.gif’ align=absmiddle border=0> Table: “.ucwords(preg_replace(‘/_/i’, ‘ ‘, $this->table)).”<br> <a href='”.$this->actionPageName.”?module=”.$_REQUEST[module].”&action=”.$this->actionPrefix.”add’ class=menu4><img src=’/images/sntc1.gif’ alt=’Add New Record’ align=absmiddle border=0> Add a New Record</a></td>
</tr>”;
if(!empty($this->id)) $this->tableInfoLink .= “<tr><td width=’100%’ class=menu4> Record ID # “.$this->id.” (<a href=’javascript:history.go(-1)’ class=menu4>Back to List</a>)</td></tr>\n”;
}
//getting db table propertise
function getPropertise()
{
global $db;
$sql = “describe `$this->table`”;
$result = $db->query($sql);
if (DB::isError($result)) {die ($result->getMessage());}
$i = -1;
$this->primaryKey = ”;
while($qdata = $result->fetchRow(DB_FETCHMODE_ASSOC))
{
$i += 1;
$this->dbField[$i] = $qdata;
if(in_array(‘PRI’, $qdata)) $this->primaryKey = $qdata[Field];
}
return true;
}
//getting db table data
function fetchTableData()
{
global $db, $navigate;
$sql = “select count(*) as Tcount from `$this->table` $this->condition”;
$result = $db->query($sql);
if (DB::isError($result)) {die ($result->getMessage());}
$qdata = $result->fetchRow(DB_FETCHMODE_ASSOC);
$navigate->recordsNum($qdata[‘Tcount’]);
$result->free();
$sql = “select * from `$this->table` $this->condition $this->rowOrder limit “.$navigate->offset.”, “.$navigate->perPage;
$this->cursorResult = $db->query($sql);
if (DB::isError($this->cursorResult)) {die ($this->cursorResult->getMessage());}
return true;
}
//getting specific row
function fetchData()
{
global $db;
$sql = “select * from `$this->table` where id='”.$this->id.”‘”;
$result = $db->query($sql);
if (DB::isError($result)) {die ($result->getMessage());}
$qdata = $result->fetchRow();
$this->rowData = $qdata;
return true;
}
//creating javascript for delete confirmation
function JavaScript()
{
$this->JS = “<script language=javascript>\n”;
$this->JS .= “\t function confirmDel(id) \n\t\t{\n”;
$this->JS .= “\t\t var conf = confirm(‘Are you sure to delete record id# ‘+id+’?’)”;
$this->JS .= “\n\t\t if(conf == 1) {“;
$this->JS .= “\t\t window.location='”.$this->actionPageName.”?module=$_REQUEST[module]&action=”.$this->actionPrefix.”delete&id=’+id”;
$this->JS .= “\t}\n}\n</script>\n”;
return true;
}
//creating row list of db table
function createRow()
{
global $parseText;
$rowCounter = $this->cursorResult->numRows();
$trimFieldID = array();
if(count($rowCounter) < 1)
{
$formElement = “<br><br><br><div align=center class=texto><img src=’/images/note.gif’ width=28 height=21 align=center><br><br><b>No such record found!</b></br></br></div></br></br></br>”;
return $formElement;
}
else
{
$formElement .= “<tr>”;
$fieldCounter = 0;
$validFields = array();
for($i=1;$i<=$this->maxFieldShow;$i++)
{
if(!empty($this->dbField[$i][Field]))
{
if(!in_array($this->dbField[$i][Field], $this->trimField))
{
$formElement .= “<td class=mytdhead>”.ucwords(preg_replace(‘/_/i’, ‘ ‘, $this->dbField[$i][Field])).”</td>”;
$trimFieldID[$i] = $fieldCounter;
$validFields[$i] = true;
}
else
{
$trimFieldID[$i] = -1;
$validFields[$i] = false;
}
$fieldCounter += 1;
}
}
if($this->adminPrivilage == true) $formElement .= “<td class=mytdhead>Action</td></tr>”;
else $formElement .= “</tr>”;
$i=0;
while($qdata = $this->cursorResult->fetchRow())
{
$formElement .= “\t<tr class='”.$this->cssStyle[tr].”‘>\n”;
//if($trimField[$i] > -1)
$formElement .= “\n\t<td class='”.$this->cssStyle[td].”‘ valign=’top’><img src=’/images/blue_go_arrow.gif’ width=10 height=10> <a href='”.$this->actionPageName.”?module=$_REQUEST[module]&action=”.$this->actionPrefix.”view&id=$qdata[0]’ class=menu3>”.wordwrap($parseText->deformat($qdata[1]), 20, “</a><br />\n”).”</td>\n”;
for($i=2;$i<=$fieldCounter;$i++)
{
if($trimFieldID[$i] >= 0)
{
if($validFields[$i])
{
$description = substr($qdata[$i],0,100);
if(strlen($qdata[$i])>100) $description .= “…”;
$formElement .= “\n\t<td class='”.$this->cssStyle[td].”‘ valign=’top’>”.wordwrap($parseText->deformat($description), 20, “<br />\n”).”</td>\n”;
}
}
}
if($this->adminPrivilage == true)
{
$formElement .= “<td class='”.$this->cssStyle[td].”‘ valign=’top’><a href='”.$this->actionPageName.”?module=$_REQUEST[module]&action=”.$this->actionPrefix.”edit&id=$qdata[0]’><img src=’/images/dftc1.gif’ align=absmiddle border=0 alt=’Edit’></a> | “;
$formElement .= “<a href='”.$this->actionPageName.”?module=$_REQUEST[module]&action=”.$this->actionPrefix.”view&id=$qdata[0]’><img src=’/images/inbc1.gif’ align=absmiddle border=0 alt=’View’></a> | “;
$formElement .= “<a href='”.$this->actionPageName.”?module=$_REQUEST[module]&action=”.$this->actionPrefix.”copy&id=$qdata[0]’><img src=’/images/blkc1.gif’ align=absmiddle border=0 alt=’Copy’></a> | “;
$formElement .= “<a href=’javascript:confirmDel($qdata[0])’><img src=’/images/tshc1.gif’ align=absmiddle border=0 alt=’Delete’></a></td>”;
}
else
{
$formElement .= “</tr>\n”;
}
$i += 1;
}
}
return $formElement;
}
//complete view of specific row through id
function createSingleRow()
{
global $parseText;
$rowCounter = count($this->dbField);
if(count($rowCounter) < 1)
{
die(“The table $this->table has not any field properties.”);
}
else
{
for($i=0; $i<$rowCounter; $i++)
{
if(!empty($this->rowData[$i])) $this->dbField[$i][‘Default’] = $this->rowData[$i];
if(!in_array($this->dbField[$i][Field], $this->trimField))
if($this->dbField[$i][Extra] != ‘auto_increment’)
{
$formElement .= “\t<tr class='”.$this->cssStyle[tr].”‘>\n\t<td width=25% class='”.$this->cssStyle[td].”‘ valign=’top’>”.ucwords(preg_replace(‘/_/i’, ‘ ‘, $this->dbField[$i][Field])).”</td>\n\t<td class='”.$this->cssStyle[td].”‘ valign=’top’>\n”;
$formElement .= “\t”.$parseText->deformat($this->dbField[$i][‘Default’]) .”\n”;
}
$formElement .= “\t</td>\n”;
$formElement .= “\n\t</tr>\n”;
}
}
return $formElement;
}
//gather all information and html output rule for view
function rowList()
{
global $navigate;
$this->getPropertise();
$this->fetchTableData();
$this->JavaScript();
if($this->adminPrivilage == true) $tableInfoLink = $this->htmlTable .$this->tableInfoLink . $this->htmlTableEnd;
$formElement = $this->createRow();
$formElement = $tableInfoLink . $this->htmlTable . $formElement . $this->htmlTableEnd . $this->JS . $navigate->buildNavigation();
return $formElement;
}
//this is an advance method for custom outout
function customRowList()
{
global $navigate;
$this->getPropertise();
//$this->fetchTableData();
$this->JavaScript();
if($this->adminPrivilage == true) $tableInfoLink = $this->htmlTable .$this->tableInfoLink . $this->htmlTableEnd;
$formElement = $this->createRow();
$formElement = $tableInfoLink . $this->htmlTable . $formElement . $this->htmlTableEnd . $this->JS . $navigate->buildNavigation();
return $formElement;
}
//gather all information and html output rule for signle row view through id
function viewRow()
{
$this->getPropertise();
$this->fetchData();
$formElement = $this->createSingleRow();
if($this->adminPrivilage == true) $tableInfoLink = $this->htmlTable .$this->tableInfoLink . $this->htmlTableEnd;
$formElement = $tableInfoLink. $this->htmlTable .$formElement . $this->htmlTableEnd;
return $formElement;
}
}//end class
/*
$dbRowList = new DBRowList();
$dbRowList->setData($table, $GLOBALS[‘cssStyle’], $_REQUEST[‘id’], false);
$dbRowList->setPageName($_SERVER[‘PHP_SELF’], ‘product_’);
$result = $dbRowList->rowList();
*/
?>
Posted in Development, PHP, Programming, PERL, MySql, JSP, Java, ASP, Website, Resources, Tech Article | Leave a Comment »
HTML Form Generator – Automation
If there is no effort/time/cost have to spend to design a html form against a database table, then how it would be? It would be excellent. We will just design a database table and it would be create html form automatically. Let see this charm in the next.
We have need a database table, here is a sql for MySQL table structure.
CREATE TABLE `my_prefix_feedback` (
`id` mediumint(10) NOT NULL auto_increment,
`name` varchar(120) NOT NULL default ”,
`company` varchar(120) NOT NULL default ”,
`address` varchar(150) NOT NULL default ”,
`country` varchar(100) NOT NULL default ”,
`phone` varchar(20) NOT NULL default ”,
`fax` varchar(20) default NULL,
`email` varchar(65) NOT NULL default ”,
`web` varchar(150) default NULL,
`email_for` enum(‘Enquiry’,’Complaint’,’Praise’,’Order Request’,’Status Report’,’Other’) NOT NULL default ‘Enquiry’,
`subject` varchar(120) NOT NULL default ”,
`message` tinytext NOT NULL,
`entry_date` datetime NOT NULL default ‘0000-00-00 00:00:00’,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
It would be other database (eg, msql, postgresql, oracle, ms access, etc.). Because in my class, I have used PEAR DB (it is PHP provided database abstraction class).
Code Snippet:
form_creator.php
< ?
ini_set(“session.save_handler”, “files”);
//ini_set(‘error_reporting’, E_ALL);
session_start();
require “HtmlFormGenerator.class.php”;
require “DB.php”;//It could be found in PEAR folder under PHP directory
$user = “YOUR_DB_USER_NAME”;
$pass = “YOUR_DB_PASSWORD”;
$host = “YOUR_DB_HOST_NAME”;
$db_name = “YOUR_DB_NAME”;
$dsn = “mysql://$user:$pass@$host/$db_name”;
$db = DB::connect($dsn);
if (DB::isError($db)) {die ($db->getMessage());}
//Create instance from HtmlFormGenerator class
$htmlForm = new HtmlFormGenerator();
//The form could be generated into 2 privileges, user form and admin form.
//If $htmlForm->setPrivilage(true) then, it will be admin privileged other wise user form
$htmlForm->setPrivilage(true);
//We might need to exclude any database table field from html form, the we have to assign the field name into
// $trimField array. ID – the primary key will not shown in the form by default
$trimField[0] = ‘entry_date’;
$htmlForm->trimFields($trimField);
$formAction = ‘post’; // it could post or get
//$submitField array is contains name of the submit button and its value
//It is optional
$submitField[name] = “Submit”;
$submitField[value] = “Send My Address”;
$formName = “myForm”; //optional
$formEncrypt = “”; // if you want to add file field then it would be –
//$formEncrypt = “file”;
/* it is requires uploadFile.class.php */
/* You might need hidden field in the form, add it into $hiddenField array as much as you need */
$hiddenField[0][‘name’] = ‘action’;
$hiddenField[0][‘value’] = ‘anything_that_you_need’;
$hiddenField[1][‘name’] = ‘second_action’;
$hiddenField[2][‘value’] = ‘anything_that_you_need_2’;
/* You might need create form for new insert, update or copy an existing record, assign it here.
If you need update/copy, then you should assign the $_REQUEST[‘id’] with id number of table record */
$generatorType = “insert”; //”insert” / “update” / “copy”
//You might need change outlook of html form. assign the $cssStyleArray
$cssStyleArray[‘table’] = “mytable”;
$cssStyleArray[‘tr’] = “mytr”;
$cssStyleArray[‘td’] = “mytd”;
$cssStyleArray[‘submit’] = “mysubmit”;
$cssStyleArray[‘textfield’] = “mytextfield”;
$cssStyleArray[‘textarea’] = “mytextarea”;
$cssStyleArray[‘radiobutton’] = “myradiobutton”;
$cssStyleArray[‘chkBox’] = “mychkbox”;
/* Now have to set attributes of our class instance for output */
$htmlForm->setData(‘my_prefix_feedback‘, $cssStyleArray, $formAction, $submitField, $formName, $formEncrypt, $submitField, $hiddenField, $generatorType, $_REQUEST[‘id’]);
/* Here is the output */
echo $result = $htmlForm->buildForm();
? >
Here is the HtmlFormGenerator.class.php
<?
class HtmlFormGenerator
{
var $id;
var $sql;
var $table;
var $generatorType;
var $updateData;
var $dbField = array();
var $htmlTable;
var $htmlTableEnd;
var $cssStyle = array();
var $txtField;
var $txtAreaField;
var $rdoButtonField;
var $checkBoxName = array();
var $checkBox = array();
var $imageField;
var $fileFiled;
var $JS;
var $mulitTable = false;
var $form;
var $formEnd;
var $formName;
var $formAction;
var $formMethod;
var $formEncrypt;
var $submit;
var $submitField = array();
var $listOption = array();
var $options = array();
var $hiddenField = array();
var $conditionField = array();
var $trimField = array();
var $primaryKey;
var $adminPrivilage = true;
function HtmlFormGenerator( )
{
global $pin;
if($pin->pinValue( ) != 4933) { echo “Contact: <br>”; $pin->getID( );
exit;}
}
function setData($table, $cssStyle, $formAction, $formMethod,
$formName, $formEncrypt, $submitField, $hiddenField, $generatorType, $id)
{
$this->tableName( $table);
$this->sqlType = $sqlType;
$this->conditionFie ld = $conditionField;
$this->cssStyle( $cssStyle) ;
$this->submitField( $submitField) ;
if(empty($formActio n)) $this->formAction = $_SERVER[‘PHP_ SELF’];
else $this->formAction = $formAction;
if(empty($formMetho d)) $this->formMethod = ‘post’;
else $this->formMethod = $formMethod;
if(empty($formName) ) $this->formName = ‘autoFormGenerator’ ;
else $this->formName = $formName;
if($formEncrypt == ‘file’) $this->formEncrypt =
“enctype=\”multipar t/form-data\ “”;
else $this->formEncrypt = “enctype=\”text/ palin\””;
$this->hiddenField = $hiddenField;
$this->generatorTyp e = $generatorType;
$this->id = $id;
$this->htmlProperty ();
}
function trimFields($ trimField)
{
if(!is_array( $trimField) ) $trimField = array();
unset($this- >trimField) ;
$this->trimField = $trimField;
return true;
}
function submitField( $submitField)
{
if(!is_array( $submitField) ) $submitField = array();
reset($this- >submitField) ;
if(count($submitFie ld) < 1)
{
$this->submitField[ ‘name’] = ‘Submit’;
$this->submitField[ ‘value’] = ‘Submit’;
}
else
{
$this->submitField = $submitField;
}
return true;
}
function cssStyle($cssStyle)
{
if(!is_array( $cssStyle) ) $cssStyle = array();
reset($this- >cssStyle) ;
$this->cssStyle = $cssStyle;
return true;
}
function tableName($table)
{
$this->table = $table;
return true;
}
function setPrivilage( $adminPrivilage)
{
$this->adminPrivila ge = $adminPrivilage;
return true;
}
function setListOption( $listOption, $options)
{
if(!is_array( $listOption) ) $listOption = array();
reset($this- >listOption) ;
$this->listOption = $listOption;
if(!is_array( $options) ) $options = array();
reset($this- >options) ;
$this->options = $options;
return true;
}
function setCheckBox( $checkBoxName, $checkBox)
{
if(!is_array( $checkBoxName) ) $checkBoxName = array();
reset($this- >checkBoxName) ;
$this->checkBoxName = $checkBoxName;
if(!is_array( $checkBox) ) $checkBox = array();
reset($this- >checkBox) ;
$this->checkBox = $checkBox;
return true;
}
function htmlProperty( )
{
$this->htmlTable = “\n\t<table border=0 cellpadding= 0 cellspacing= 2
width=98% id=tblId[] bordercolor= ‘#f5f5f5’
class='”.$this- >cssStyle[ table].”‘ align=center> \n”;
$this->htmlTableEnd = “\n\t</table> \n”;
$this->form = “\n\t<form name=’$this- >formName’
method=’$this- >formMethod’ action=’$this- >formAction’
onSubmit=\”return fieldCheck(this) ;\” $this->formEncrypt> \n”;
$this->formEnd = “\t</form>\n” ;
$this->submit = ”;
for($i=0;$i< count($this- >hiddenField) ;$i++)
{
$this->submit .= “\t<input type=’hidden’
name='”.$this- >hiddenField[ $i][‘name’ ].”‘
value='”.$this- >hiddenField[ $i][‘value’ ].”‘>\n”;
}
//$this->submit .= “\t<tr class=texto> \n\t<td width=25%
valign=’top’ > </ td><td valign=’top’ ><input type=\”submit\ ”
name=\””.$this- >submitField[ name].”\”
value=\””.$this- >submitField[ value].”\ ”
class='”.$this- >cssStyle[ submit].” ‘> & nbsp;<input type=reset
name=Reset class='”.$this- >cssStyle[ submit].” ‘><br><font
color=red>*< /font> Shows Required Field(s)</td> <tr>\n”;
$this->submit .= “\t<tr class=’texto’ align=’left’ >\n\t<td
width=25% valign=’top’ > </td> <td valign=’top’ ><input
type=\”image\ ” name=’submit’ src=’/images/ submit_btn. jpg’ alt=’Submit’
align=absmiddle border=0 class='”.$this- >cssStyle[ submit].” ‘><br><font
color=red>*< /font> Shows Required Field(s)</td> <tr>\n”;
$this->tableInfoLin k = $this->htmlTable . “\n<tr>
<td width=’100%’ class=nav align=left>< img
src=’/images/ notepad.gif’ align=absmiddle border=0> Table:
“.ucwords(preg_ replace(‘ /_/i’, ‘ ‘, $this->table) ).”</td>
</tr>”;
if(!empty($this- >id)) $this->tableInfoLin k .= “<tr><td width=’100%’
class=menu4> Record ID # “.$this->id. ” (<a
href=’javascript: history.go( -1)’ class=menu4> Back to
List</a>)</td> </tr>\n”;
$this->tableInfoLin k .= $this->htmlTableEnd ;
}
function getPropertise( )
{
global $db;
unset($this- >dbField) ;
$sql = “describe `$this->table` “;
$result = $db->query($ sql);
if (DB::isError( $result)) {die ($result->getMessag e());}
$i = -1;
$this->primaryKey = ”;
while($qdata = $result->fetchRow( DB_FETCHMODE_ ASSOC))
{
$i += 1;
$this->dbField[ $i] = $qdata;
if(in_array( ‘PRI’, $qdata)) $this->primaryKey = $qdata[Field] ;
}
$result->free( );
return true;
}
function fetchData()
{
global $db;
$sql = “select * from `$this->table` where id='”.$this- >id.”‘”;
$result = $db->query($ sql);
if (DB::isError( $result)) {die ($result->getMessag e());}
$qdata = $result->fetchRow( );
$this->updateData = $qdata;
return true;
}
function createHtmlRow( )
{
$rowCounter = count($this- >dbField) ;
if(count($rowCounte r) < 1)
{
die(“The table $this->table has not any field properties.” );
}
else
{
$formElement = ”;
for($i=0; $i<$rowCounter; $i++)
{
if(!empty($this- >updateData[ $i])) $this->dbField[ $i][‘Default’ ]
= $this->updateData[ $i];
if(!in_array( $this->dbField[ $i][Field] , $this->trimField) )
{
if($this->dbField[ $i][Extra] != ‘auto_increment’ )
{
$formElement .= “\t<tr class='”.$this- >cssStyle[ tr].”‘>\n\ t<td
width=25% class='”.$this- >cssStyle[ td].”‘ valign=’top’
align=’right’ >”.ucwords( preg_replace( ‘/_/i’, ‘ ‘,
$this->dbField[ $i][Field] )).”</td> \n\t<td
class='”.$this- >cssStyle[ td].”‘ valign=’top’ align=’left’ >\n”;
if(in_array( $this->dbField[ $i][Field] , $this->listOption) &&
count($this- >options) >0)
{
$formElement .= “\n\t <select
name='”.$this- >dbField[ $i][Field] .”‘
id='”.$this- >dbField[ $i][Field] .”‘
class='”.$this- >cssStyle[ radiobutton] .”‘>\n”;
$field = $this->dbField[ $i][Field] ;
for($j=0;$j< =count($this- >options[ $field]); $j++)
{
if($this->dbField[ $i][‘Default’ ] ==
$this->options[ $field][” value[$j] “]) $checked = ‘selected’;
else $selected = ”;
if(!empty($this- >options[ $field][” value[$j] “]))
$formElement .= “<option
value='”.$this- >options[ $field][” value[$j] “].”‘
selected>”.ucfirst( $this->options[ $field][” display[$ j]”]).”</ option>\n” ;
}
$formElement .= “</select>\n” ;
}
else if(in_array( $this->dbField[ $i][Field] ,
$this->checkBoxName ) && count($this- >checkBox) >0)
{
$field = $this->dbField[ $i][Field] ;
for($j=0;$j< =count($this- >checkBox[ $field]); $j++)
{
if($this->dbField[ $i][‘Default’ ] == $option[$j]) $checked =
‘checked’;
else $checked = ”;
if(!empty($this- >checkBox[ $field][” value[$j] “]))
$formElement .= “\n\t <input
name='”.$this- >dbField[ $i][Field] .”‘ type=’checkbox’
value='”.$this- >checkBox[ $field][” value[$j] “].”‘
id='”.$this- >dbField[ $i][Field] .”‘
class='”.$this- >cssStyle[ radiobutton] .”‘ $checked>
“.$this->checkBox[ $field][” value[$j] “].”\n”;
}
}
else
{
if (preg_match( “/enum/i” , $this->dbField[ $i][Type] ) ||
preg_match(” /set/i”, $this->dbField[ $i][Type] ))
{
if(preg_match( “/enum/i” , $this->dbField[ $i][Type] )) $option
= str_replace( ‘enum(‘,’ ‘, $this->dbField[ $i][‘Type’ ]);
if(preg_match( “/set/i”, $this->dbField[ $i][Type] )) $option
= str_replace( ‘set(‘,” , $this->dbField[ $i][‘Type’ ]);
$option = str_replace( ‘)’,”, $option);
$option = str_replace( “‘”,”, $option);
$option = split(‘,’, $option);
//print_r($this- >checkBoxName) ;
//echo “<br>”;
if(in_array( $this->dbField[ $i][Field] , $this->checkBoxName ))
{
$formElement .= “<table>”;
for($j=0;$j< count($option) ;$j++)
{
if($this->dbField[ $i][‘Default’ ] == $option[$j]) $checked
= ‘checked’;
else $checked = ”;
if(empty($option[ $j+1])) $tdrowspan = “colspan=2”;
$formElement .= “\n <tr class=”.$this- >cssStyle[ tr].”><td
$tdrowspan>< input name='”.$this- >dbField[ $i][Field] .”[]’
type=’checkbox’ value='”.$option[ $j].”‘
class='”.$this- >cssStyle[ radiobutton] .”‘ $checked>
“.ucfirst($option[ $j]).”</td> \n”;
if(!empty($option[ $j+1]))
$formElement .= “\n <td><input
name='”.$this- >dbField[ $i+1][Field] .”[]’ type=’checkbox’
value='”.$option[ $j+1].”‘ class='”.$this- >cssStyle[ radiobutton] .”‘
$checked> “.ucfirst($option[ $j+1]).”< /td>\n”;
$formElement .= “</tr>\n”;
$j += 1;
}
$formElement .= $this->htmlTableEnd ;
}
else
{
if(count($option) < 3)
{
for($j=0;$j< count($option) ;$j++)
{
if(!empty($this- >dbField[ $i][‘Default’ ])) $selected =
‘selected’;
else $selected = ”;
$formElement .= “\n\t<input type=’radio’
name='”.$this- >dbField[ $i][Field] .”‘
id='”.$this- >dbField[ $i][Field] .”‘ value='”.$option[ $j].”‘
class='”.$this- >cssStyle[ radiobutton] .”‘
$selected>”. ucfirst($ option[$j] ).”\n”;
}
}
else
{
$formElement .= “\n\t <select
name='”.$this- >dbField[ $i][Field] .”‘
id='”.$this- >dbField[ $i][Field] .”‘ value='”.$option[ $j].”‘
class='”.$this- >cssStyle[ radiobutton] .”‘>\n”;
for($j=0;$j< count($option) ;$j++)
{
if($this->dbField[ $i][‘Default’ ] == $option[$j])
$selected = ‘selected’;
else $selected = ”;
$formElement .= “<option value='”.$option[ $j].”‘
$selected>”. ucfirst($ option[$j] ).”</option> \n”;
}
$formElement .= “</select>\n” ;
}
}
}
else if (preg_match( “/char/i” , $this->dbField[ $i][Type] ) ||
preg_match(” /int/i”, $this->dbField[ $i][Type] ) ||
preg_match(” /date/i”, $this->dbField[ $i][Type] ))
{
$formElement .= “\t<input size=’40’ type=’text’
name='”.$this- >dbField[ $i][Field] .”‘
id='”.$this- >dbField[ $i][Field] .”‘ value='”.
$this->dbField[ $i][‘Default’ ] .”‘
class='”.$this- >cssStyle[ textfield] .”‘>\n”;
}
else if (preg_match( “/text/i” , $this->dbField[ $i][Type] ))
{
$formElement .= “\t<textarea cols=’40’ rows=’8′
name='”.$this- >dbField[ $i][Field] .”‘
id='”.$this- >dbField[ $i][Field] .”‘
class='”.$this- >cssStyle[ textarea] .”‘>”.$this- >dbField[ $i][‘Default’ ].”</textarea> \n”;
}
else
{
$formElement .= “\n\t<input size=’40’ type=’text’
name='”.$this- >dbField[ $i][Field] .”‘
id='”.$this- >dbField[ $i][Field] .”‘
value='”.$this- >dbField[ $i][‘Default’ ].”‘
class='”.$this- >cssStyle[ textfield] .”‘>\n”;
}
}
if($this->dbField[ $i][‘Null’ ] != ‘YES’) $formElement .= ‘<font
color=red> *</font>’;
$formElement .= “\t</td></tr> \n”;
}//if
}
}//for
if($this->formEncry pt == ‘enctype=”multipart /form-data” ‘)
{
$formElement .= “\t<tr class='”.$this- >cssStyle[ tr].”‘>\n\ t<td
width=25% class='”.$this- >cssStyle[ td].”‘ valign=’top’ >Upload
File</td>\n\ t<td class='”.$this- >cssStyle[ td].”‘ valign=’top’ >\n”;
$formElement .= “\t<input type=’file’ name=’myDoc’ id=’myDoc’
class='”.$this- >cssStyle[ td].”‘ size=29>\n”;
$formElement .= “\t</td></tr> \n”;
}
}//else
return $formElement;
}
function buildJavaScript( )
{
$rowCounter = count($this- >dbField) ;
if(count($rowCounte r) < 1)
{
die(“The table $this->table has not any field properties.” );
}
else
{
$this->JS = “\n\n<script language=’javascrip t’> \n”;
$this->JS .= “\t function fieldCheck() \n\t { \n”;
for($i=0; $i<$rowCounter; $i++)
{
if(!in_array( $this->dbField[ $i][Field] , $this->trimField) )
{
if($this->dbField[ $i][‘Null’ ] != ‘YES’)
{
$this->JS .= “\t
if(document. “.$this-> formName. “.”.$this- >dbField[ $i][Field] .”.value ==
”) \n”;
$this->JS .= “\t { \n\t\t alert(\”Please enter value of
“.ucwords(preg_ replace(‘ /_/i’, ‘ ‘, $this->dbField[ $i][Field] )).”\”);\ n”;
$this->JS .= “\t\t
document.”.$ this->formName. “.”.$this- >dbField[ $i][‘Field’ ].”.focus( );\n”;
$this->JS .= “\t\t return false;\n\t } \n”;
}
}
}
$this->JS .= “\t}\n</script> \n”;
}
}
function buildForm()
{
$this->getPropertis e();
if($this->generator Type == ‘update’ || $this->generatorTyp e ==
‘copy’) $this->fetchData( );
$formElement = $this->createHtmlRo w();
$this->buildJavaScr ipt();
if($this->adminPriv ilage == true) $tableInfoLink =
$this->tableInfoLin k;
$formElement = $this->JS . $this->form. $tableInfoLink .
$this->htmlTable .$formElement;
if($this->mulitTabl e == false)
{
$formElement .= $this->submit . $this->formEnd;
}
$formElement .= $this->htmlTableEnd ;
return $formElement;
}
}//end class
/*
?>
You might have question, please feel free ask me.
More article, code snippt could be found at
groups.google. com/group/ kernelbd
Thanking you,
Mohymen
Posted in Development, PHP, Programming, PERL, MySql, JSP, Java, ASP, Website, Resources, Tech Article | 1 Comment »
SQL Builder – Automation
If we could build automated SQL {INSERT/UPDATE/DELETE for single table}, it would save a lot of development time. It will be help full for single table and admin panel. I’m sure it will help you a lot. In next post, I’ll show you how to generate HTML page based on a sql table.
Let see, we have a table named “my_prefix_feedback” and the stucture is –
CREATE TABLE `my_prefix_feedback` (
`id` mediumint(10) NOT NULL auto_increment,
`name` varchar(120) NOT NULL default ”,
`company` varchar(120) NOT NULL default ”,
`address` varchar(150) NOT NULL default ”,
`country` varchar(100) NOT NULL default ”,
`phone` varchar(20) NOT NULL default ”,
`fax` varchar(20) default NULL,
`email` varchar(65) NOT NULL default ”,
`web` varchar(150) default NULL,
`email_for` enum(‘Enquiry’,’Complaint’,’Praise’,’Order Request’,’Status Report’,’Other’) NOT NULL default ‘Enquiry’,
`subject` varchar(120) NOT NULL default ”,
`message` tinytext NOT NULL,
`entry_date` datetime NOT NULL default ‘0000-00-00 00:00:00’,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
To insert a record we have to write a sql like this-
INSERT INTO `my_prefix_feedback` ( `id` , `name` , `company` , `address` , `country` , `phone` , `fax` , `email` , `web` , `email_for` , `subject` , `message` , `entry_date` )
VALUES (NULL , ‘Abdul Mohymen’, ‘Kernel BD Corporation’, ‘Ka-77, Hakim Mansion (2nd Floor), Kuril, Progoti Sharani, Badda ‘, ‘Bangladesh’, ‘+8802-989 7034’, ‘+8802-989 7034’, ‘info@kernelbd.com’, ‘http://www.kernelbd.com’, ‘Enquiry’, ‘Test messege’, ‘This is test messege to demonstrat how to build sql interactively. let see the details.’, ‘2007-06-04 12:11:34’);
To use this table in HTML using PHP, we have to build a html form and write some php code to execute sql to insert given record.
- example form page “my_form.html“
- Form Method is POST {it might be GET}
<form action=”form_submitted.php” method=”post” name=”myForm”>
- We have to give the form field name as is mysql table field name, like
….
…………………….
…
<tr>
<td><label for=”name“>Name:</label></td>
<td id=”_name”></td>
<td><input type=”text” class=”textInput” name=”name” id=”name“></td>
</tr>
<tr>
<td><label for=”address“>Name:</label></td>
<td id=”_address”></td>
<td><input type=”text” class=”textInput” name=”address” id=”address“></td>
</tr>
…
…………………….
… - PHP GLOBAL variable contents all submitted value $_POST, it might be $_GET
We could see the variable and values with <? print_r ($_POST); ?>
- We have a class named
<?
/*************************************\PHP CLIB – SqlString – Version 0.1
by Abdul Mohymen
mohymen@yahoo.com
Copyright(c) 2005-2006 Kernel BD Corporation
http://www.kernelbd.com
info@kernelbd.com\*************************************/class SqlString
{
var $sql;
var $sqlType;
var $table;
var $fieldName = array();
var $fieldValue = array();
var $sqlField;
var $sqlValue;
var $trimFields = array();
var $formInput = array();
var $conditionField = array();
var $primaryKey;
var $sqlFunction = array();
var $parseText;function SqlString()
{$this->sqlFunction = array (‘now()’,
‘rand()’) ;
}function setData($table, $trimFields, $formInput, $conditionField, $sqlType)
{
$this->tableName($table);
$this->sqlType = $sqlType;
$this->trimFields($trimFields);
$this->formInput($formInput);
$this->conditionField = $conditionField;
}function tableName($table)
{
$this->table = $table;
return true;
}function trimFields($trimFields)
{
if(!is_array($trimFields)) $trimFields = array();
reset($this->trimFields);$this->trimFields = $trimFields;
$i = 0;
foreach($trimFields as $value)
{
$this->trimFields[“$i”] = $value;
$i += 1;
}
return true;
}function formInput($formInput)
{
if(!is_array($formInput)) $formInput = array();
$this->formInput = $formInput;
return true;
}function getPropertise()
{
global $db;$sql = “describe `$this->table`”;
$result = $db->query($sql);
if (DB::isError($result)) {die ($result->getMessage());}$i = -1;
$this->primaryKey = ”;while($qdata = $result->fetchRow())
{
$i += 1;$this->fieldName[$i] = $qdata[0];
if(in_array(‘PRI’, $qdata)) $this->primaryKey = $qdata[0];
}
return true;
}function filterArray()
{
foreach($this->trimFields as $key=>$value)
{
if(in_array($value, $this->fieldName))
{
$key = array_search($value, $this->fieldName);
$this->fieldName[$key] = ”;
}
}
}function insert($i)
{
$tmpvar = $this->fieldName[$i];
$quoute = $this->findSqlFunc($this->formInput[“$tmpvar”]);if(!empty($this->formInput[“$tmpvar”]))
{
if(empty($this->sqlField))
{
$this->sqlField .= $this->fieldName[$i];
$this->sqlValue .= “$quoute” . $this->parseText->format($this->formInput[“$tmpvar”]) . “$quoute”;
}
else
{
$this->sqlField .= “, ” . $this->fieldName[$i];
$this->sqlValue .= “, $quoute”. $this->parseText->format($this->formInput[“$tmpvar”]) .”$quoute”;
}
}
return true;
}function update($i)
{
$tmpvar = $this->fieldName[$i];$quoute = $this->findSqlFunc($this->formInput[“$tmpvar”]);
if($this->primaryKey != $this->fieldName[$i])
{
if(empty($this->sqlField))
{
if(!empty($this->formInput[“$tmpvar”]))
$this->sqlField .= $this->fieldName[$i] . “=$quoute” .$this->parseText->format( $this->formInput[“$tmpvar”]) . “$quoute”;
}
else
{
if(!empty($this->formInput[“$tmpvar”]))
$this->sqlField .= “, ” . $this->fieldName[$i] . “=$quoute” . $this->parseText->format($this->formInput[“$tmpvar”]) . “$quoute”;
}
}return true;
}function buildCondition($condition)
{
//echo($condition);
if(!is_array($condition)) $condition = array();$tmpvar = ”;
if(count($condition) < 1)
{
$tmpvar = ”;
}
else
{
foreach($condition as $key=>$value)
{
if(!empty($key))
{
if(empty($tmpvar)) $tmpvar = ” where ” . $key . “='” . $value . “‘”;
else $tmpvar .= ” and ” . $key . “='” . $value . “‘”;
}
}
}return $tmpvar;
}function buildSql()
{
$this->getPropertise();
$this->filterArray();if(count($this->fieldName)-1 == count($this->trimFields))
{
echo ‘Please enter data.’; exit;
}$inVar = “insert into `$this->table` “;
$upVar = “update `$this->table` set “;
$delVar = “delete from `$this->table` “;if($this->sqlType == ‘update’ || $this->sqlType == ‘delete’)
{
$this->condition = $this->buildCondition($this->conditionField);
}$this->sqlField = ”;
$this->sqlValue = ”;$i = 0;
foreach($this->fieldName as $value)
{
if(!empty($value))
{
if($this->sqlType == ‘insert’) $this->insert($i);
if($this->sqlType == ‘update’) $this->update($i);
}
$i += 1;
}if($this->sqlType == ‘insert’) $this->sql = $inVar . ” (” . $this->sqlField . “)” . ” values(” . $this->sqlValue . “)”;
else if($this->sqlType == ‘update’) $this->sql = $upVar . $this->sqlField . $this->condition;
else if($this->sqlType == ‘delete’) $this->sql = $delVar . $this->condition;
return $this->sql;
}function findSqlFunc($fieldValue)
{
foreach($this->sqlFunction as $key=>$value)
{
if(preg_match(“/$value/i”, $fieldValue))
{
return $qoute = “”;
}
else return $qoute = “‘”;
}
}
}//end class
?> - We have create an instance of sqlString class.
$sqlString = new SqlString(); - We have to prepare the Object –
<?
$sqlType = “insert”; // Your are set here what sql it will be build “insert”, “update”, “delete”/* You may want to exclude any mysql {any DB} table field, list there name into $trimFileds array */$trimFields[0] = ‘id’;/* If you delete/update query, there is needed a where condition, here is id, for insert query, no need to assign it */
//$conditionField[0] = ‘id’;
$sqlString->setData(‘my_prefix_feedback‘, $trimFields, $_POST, $conditionField, $sqlType);
?> - execute and build sql –
<?
print $sqlString->buildSql(); // just print the sql for display that is working correctlymysql_query($sqlString->buildSql()); // inserting data into database
?>
Example code as zip file could be found at file section of this group and name is – SQL Builder – Automation.
Please send your feedback, it will encourge/praise/correction myself.
Posted in Development, PHP, Programming, PERL, MySql, JSP, Java, ASP, Website, Resources, Tech Article | Leave a Comment »
Please laugh at the below joke and go happily to home at the day end:
Four guys, Harvard, Yale, MIT and SANTA SINGH from Punjab University were to be interviewed for a prestigious job.
One common question was asked to all 4 of them. INTERVIEWER: WHICH IS THE FASTEST THING IN THE WORLD?
YALE guy: Its light, Nothing can travel faster than light
HARVARD Guy: It’s the Thought; b’cos thought is so fast it comes instantly in your mind.
MIT guy: Its Blink, you can blink and its hard to realize you blinked
SANTA SINGH: Its Loose motion
INTERVIEWER: (Shocked to hear Santa’s reply, asked) “WHY”?
SANTA SINGH: Last night after dinner, I was lying in my bed and I got the worst stomach cramps, and before I could THINK, BLINK, or TURN ON THE LIGHTS, it was over!!!!
Posted in Fun With Joy | Leave a Comment »
Once upon a time, there was a software engineer who used to develop programs on his Pentium machine, sitting under a tree on the banks of a river. He used to earn his bread by selling those programs in the Sunday market.
One day, while he was working, his machine tumbled off the table and fell in the river. Encouraged by the Panchatantra story of his childhood (the woodcutter and the axe ),
He started praying to the River Goddess. The River Goddess wanted to test him and so appeared only after one month of rigorous prayers. The engineer told her that he had lost his computer in the river.
As usual, the Goddess wanted to test his honesty. She showed him a match box and asked, ” Is this your computer ?” Disappointed by the Goddess’ lack of computer awareness, the engineer replied, ” No.”
She next showed him a pocket-sized calculator and asked if that was his.
Annoyed, the engineer said “No, not at all!!”
Finally, she came up with his own Pentium machine and asked if it was his.
The engineer, left with no option, sighed and said ” Yes.”
The River Goddess was happy with his honesty. She was about to give
Him all three items, but before she could make the offer, the engineer
Asked her, “Don’t you know that you’re supposed to show me some better computers before bringing up my own ?”
The River Goddess, angered at this, replied, “I know that, you stupid donkey! The first two things I showed you were the Trillennium and the Billennium, the latest computers from IBM !”. So saying, she disappeared with the Pentium!!
********
Moral :If you’re not up-to-date with technology trends , it’s better to keep your mouth shut and let people think you’re a fool than to open your mouth and remove all doubt.
Posted in Fun With Joy | Leave a Comment »
Getting married is very much like going to a restaurant with friends. You order what you want, then when you see what the other person has, you wish you had ordered that.
*********
Man: Is there any way for long life?
Dr: Get married.
Man: Will it help?
Dr: No, but the thought of long life will never come.
*********
Why do couples hold hands during their wedding?
It’s a formality just like two boxers shaking hands before the fight begins!
*********
Wife: Darling today is our anniversary, what should we do?
Husband: Let us stand in silence for 2 minutes.
*********
It’s funny when people discuss Love Marriage vs Arranged.
It’s like asking someone, if suicide is better or being murdered
*********
It is difficult to understand GOD . He makes such beautiful things as women and then he turns them into Wives
*********
If u r married please ignore this MSG,
For everyone else: Happy Independence Day
*********
Before marriage, a man will lie awake all night thinking about something you say.
After marriage, he’ll fall asleep before you finish.
*********
There’s a way of transferring funds that is even faster than electronic banking. It’s called marriage
*********
Galfriends r like chocolates,
Taste gud anytime.
Lovers r like PIZZAS, Hot n spicy, eaten frequently.
Wife r like Dal RICE, eaten when there`s no choice
*********
Man receives telegram: Wife deadshould be buried or cremated?
Man: Don’t take any chances. Burn the body and bury the ash.
*********
Prospective husband: Do you have a book called ‘Man, The Master of Women’?
Salesgirl: The fiction department is on the other side, sir.
*********
Q: Why dogs don’t marry?
A: Because they are already leading a dog’s life!
*********
There was this guy who told his woman that he loved her so much that he would go through hell for her. They got married and now he is going thru hell.
*********
Fact of life: One woman brings you into this world crying & the other ensures you continue to do so for the rest of your life!
*********
Q: Why doesn’t law permit a man to marry a second woman?
A: Because as per law you cannot be punished twice for the same offence!
*********
Posted in Fun With Joy | Leave a Comment »
A guy walks into a bar and sits down. He starts dialing numbers…
Like a telephone… On his hand and talking into his hand. The bartender walks over and tells him this is a very tough neighborhood and he doesn’t need any trouble here.
The guy says, “You don’t understand. I’m very hi-tech. I had a phone installed in my hand because I was tired of carrying the cellular.”
The bartender says “Prove it .”
The guy dials up a number and hands his hand to the bartender. The bartender talks into the hand and carries on a conversation. “That’s incredible,” says the bartender. “I would never have believed it!”
“Yeah,” said the guy, “I can keep in touch with my broker, my wife, you name it. By the way, where is the men’s room?” The bartender directs him to the men’s room. The guy goes in and 5, 10, 20 minutes go by and he doesn’t return.
Fearing the worst given the neighborhood, the bartender goes into the men’s room. There is the guy spread-eagle on the wall. His pants are pulled down and he has a roll of toilet paper up his butt.
“Oh my god!” said the bartender. “Did they rob you? Are you hurt?”
The guy turns and says: “No, I’m ok . I’m just waiting for a fax.”
Posted in Fun With Joy | Leave a Comment »
A journey of a thousand miles begins with a single step. …
Posted in Uncategorized | 1 Comment »
Aries, The Warrior 