Feeds:
Posts
Comments

Free online QR Code generator.

 


http://www.kernelbd.com


DB Row List for Database Table – Automation

dbrowlistclassphp.txt

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();
*/
?>


http://www.kernelbd.com


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

Kernel BD Corporation

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&#8217;, ‘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.


http://www.kernelbd.com

Laugh at the End

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!!!!

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.

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!

*********