PHP generates a complete MYSQL statement class instance through parameters, mysql statements

PHP generates a complete MYSQL statement class instance through parameters, mysql statements

This example describes how to generate MYSQL statement classes through parameters implemented by PHP. We will share this with you for your reference. The details are as follows:

This class can create SELECT, INSERT, UPDATE, and DELETE statements using the specified table and field parameters.

This class can create WHERE conditions for SQL statements, such as LIKE Query statements, using LEFT JOIN and ORDER statements

<?php /* *******************************************************************Example fileThis example shows how to use the MyLibSQLGen classThe 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-02PRODUCTNAME    : class MyLibSQLGenPRODUCTVERSION  : 1.0.0DESCRIPTION    : class yang berfungsi untuk menggenerate SQLDENPENCIES    : */  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 ;}  }}?>