Feeds:
Posts
Comments

Archive for the ‘Tech Article’ Category

 


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

Advertisements

Read Full Post »


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

Read Full Post »

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

Read Full Post »