本文实例讲述了php实现的通过参数生成mysql语句类。分享给大家供大家参考，具体如下：
这个类可以通过指定的表和字段参数创建select ,insert , update 和 delete 语句。
这个类可以创建sql语句的where条件，像like的查询语句，使用left join和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> " ;}
?>
类代码：
<?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 ;}
}
}
?>
更多关于php相关内容感兴趣的读者可查看本站专题：《php基于pdo操作数据库技巧总结》、《php运算与运算符用法总结》、《php网络编程技巧总结》、《php基本语法入门教程》、《php操作office文档技巧总结(包括word,excel,access,ppt)》、《php日期与时间用法总结》、《php面向对象程序设计入门教程》、《php字符串(string)用法总结》、《php+mysql数据库操作入门教程》及《php常见数据库操作技巧汇总》
希望本文所述对大家php程序设计有所帮助。
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:)！