PHP;"> 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 " Result Generate Insert
" ; $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 . "
" ;} echo " Result Generate Update
" ; $fields = Array ( " name "," 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 . "
" ;} echo " Result Generate Delete
" ; $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 . "
" ;} echo " Result Generate List
" ; $fields = Array ( " id "," name "," 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 . "
" ;} echo " Result Generate List with search on all fields
" ; $fields = Array ( " id "," 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 . "
" ;} echo " Result Generate List with search on some fields
" ; $fields = Array ( " id "," 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 . "
" ;} ?>

PHP;"> PHP /* Created By : Fadjar Nurswanto 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,- 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,- 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,- 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,- 1 ); $this -> valuesql = substr ( $this -> valuesql,- 1 ); $this -> partsql = substr ( $this -> partsql,- 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,- 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,- 1 ) . " FROM " . substr ( $sql_table,- 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 ;} } } ?>