This article mainly introduces the PHP implementation of generating MYSQL statement classes through parameters, and analyzes the implementation and usage techniques of generating MYSQL statement classes in the form of complete examples. Friends in need can refer to
This class can Creates SELECT, INSERT, UPDATE and DELETE statements with specified table and field parameters.
This class can create WHERE conditions of SQL statements, such as LIKE query statements, using LEFT JOIN and ORDER statements
<?php
/* *******************************************************************
Example file
This example shows how to use the MyLibSQLGen class
The example is based on the following MySQL table:
CREATE TABLE customer (
id int(10) unsigned NOT NULL auto_increment,
name varchar(60) NOT NULL default '',
address varchar(60) NOT NULL default '',
city varchar(60) NOT NULL default '',
PRIMARY KEY (cust_id)
) TYPE=MyISAM;
******************************************************************* */
require_once ( " class_mylib_SQLGen-1.0.php " );
$fields = Array ( " name " , " address " , " city " );
$values = Array ( " Fadjar " , " Resultmang Raya Street " , " Jakarta " );
$tables = Array ( " customer " );
echo " <b>Result Generate Insert</b><br> " ;
$object = new MyLibSQLGen();
$object -> clear_all_assign(); // to refresh all property but it no need when first time execute
$object -> setFields( $fields );
$object -> setValues( $values );
$object -> setTables( $tables );
if ( ! $object -> getInsertSQL()){ echo $object -> Error; exit ;}
else { $sql = $object -> Result; echo $sql . " <br> " ;}
echo " <b>Result Generate Update</b><br> " ;
$fields = Array ( " name " , " address " , " city " );
$values = Array ( " Fadjar " , " Resultmang Raya Street " , " Jakarta " );
$tables = Array ( " customer " );
$id = 1 ;
$conditions [ 0 ][ " condition " ] = " id='$id' " ;
$conditions [ 0 ][ " connection " ] = "" ;
$object -> clear_all_assign();
$object -> setFields( $fields );
$object -> setValues( $values );
$object -> setTables( $tables );
$object -> setConditions( $conditions );
if ( ! $object -> getUpdateSQL()){ echo $object -> Error; exit ;}
else { $sql = $object -> Result; echo $sql . " <br> " ;}
echo " <b>Result Generate Delete</b><br> " ;
$tables = Array ( " customer " );
$conditions [ 0 ][ " condition " ] = " id='1' " ;
$conditions [ 0 ][ " connection " ] = " OR " ;
$conditions [ 1 ][ " condition " ] = " id='2' " ;
$conditions [ 1 ][ " connection " ] = " OR " ;
$conditions [ 2 ][ " condition " ] = " id='4' " ;
$conditions [ 2 ][ " connection " ] = "" ;
$object -> clear_all_assign();
$object -> setTables( $tables );
$object -> setConditions( $conditions );
if ( ! $object -> getDeleteSQL()){ echo $object -> Error; exit ;}
else { $sql = $object -> Result; echo $sql . " <br> " ;}
echo " <b>Result Generate List</b><br> " ;
$fields = Array ( " id " , " name " , " address " , " city " );
$tables = Array ( " customer " );
$id = 1 ;
$conditions [ 0 ][ " condition " ] = " id='$id' " ;
$conditions [ 0 ][ " connection " ] = "" ;
$object -> clear_all_assign();
$object -> setFields( $fields );
$object -> setTables( $tables );
$object -> setConditions( $conditions );
if ( ! $object -> getQuerySQL()){ echo $object -> Error; exit ;}
else { $sql = $object -> Result; echo $sql . " <br> " ;}
echo " <b>Result Generate List with search on all fields</b><br> " ;
$fields = Array ( " id " , " name " , " address " , " city " );
$tables = Array ( " customer " );
$id = 1 ;
$search = " Fadjar Nurswanto " ;
$object -> clear_all_assign();
$object -> setFields( $fields );
$object -> setTables( $tables );
$object -> setSearch( $search );
if ( ! $object -> getQuerySQL()){ echo $object -> Error; exit ;}
else { $sql = $object -> Result; echo $sql . " <br> " ;}
echo " <b>Result Generate List with search on some fields</b><br> " ;
$fields = Array ( " id " , " name " , " address " , " city " );
$tables = Array ( " customer " );
$id = 1 ;
$search = Array (
" name " => " Fadjar Nurswanto " ,
" address " => " Tomang Raya "
);
$object -> clear_all_assign();
$object -> setFields( $fields );
$object -> setTables( $tables );
$object -> setSearch( $search );
if ( ! $object -> getQuerySQL()){ echo $object -> Error; exit ;}
else { $sql = $object -> Result; echo $sql . " <br> " ;}
?>Class code:
<?php
/*
Created By : Fadjar Nurswanto <fajr_n@rindudendam.net>
DATE : 2006-08-02
PRODUCTNAME : class MyLibSQLGen
PRODUCTVERSION : 1.0.0
DESCRIPTION : class yang berfungsi untuk menggenerate SQL
DENPENCIES :
*/
class MyLibSQLGen
{
var $Result ;
var $Tables = Array ();
var $Values = Array ();
var $Fields = Array ();
var $Conditions = Array ();
var $Condition ;
var $LeftJoin = Array ();
var $Search ;
var $Sort = " ASC " ;
var $Order ;
var $Error ;
function MyLibSQLGen(){}
function BuildCondition()
{
$funct = " BuildCondition " ;
$className = get_class ( $this );
$conditions = $this -> getConditions();
if ( ! $conditions ){ $this -> dbgDone( $funct ); return true ;}
if ( ! is_array ( $conditions ))
{
$this -> Error = " $className::$funct Variable conditions not Array " ;
return ;
}
for ( $i = 0 ; $i < count ( $conditions ); $i ++ )
{
$this -> Condition .= $conditions [ $i ][ " condition " ] . " " . $conditions [ $i ][ " connection " ] . " " ;
}
return true ;
}
function BuildLeftJoin()
{
$funct = " BuildLeftJoin " ;
$className = get_class ( $this );
if ( ! $this -> getLeftJoin()){ $this -> Error = " $className::$funct Property LeftJoin was empty " ; return ;}
$LeftJoinVars = $this -> getLeftJoin();
$hasil = false ;
foreach ( $LeftJoinVars as $LeftJoinVar )
{
@ $hasil .= " LEFT JOIN " . $LeftJoinVar [ " table " ];
foreach ( $LeftJoinVar [ " on " ] as $var )
{
@ $condvar .= $var [ " condition " ] . " " . $var [ " connection " ] . " " ;
}
$hasil .= " ON ( " . $condvar . " ) " ;
unset ( $condvar );
}
$this -> ResultLeftJoin = $hasil ;
return true ;
}
function BuildOrder()
{
$funct = " BuildOrder " ;
$className = get_class ( $this );
if ( ! $this -> getOrder()){ $this -> Error = " $className::$funct Property Order was empty " ; return ;}
if ( ! $this -> getFields()){ $this -> Error = " $className::$funct Property Fields was empty " ; return ;}
$Fields = $this -> getFields();
$Orders = $this -> getOrder();
if ( ereg ( " , " , $Orders )){ $Orders = explode ( " , " , $Order );}
if ( ! is_array ( $Orders )){ $Orders = Array ( $Orders );}
foreach ( $Orders as $Order )
{
if ( ! is_numeric ( $Order )){ $this -> Error = " $className::$funct Property Order not Numeric " ; return ;}
if ( $Order > count ( $this -> Fields)){ $this -> Error = " $className::$funct Max value of property Sort is " . count ( $this -> Fields); return ;}
@ $xorder .= $Fields [ $Order ] . " , " ;
}
$this -> ResultOrder = " ORDER BY " . substr ( $xorder , 0 ,- 1 );
return true ;
}
function BuildSearch()
{
$funct = " BuildSearch " ;
$className = get_class ( $this );
if ( ! $this -> getSearch()){ $this -> Error = " $className::$funct Property Search was empty " ; return ;}
if ( ! $this -> getFields()){ $this -> Error = " $className::$funct Property Fields was empty " ; return ;}
$Fields = $this -> getFields();
$xvalue = $this -> getSearch();
if ( is_array ( $xvalue ))
{
foreach ( $Fields as $field )
{
if (@ $xvalue [ $field ])
{
$Values = explode ( " " , $xvalue [ $field ]);
foreach ( $Values as $Value )
{
@ $hasil .= $field . " LIKE '% " . $Value . " %' OR " ;
}
if ( $hasil )
{
@ $hasil_final .= " ( " . substr ( $hasil , 0 ,- 4 ) . " ) AND " ;
unset ( $hasil );
}
}
}
$hasil = $hasil_final ;
}
else
{
foreach ( $Fields as $field )
{
$Values = explode ( " " , $xvalue );
foreach ( $Values as $Value )
{
@ $hasil .= $field . " LIKE '% " . $Value . " %' OR " ;
}
}
}
$this -> ResultSearch = substr ( $hasil , 0 ,- 4 );
return true ;
}
function clear_all_assign()
{
$this -> Result = null ;
$this -> ResultSearch = null ;
$this -> ResultLeftJoin = null ;
$this -> Result = null ;
$this -> Tables = Array ();
$this -> Values = Array ();
$this -> Fields = Array ();
$this -> Conditions = Array ();
$this -> Condition = null ;
$this -> LeftJoin = Array ();
$this -> Sort = " ASC " ;
$this -> Order = null ;
$this -> Search = null ;
$this -> fieldSQL = null ;
$this -> valueSQL = null ;
$this -> partSQL = null ;
$this -> Error = null ;
return true ;
}
function CombineFieldValue( $manual = false )
{
$funct = " CombineFieldsPostVar " ;
$className = get_class ( $this );
$fields = $this -> getFields();
$values = $this -> getValues();
if ( ! is_array ( $fields ))
{
$this -> Error = " $className::$funct Variable fields not Array " ;
return ;
}
if ( ! is_array ( $values ))
{
$this -> Error = " $className::$funct Variable values not Array " ;
return ;
}
if ( count ( $fields ) != count ( $values ))
{
$this -> Error = " $className::$funct Count of fields and values not match " ;
return ;
}
for ( $i = 0 ; $i < count ( $fields ); $i ++ )
{
@ $this -> fieldSQL .= $fields [ $i ] . " , " ;
if ( $fields [ $i ] == " pwd " || $fields [ $i ] == " password " || $fields [ $i ] == " pwd " )
{
@ $this -> valueSQL .= " password(' " . $values [ $i ] . " '), " ;
@ $this -> partSQL .= $fields [ $i ] . " =password(' " . $values [ $i ] . " '), " ;
}
else
{
if ( is_numeric ( $values [ $i ]))
{
@ $this -> valueSQL .= $values [ $i ] . " , " ;
@ $this -> partSQL .= $fields [ $i ] . " = " . $values [ $i ] . " , " ;
}
else
{
@ $this -> valueSQL .= " ' " . $values [ $i ] . " ', " ;
@ $this -> partSQL .= $fields [ $i ] . " =' " . $values [ $i ] . " ', " ;
}
}
}
$this -> fieldSQL = substr ( $this -> fieldSQL , 0 ,- 1 );
$this -> valueSQL = substr ( $this -> valueSQL , 0 ,- 1 );
$this -> partSQL = substr ( $this -> partSQL , 0 ,- 1 );
return true ;
}
function getDeleteSQL()
{
$funct = " getDeleteSQL " ;
$className = get_class ( $this );
$Tables = $this -> getTables();
if ( ! $Tables || ! count ( $Tables ))
{
$this -> dbgFailed( $funct );
$this -> Error = " $className::$funct Table was empty " ;
return ;
}
for ( $i = 0 ; $i < count ( $Tables ); $i ++ )
{
@ $Table .= $Tables [ $i ] . " , " ;
}
$Table = substr ( $Table , 0 ,- 1 );
$sql = " DELETE FROM " . $Table ;
if ( $this -> getConditions())
{
if ( ! $this -> BuildCondition()){ $this -> dbgFailed( $funct ); return ;}
$sql .= " WHERE " . $this -> getCondition();
}
$this -> Result = $sql ;
return true ;
}
function getInsertSQL()
{
$funct = " getInsertSQL " ;
$className = get_class ( $this );
if ( ! $this -> getValues()){ $this -> Error = " $className::$funct Property Values was empty " ; return ;}
if ( ! $this -> getFields()){ $this -> Error = " $className::$funct Property Fields was empty " ; return ;}
if ( ! $this -> getTables()){ $this -> Error = " $className::$funct Property Tables was empty " ; return ;}
if ( ! $this -> CombineFieldValue()){ $this -> dbgFailed( $funct ); return ;}
$Tables = $this -> getTables();
$sql = " INSERT INTO " . $Tables [ 0 ] . " ( " . $this -> fieldSQL . " ) VALUES ( " . $this -> valueSQL . " ) " ;
$this -> Result = $sql ;
return true ;
}
function getUpdateSQL()
{
$funct = " getUpdateSQL " ;
$className = get_class ( $this );
if ( ! $this -> getValues()){ $this -> Error = " $className::$funct Property Values was empty " ; return ;}
if ( ! $this -> getFields()){ $this -> Error = " $className::$funct Property Fields was empty " ; return ;}
if ( ! $this -> getTables()){ $this -> Error = " $className::$funct Property Tables was empty " ; return ;}
if ( ! $this -> CombineFieldValue()){ $this -> dbgFailed( $funct ); return ;}
if ( ! $this -> BuildCondition()){ $this -> dbgFailed( $funct ); return ;}
$Tables = $this -> getTables();
$sql = " UPDATE " . $Tables [ 0 ] . " SET " . $this -> partSQL . " WHERE " . $this -> getCondition();
$this -> Result = $sql ;
return true ;
}
function getQuerySQL()
{
$funct = " getQuerySQL " ;
$className = get_class ( $this );
if ( ! $this -> getFields()){ $this -> Error = " $className::$funct Property Fields was empty " ; return ;}
if ( ! $this -> getTables()){ $this -> Error = " $className::$funct Property Tables was empty " ; return ;}
$Fields = $this -> getFields();
$Tables = $this -> getTables();
foreach ( $Fields as $Field ){@ $sql_raw .= $Field . " , " ;}
foreach ( $Tables as $Table ){@ $sql_table .= $Table . " , " ;}
$this -> Result = " SELECT " . substr ( $sql_raw , 0 ,- 1 ) . " FROM " . substr ( $sql_table , 0 ,- 1 );
if ( $this -> getLeftJoin())
{
if ( ! $this -> BuildLeftJoins()){ $this -> dbgFailed( $funct ); return ;}
$this -> Result .= " " . $this -> ResultLeftJoin;
}
if ( $this -> getConditions())
{
if ( ! $this -> BuildCondition()){ $this -> dbgFailed( $funct ); return ;}
$this -> Result .= " WHERE ( " . $this -> Condition . " ) " ;
}
if ( $this -> getSearch())
{
if ( ! $this -> BuildSearch()){ $this -> dbgFailed( $funct ); return ;}
if ( $this -> ResultSearch)
{
if ( eregi ( " WHERE " , $this -> Result)){ $this -> Result .= " AND " . $this -> ResultSearch;}
else { $this -> Result .= " WHERE " . $this -> ResultSearch;}
}
}
if ( $this -> getOrder())
{
if ( ! $this -> BuildOrder()){ $this -> dbgFailed( $funct ); return ;}
$this -> Result .= " " . $this -> ResultOrder;
}
if ( $this -> getSort())
{
if (@ $this -> ResultOrder)
{
$this -> Result .= " " . $this -> getSort();
}
}
return true ;
}
function getCondition(){ return @ $this -> Condition;}
function getConditions(){ if ( count (@ $this -> Conditions) && is_array (@ $this -> Conditions)){ return @ $this -> Conditions;}}
function getFields(){ if ( count (@ $this -> Fields) && is_array (@ $this -> Fields)){ return @ $this -> Fields;}}
function getLeftJoin(){ if ( count (@ $this -> LeftJoin) && is_array (@ $this -> LeftJoin)){ return @ $this -> LeftJoin;}}
function getOrder(){ return @ $this -> Order;}
function getSearch(){ return @ $this -> Search;}
function getSort(){ return @ $this -> Sort ;}
function getTables(){ if ( count (@ $this -> Tables) && is_array (@ $this -> Tables)){ return @ $this -> Tables;}}
function getValues(){ if ( count (@ $this -> Values) && is_array (@ $this -> Values)){ return @ $this -> Values;}}
function setCondition( $input ){ $this -> Condition = $input ;}
function setConditions( $input )
{
if ( is_array ( $input )){ $this -> Conditions = $input ;}
else { $this -> Error = get_class ( $this ) . " ::setConditions Parameter input not array " ; return ;}
}
function setFields( $input )
{
if ( is_array ( $input )){ $this -> Fields = $input ;}
else { $this -> Error = get_class ( $this ) . " ::setFields Parameter input not array " ; return ;}
}
function setLeftJoin( $input )
{
if ( is_array ( $input )){ $this -> LeftJoin = $input ;}
else { $this -> Error = get_class ( $this ) . " ::setFields Parameter input not array " ; return ;}
}
function setOrder( $input ){ $this -> Order = $input ;}
function setSearch( $input ){ $this -> Search = $input ;}
function setSort( $input ){ $this -> Sort = $input ;}
function setTables( $input )
{
if ( is_array ( $input )){ $this -> Tables = $input ;}
else { $this -> Error = get_class ( $this ) . " ::setTables Parameter input not array " ; return ;}
}
function setValues( $input )
{
if ( is_array ( $input )){ $this -> Values = $input ;}
else { $this -> Error = get_class ( $this ) . " ::setValues Parameter input not array " ; return ;}
}
}
?>Summary: The above is this The entire content of this article is hoped to be helpful to everyone's study.
Related recommendations:
phpUsage and examples of cookie information security for user login
in PHP Analysis of Socket connection and read and write data timeout issues
phpDetailed explanation of methods and examples of calling your own java program
The above is the detailed content of PHP implements methods and examples of generating MYSQL statement classes through parameters. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
