techsir 登陆 |注册|TS首页
  首页 › 快活林 › 猿氏悟语 ›

动态网页技术PHP通过参数来生成MYSQL语句类

By: 冰客 发表于 2008-11-7 13:18:01 · 4282次点击   回复:0   
这个类可以通过具有参数的数组来构建MySQL查询语句。
  这个类可以通过指定的表和字段参数创建SELECT ,INSERT , UPDATE 和 DELETE 语句。
  这个类可以创建SQL语句的WHERE条件，像LIKE的查询语句，使用LEFT JOIN和ORDER 语句。
  例子:

  2.    <? php
  3.    /* *******************************************************************
  4.   Example file
  5.   This example shows how to use the MyLibSQLGen class
  6.   
  7.   The example is based on the following MySQL table:
  8.   
  9.   CREATE TABLE customer (
  10.    id int(10) unsigned NOT NULL auto_increment,
  11.    name varchar(60) NOT NULL default '',
  12.    address varchar(60) NOT NULL default '',
  13.    city varchar(60) NOT NULL default '',
  14.    PRIMARY KEY (cust_id)
  15.   ) TYPE=MyISAM;
  16.   
  17.   ******************************************************************* */
  18.   
  19.    require_once ( " class_mylib_SQLGen-1.0.php " );
  20.   
  21.    $fields = Array ( " name " , " address " , " city " );
  22.    $values = Array ( " Fadjar " , " Resultmang Raya Street " , " Jakarta " );
  23.    $tables = Array ( " customer " );
  24.   
  25.    echo " <b>Result Generate Insert</b><br> " ;
  26.    $object = new MyLibSQLGen();
  27.    $object -> clear_all_assign(); // to refresh all property but it no need when first time execute
  28.    $object -> setFields( $fields );
  29.    $object -> setValues( $values );
  30.    $object -> setTables( $tables );
  31.   
  32.    if ( ! $object -> getInsertSQL()){ echo $object -> Error; exit ;}
  33.    else { $sql = $object -> Result; echo $sql . " <br> " ;}
  34.   
  35.   
  36.    echo " <b>Result Generate Update</b><br> " ;
  37.    $fields = Array ( " name " , " address " , " city " );
  38.    $values = Array ( " Fadjar " , " Resultmang Raya Street " , " Jakarta " );
  39.    $tables = Array ( " customer " );
  40.    $id = 1 ;
  41.    $conditions [ 0 ][ " condition " ] = " id='$id' " ;
  42.    $conditions [ 0 ][ " connection " ] = "" ;
  43.   
  44.    $object -> clear_all_assign();
  45.    $object -> setFields( $fields );
  46.    $object -> setValues( $values );
  47.    $object -> setTables( $tables );
  48.    $object -> setConditions( $conditions );
  49.   
  50.    if ( ! $object -> getUpdateSQL()){ echo $object -> Error; exit ;}
  51.    else { $sql = $object -> Result; echo $sql . " <br> " ;}
  52.   
  53.    echo " <b>Result Generate Delete</b><br> " ;
  54.    $tables = Array ( " customer " );
  55.    $conditions [ 0 ][ " condition " ] = " id='1' " ;
  56.    $conditions [ 0 ][ " connection " ] = " OR " ;
  57.    $conditions [ 1 ][ " condition " ] = " id='2' " ;
  58.    $conditions [ 1 ][ " connection " ] = " OR " ;
  59.    $conditions [ 2 ][ " condition " ] = " id='4' " ;
  60.    $conditions [ 2 ][ " connection " ] = "" ;
  61.   
  62.    $object -> clear_all_assign();
  63.    $object -> setTables( $tables );
  64.    $object -> setConditions( $conditions );
  65.   
  66.    if ( ! $object -> getDeleteSQL()){ echo $object -> Error; exit ;}
  67.    else { $sql = $object -> Result; echo $sql . " <br> " ;}
  68.   
  69.    echo " <b>Result Generate List</b><br> " ;
  70.    $fields = Array ( " id " , " name " , " address " , " city " );
  71.    $tables = Array ( " customer " );
  72.    $id = 1 ;
  73.    $conditions [ 0 ][ " condition " ] = " id='$id' " ;
  74.    $conditions [ 0 ][ " connection " ] = "" ;
  75.   
  76.    $object -> clear_all_assign();
  77.    $object -> setFields( $fields );
  78.    $object -> setTables( $tables );
  79.    $object -> setConditions( $conditions );
  80.   
  81.    if ( ! $object -> getQuerySQL()){ echo $object -> Error; exit ;}
  82.    else { $sql = $object -> Result; echo $sql . " <br> " ;}
  83.   
  84.    echo " <b>Result Generate List with search on all fields</b><br> " ;
  85.    $fields = Array ( " id " , " name " , " address " , " city " );
  86.    $tables = Array ( " customer " );
  87.    $id = 1 ;
  88.    $search = " Fadjar Nurswanto " ;
  89.    $object -> clear_all_assign();
  90.    $object -> setFields( $fields );
  91.    $object -> setTables( $tables );
  92.    $object -> setSearch( $search );
  93.   
  94.    if ( ! $object -> getQuerySQL()){ echo $object -> Error; exit ;}
  95.    else { $sql = $object -> Result; echo $sql . " <br> " ;}
  96.   
  97.    echo " <b>Result Generate List with search on some fields</b><br> " ;
  98.    $fields = Array ( " id " , " name " , " address " , " city " );
  99.    $tables = Array ( " customer " );
  100.    $id = 1 ;
  101.    $search = Array (
  102.    " name " => " Fadjar Nurswanto " ,
  103.    " address " => " Tomang Raya "
  104.    );
  105.   
  106.    $object -> clear_all_assign();
  107.    $object -> setFields( $fields );
  108.    $object -> setTables( $tables );
  109.    $object -> setSearch( $search );
  110.   
  111.    if ( ! $object -> getQuerySQL()){ echo $object -> Error; exit ;}
  112.    else { $sql = $object -> Result; echo $sql . " <br> " ;}
  113.    ?>
  114.   
复制代码
类代码:

  2.    <? php
  3.    /*
  4.   Created By : Fadjar Nurswanto <fajr_n@rindudendam.net>
  5.   DATE : 2006-08-02
  6.   PRODUCTNAME : class MyLibSQLGen
  7.   PRODUCTVERSION : 1.0.0
  8.   DESCRIPTION : class yang berfungsi untuk menggenerate SQL
  9.   DENPENCIES :
  10.    */
  11.    class MyLibSQLGen
  12.   {
  13.    var $Result ;
  14.    var $Tables = Array ();
  15.    var $Values = Array ();
  16.    var $Fields = Array ();
  17.    var $Conditions = Array ();
  18.    var $Condition ;
  19.    var $LeftJoin = Array ();
  20.    var $Search ;
  21.    var $Sort = " ASC " ;
  22.    var $Order ;
  23.    var $Error ;
  24.   
  25.    function MyLibSQLGen(){}
  26.    function BuildCondition()
  27.    {
  28.    $funct = " BuildCondition " ;
  29.    $className = get_class ( $this );
  30.    $conditions = $this -> getConditions();
  31.    if ( ! $conditions ){ $this -> dbgDone( $funct ); return true ;}
  32.    if ( ! is_array ( $conditions ))
  33.    {
  34.    $this -> Error = " $className::$funct \nVariable conditions not Array " ;
  35.    return ;
  36.    }
  37.    for ( $i = 0 ; $i < count ( $conditions ); $i ++ )
  38.    {
  39.    $this -> Condition .= $conditions [ $i ][ " condition " ] . " " . $conditions [ $i ][ " connection " ] . " " ;
  40.    }
  41.    return true ;
  42.    }
  43.    function BuildLeftJoin()
  44.    {
  45.    $funct = " BuildLeftJoin " ;
  46.    $className = get_class ( $this );
  47.    if ( ! $this -> getLeftJoin()){ $this -> Error = " $className::$funct \nProperty LeftJoin was empty " ; return ;}
  48.   
  49.    $LeftJoinVars = $this -> getLeftJoin();
  50.   
  51.    $hasil = false ;
  52.    foreach ( $LeftJoinVars as $LeftJoinVar )
  53.    {
  54.    @ $hasil .= " LEFT JOIN " . $LeftJoinVar [ " table " ];
  55.    foreach ( $LeftJoinVar [ " on " ] as $var )
  56.    {
  57.    @ $condvar .= $var [ " condition " ] . " " . $var [ " connection " ] . " " ;
  58.    }
  59.    $hasil .= " ON ( " . $condvar . " ) " ;
  60.    unset ( $condvar );
  61.    }
  62.   
  63.    $this -> ResultLeftJoin = $hasil ;
  64.   
  65.    return true ;
  66.    }
  67.    function BuildOrder()
  68.    {
  69.    $funct = " BuildOrder " ;
  70.    $className = get_class ( $this );
  71.    if ( ! $this -> getOrder()){ $this -> Error = " $className::$funct \nProperty Order was empty " ; return ;}
  72.    if ( ! $this -> getFields()){ $this -> Error = " $className::$funct \nProperty Fields was empty " ; return ;}
  73.   
  74.    $Fields = $this -> getFields();
  75.    $Orders = $this -> getOrder();
  76.    if ( ereg ( " , " , $Orders )){ $Orders = explode ( " , " , $Order );}
  77.    if ( ! is_array ( $Orders )){ $Orders = Array ( $Orders );}
  78.   
  79.    foreach ( $Orders as $Order )
  80.    {
  81.    if ( ! is_numeric ( $Order )){ $this -> Error = " $className::$funct \nProperty Order not Numeric " ; return ;}
  82.    if ( $Order > count ( $this -> Fields)){ $this -> Error = " $className::$funct \nMax value of property Sort is " . count ( $this -> Fields); return ;}
  83.   
  84.    @ $xorder .= $Fields [ $Order ] . " , " ;
  85.    }
  86.   
  87.    $this -> ResultOrder = " ORDER BY " . substr ( $xorder , 0 ,- 1 );
  88.   
  89.    return true ;
  90.    }
  91.    function BuildSearch()
  92.    {
  93.    $funct = " BuildSearch " ;
  94.    $className = get_class ( $this );
  95.   
  96.    if ( ! $this -> getSearch()){ $this -> Error = " $className::$funct \nProperty Search was empty " ; return ;}
  97.    if ( ! $this -> getFields()){ $this -> Error = " $className::$funct \nProperty Fields was empty " ; return ;}
  98.   
  99.    $Fields = $this -> getFields();
  100.    $xvalue = $this -> getSearch();
  101.   
  102.    if ( is_array ( $xvalue ))
  103.    {
  104.    foreach ( $Fields as $field )
  105.    {
  106.    if (@ $xvalue [ $field ])
  107.    {
  108.    $Values = explode ( " " , $xvalue [ $field ]);
  109.    foreach ( $Values as $Value )
  110.    {
  111.    @ $hasil .= $field . " LIKE '% " . $Value . " %' OR " ;
  112.    }
  113.    if ( $hasil )
  114.    {
  115.    @ $hasil_final .= " ( " . substr ( $hasil , 0 ,- 4 ) . " ) AND " ;
  116.    unset ( $hasil );
  117.    }
  118.    }
  119.    }
  120.    $hasil = $hasil_final ;
  121.    }
  122.    else
  123.    {
  124.    foreach ( $Fields as $field )
  125.    {
  126.    $Values = explode ( " " , $xvalue );
  127.    foreach ( $Values as $Value )
  128.    {
  129.    @ $hasil .= $field . " LIKE '% " . $Value . " %' OR " ;
  130.    }
  131.    }
  132.    }
  133.   
  134.    $this -> ResultSearch = substr ( $hasil , 0 ,- 4 );
  135.    return true ;
  136.    }
  137.    function clear_all_assign()
  138.    {
  139.    $this -> Result = null ;
  140.    $this -> ResultSearch = null ;
  141.    $this -> ResultLeftJoin = null ;
  142.    $this -> Result = null ;
  143.    $this -> Tables = Array ();
  144.    $this -> Values = Array ();
  145.    $this -> Fields = Array ();
  146.    $this -> Conditions = Array ();
  147.    $this -> Condition = null ;
  148.    $this -> LeftJoin = Array ();
  149.    $this -> Sort = " ASC " ;
  150.    $this -> Order = null ;
  151.    $this -> Search = null ;
  152.    $this -> fieldSQL = null ;
  153.    $this -> valueSQL = null ;
  154.    $this -> partSQL = null ;
  155.    $this -> Error = null ;
  156.    return true ;
  157.    }
  158.    function CombineFieldValue( $manual = false )
  159.    {
  160.    $funct = " CombineFieldsPostVar " ;
  161.    $className = get_class ( $this );
  162.    $fields = $this -> getFields();
  163.    $values = $this -> getValues();
  164.    if ( ! is_array ( $fields ))
  165.    {
  166.    $this -> Error = " $className::$funct \nVariable fields not Array " ;
  167.    return ;
  168.    }
  169.    if ( ! is_array ( $values ))
  170.    {
  171.    $this -> Error = " $className::$funct \nVariable values not Array " ;
  172.    return ;
  173.    }
  174.    if ( count ( $fields ) != count ( $values ))
  175.    {
  176.    $this -> Error = " $className::$funct \nCount of fields and values not match " ;
  177.    return ;
  178.    }
  179.    for ( $i = 0 ; $i < count ( $fields ); $i ++ )
  180.    {
  181.    @ $this -> fieldSQL .= $fields [ $i ] . " , " ;
  182.    if ( $fields [ $i ] == " pwd " || $fields [ $i ] == " password " || $fields [ $i ] == " pwd " )
  183.    {
  184.    @ $this -> valueSQL .= " password(' " . $values [ $i ] . " '), " ;
  185.    @ $this -> partSQL .= $fields [ $i ] . " =password(' " . $values [ $i ] . " '), " ;
  186.    }
  187.    else
  188.    {
  189.    if ( is_numeric ( $values [ $i ]))
  190.    {
  191.    @ $this -> valueSQL .= $values [ $i ] . " , " ;
  192.    @ $this -> partSQL .= $fields [ $i ] . " = " . $values [ $i ] . " , " ;
  193.    }
  194.    else
  195.    {
  196.    @ $this -> valueSQL .= " ' " . $values [ $i ] . " ', " ;
  197.    @ $this -> partSQL .= $fields [ $i ] . " =' " . $values [ $i ] . " ', " ;
  198.    }
  199.    }
  200.    }
  201.    $this -> fieldSQL = substr ( $this -> fieldSQL , 0 ,- 1 );
  202.    $this -> valueSQL = substr ( $this -> valueSQL , 0 ,- 1 );
  203.    $this -> partSQL = substr ( $this -> partSQL , 0 ,- 1 );
  204.    return true ;
  205.    }
  206.    function getDeleteSQL()
  207.    {
  208.    $funct = " getDeleteSQL " ;
  209.    $className = get_class ( $this );
  210.    $Tables = $this -> getTables();
  211.    if ( ! $Tables || ! count ( $Tables ))
  212.    {
  213.    $this -> dbgFailed( $funct );
  214.    $this -> Error = " $className::$funct \nTable was empty " ;
  215.    return ;
  216.    }
  217.    for ( $i = 0 ; $i < count ( $Tables ); $i ++ )
  218.    {
  219.    @ $Table .= $Tables [ $i ] . " , " ;
  220.    }
  221.    $Table = substr ( $Table , 0 ,- 1 );
  222.   
  223.    $sql = " DELETE FROM " . $Table ;
  224.   
  225.    if ( $this -> getConditions())
  226.    {
  227.    if ( ! $this -> BuildCondition()){ $this -> dbgFailed( $funct ); return ;}
  228.    $sql .= " WHERE " . $this -> getCondition();
  229.    }
  230.    $this -> Result = $sql ;
  231.    return true ;
  232.    }
  233.    function getInsertSQL()
  234.    {
  235.    $funct = " getInsertSQL " ;
  236.    $className = get_class ( $this );
  237.    if ( ! $this -> getValues()){ $this -> Error = " $className::$funct \nProperty Values was empty " ; return ;}
  238.    if ( ! $this -> getFields()){ $this -> Error = " $className::$funct \nProperty Fields was empty " ; return ;}
  239.    if ( ! $this -> getTables()){ $this -> Error = " $className::$funct \nProperty Tables was empty " ; return ;}
  240.   
  241.    if ( ! $this -> CombineFieldValue()){ $this -> dbgFailed( $funct ); return ;}
  242.    $Tables = $this -> getTables();
  243.   
  244.    $sql = " INSERT INTO " . $Tables [ 0 ] . " ( " . $this -> fieldSQL . " ) VALUES ( " . $this -> valueSQL . " ) " ;
  245.   
  246.    $this -> Result = $sql ;
  247.   
  248.    return true ;
  249.    }
  250.    function getUpdateSQL()
  251.    {
  252.    $funct = " getUpdateSQL " ;
  253.    $className = get_class ( $this );
  254.   
  255.    if ( ! $this -> getValues()){ $this -> Error = " $className::$funct \nProperty Values was empty " ; return ;}
  256.    if ( ! $this -> getFields()){ $this -> Error = " $className::$funct \nProperty Fields was empty " ; return ;}
  257.    if ( ! $this -> getTables()){ $this -> Error = " $className::$funct \nProperty Tables was empty " ; return ;}
  258.   
  259.    if ( ! $this -> CombineFieldValue()){ $this -> dbgFailed( $funct ); return ;}
  260.    if ( ! $this -> BuildCondition()){ $this -> dbgFailed( $funct ); return ;}
  261.    $Tables = $this -> getTables();
  262.   
  263.    $sql = " UPDATE " . $Tables [ 0 ] . " SET " . $this -> partSQL . " WHERE " . $this -> getCondition();
  264.   
  265.    $this -> Result = $sql ;
  266.   
  267.    return true ;
  268.    }
  269.    function getQuerySQL()
  270.    {
  271.    $funct = " getQuerySQL " ;
  272.    $className = get_class ( $this );
  273.   
  274.    if ( ! $this -> getFields()){ $this -> Error = " $className::$funct \nProperty Fields was empty " ; return ;}
  275.    if ( ! $this -> getTables()){ $this -> Error = " $className::$funct \nProperty Tables was empty " ; return ;}
  276.   
  277.    $Fields = $this -> getFields();
  278.    $Tables = $this -> getTables();
  279.    foreach ( $Fields as $Field ){@ $sql_raw .= $Field . " , " ;}
  280.    foreach ( $Tables as $Table ){@ $sql_table .= $Table . " , " ;}
  281.   
  282.    $this -> Result = " SELECT " . substr ( $sql_raw , 0 ,- 1 ) . " FROM " . substr ( $sql_table , 0 ,- 1 );
  283.   
  284.    if ( $this -> getLeftJoin())
  285.    {
  286.    if ( ! $this -> BuildLeftJoins()){ $this -> dbgFailed( $funct ); return ;}
  287.    $this -> Result .= " " . $this -> ResultLeftJoin;
  288.    }
  289.    if ( $this -> getConditions())
  290.    {
  291.    if ( ! $this -> BuildCondition()){ $this -> dbgFailed( $funct ); return ;}
  292.    $this -> Result .= " WHERE ( " . $this -> Condition . " ) " ;
  293.    }
  294.    if ( $this -> getSearch())
  295.    {
  296.    if ( ! $this -> BuildSearch()){ $this -> dbgFailed( $funct ); return ;}
  297.    if ( $this -> ResultSearch)
  298.    {
  299.    if ( eregi ( " WHERE " , $this -> Result)){ $this -> Result .= " AND " . $this -> ResultSearch;}
  300.    else { $this -> Result .= " WHERE " . $this -> ResultSearch;}
  301.    }
  302.    }
  303.    if ( $this -> getOrder())
  304.    {
  305.    if ( ! $this -> BuildOrder()){ $this -> dbgFailed( $funct ); return ;}
  306.    $this -> Result .= " " . $this -> ResultOrder;
  307.    }
  308.    if ( $this -> getSort())
  309.    {
  310.    if (@ $this -> ResultOrder)
  311.    {
  312.    $this -> Result .= " " . $this -> getSort();
  313.    }
  314.    }
  315.   
  316.    return true ;
  317.    }
  318.   
  319.    function getCondition(){ return @ $this -> Condition;}
  320.    function getConditions(){ if ( count (@ $this -> Conditions) && is_array (@ $this -> Conditions)){ return @ $this -> Conditions;}}
  321.    function getFields(){ if ( count (@ $this -> Fields) && is_array (@ $this -> Fields)){ return @ $this -> Fields;}}
  322.    function getLeftJoin(){ if ( count (@ $this -> LeftJoin) && is_array (@ $this -> LeftJoin)){ return @ $this -> LeftJoin;}}
  323.    function getOrder(){ return @ $this -> Order;}
  324.    function getSearch(){ return @ $this -> Search;}
  325.    function getSort(){ return @ $this -> Sort ;}
  326.    function getTables(){ if ( count (@ $this -> Tables) && is_array (@ $this -> Tables)){ return @ $this -> Tables;}}
  327.    function getValues(){ if ( count (@ $this -> Values) && is_array (@ $this -> Values)){ return @ $this -> Values;}}
  328.   
  329.    function setCondition( $input ){ $this -> Condition = $input ;}
  330.    function setConditions( $input )
  331.    {
  332.    if ( is_array ( $input )){ $this -> Conditions = $input ;}
  333.    else { $this -> Error = get_class ( $this ) . " ::setConditions \nParameter input not array " ; return ;}
  334.    }
  335.    function setFields( $input )
  336.    {
  337.    if ( is_array ( $input )){ $this -> Fields = $input ;}
  338.    else { $this -> Error = get_class ( $this ) . " ::setFields \nParameter input not array " ; return ;}
  339.    }
  340.    function setLeftJoin( $input )
  341.    {
  342.    if ( is_array ( $input )){ $this -> LeftJoin = $input ;}
  343.    else { $this -> Error = get_class ( $this ) . " ::setFields \nParameter input not array " ; return ;}
  344.    }
  345.    function setOrder( $input ){ $this -> Order = $input ;}
  346.    function setSearch( $input ){ $this -> Search = $input ;}
  347.    function setSort( $input ){ $this -> Sort = $input ;}
  348.    function setTables( $input )
  349.    {
  350.    if ( is_array ( $input )){ $this -> Tables = $input ;}
  351.    else { $this -> Error = get_class ( $this ) . " ::setTables \nParameter input not array " ; return ;}
  352.    }
  353.    function setValues( $input )
  354.    {
  355.    if ( is_array ( $input )){ $this -> Values = $input ;}
  356.    else { $this -> Error = get_class ( $this ) . " ::setValues \nParameter input not array " ; return ;}
  357.    }
  358.   }
  359.    ?>
复制代码
4282次点击
0个回复  |  直到 2008-11-7 13:18:01
↑ 回到顶部
添加一条新回复
您需要登录后才可以回帖 登录 | 成为会员 新浪微博登陆

← TechSir
 
标签云|手机版|科技先生 ( 京ICP备07036130号 Powered by Discuz! X )

GMT+8, 2024-5-19 13:19