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.
Leave a comment