Maison > Article > développement back-end > PHP implémente des méthodes et des exemples de génération de classes d'instructions MYSQL via des paramètres
Cet article présente principalement l'implémentation PHP de la génération de classes d'instructions MYSQL via des paramètres, et analyse les techniques d'implémentation et d'utilisation de la génération de classes d'instructions MYSQL sous la forme d'exemples complets. Les amis dans le besoin peuvent se référer à
Ceci. la classe peut créer des instructions SELECT, INSERT, UPDATE et DELETE avec des paramètres de table et de champ spécifiés.
Cette classe peut créer des conditions WHERE d'instructions SQL, comme des instructions de requête LIKE, en utilisant les instructions LEFT JOIN et ORDER
<?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> " ;} ?>
Code de classe :
<?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 ;} } } ?>
Résumé. : Ce qui précède représente l'intégralité du contenu de cet article, j'espère qu'il sera utile à l'étude de chacun.
Recommandations associées :
phpExplication détaillée des méthodes et exemples d'appel de votre propre programme Java
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!