PHP 搜索功能数据库语句查询条件的类

以前用搜索的时候直接自己写SQL语句，今天写了一个类，觉得还蛮好玩的，不过是单表查询，不是多表的，有空继续改进。

HTML页面，放的是几个搜索条件输入框

<form action='query_condition.php' name="query_condtion" method="post">
search start time:<input type="text" name="time[]" id="time_start"/>
search end time:<input type="text" name="time[]" id="time_end"/>
<br />
license plate number:<input type="text" name="carnum" id="carnum"/><br />
camera:
<select name="deviceid" id="deviceid"/>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
</select>
<br />
come in or out
<select name="type" id="type"/>
<option value="1">come in</option>
<option value="2">come out</option>
</select>
<input type="submit" value="submit" />
</form>

 处理程序，要求输入查询的数据库表的字段以及类型，为的是在varchar...类型的字段值上加上引号,int,float之类的就不加了，另外的话调用的时候setupFieldValueArray函数要注意下参数

<?php
require_once('QueryCondition.php');

$field_type = array(
    'carid' => 'int', 
    'carnum' => 'string',
    'deviceid' => 'int',
    'type' => 'int',
    'imageurl' => 'string',
    'time' => 'string',
);

$qwObj = new queryCondition($field_type);
$str = $qwObj->getQueryWhere($tmp_arr);

if (count($_POST) > 0) {
    foreach ($_POST as $field_name => $field_value) {
        $field_name = trim($field_name);
        switch ($field_name) {
            case "time":
                $qwObj->setupFieldValueArray($field_name, $field_value, 14);
                break;
            case 'carnum':
                $qwObj->setupFieldValueArray($field_name, $field_value, 10);
                break;
            case 'deviceid':
                $qwObj->setupFieldValueArray($field_name, $field_value, 4);
                break;
            case 'type':
                $qwObj->setupFieldValueArray($field_name, $field_value, 4);
                break;

            default:
                break;
        }
        
        
    }
    $rs = $qwObj->getQueryWhere();
    print_r($rs);
}
?>

类文件

<?php
class queryCondition {
    var $_fieldsTypes;
    var $_fields;

    public function queryCondition($fieldsTypes) {
        if ( is_array($fieldsTypes) ) {
            $this->_fieldsTypes = $fieldsTypes;
        }
    }
    
    public function getQueryWhere() {
        $arr = $this->_fields;
        if ( is_array($arr) ) {
            $totalQueryArr = array();
            foreach ($arr as $fieldName => $value) {
                $queryArr = array();
                if ( isset($this->_fieldsTypes[$fieldName]) ) {
                    $type = $this->_fieldsTypes[$fieldName];
                    $quote = '';
                    if ( $type == 'string' ) {
                        $quote = '"';
                    }
                    
                    if ( count($value['and']) > 0 ) {
                        $str = $this->_getSubQueryWhere($fieldName, $value['and'], $quote, 1);
//                        echo $str;
                        if ( $str != '') {
                            $queryArr[] = $str;
                        }
                    }

                    if ( count($value['or']) > 0 ) {
                        $str = $this->_getSubQueryWhere($fieldName, $value['or'], $quote, 2);
                        
                        if ( $str != '') {
                            $queryArr[] = $str;
                        }
                    }

                    if ( count($queryArr) > 0 ) {
                        $queryJoin = (isset($value['join']) && $value['join'] != '') ? ' ' . strtoupper($value['join']) . ' ' : ' AND ';
                        $totalQueryArr[] = implode($queryJoin, $queryArr);
                    }
                }
            }
            if ( count($totalQueryArr) > 0 ) {
                return implode(' AND ', $totalQueryArr);
            }
        }
        return '';
    }

    protected function _getSubQueryWhere ($fieldName, $subConditions, $quote, $wholeJoin = 1) {
        if ( $fieldName != '' && is_array($subConditions) && count($subConditions) > 0 ) {
            
            $sqlArr = array();

            foreach ( $subConditions as $subCondition ) {
                if ( isset($subCondition['values']) && isset($subCondition['mark']) ) {
                    $queryValues = $this->_resetFieldValues($subCondition['values'], $quote); 

                    $queryMark = $subCondition['mark'];

                    $queryJoin = strtoupper($subCondition['join']);

                    $markStr = $this->_getMarkStr($fieldName, $queryMark);

                    if ( is_array($queryValues) ) {
                        if ( $queryMark == 14 ) {
                            if (count($queryValues) == 2) {
                                $sqlArr[] = vsprintf($markStr, $queryValues);
                            } else if (count($queryValues) > 2) {
                                $sqlArr[] = vsprintf($markStr, array($queryValues[0], $queryValues[1]));
                            }
                        } else {
                            $strArr = array();
                            
                            foreach ( $queryValues as $singleValue ) {
                                $strArr[] = vsprintf($markStr, $singleValue);
                            }
                            $sqlArr[] = implode(' ' . $queryJoin . ' ', $strArr);
                        }
                    } else if ( is_string($queryValues) ) {
                        $sqlArr[] = vsprintf($markStr, $queryValues);
                    }
                } 
            }
             
            if ( count($sqlArr) > 0 ) {
                $wholeJoin = ( $wholeJoin == 1 ) ? ' AND ' : ' OR ';
                $query = implode($wholeJoin, $sqlArr);
            }
            return $query;
        }
        return '';
    }

    /**************************************************
     * 文 件 名：QueryCondition.php 
     *
     * 说    明：处理SQL查询语句里的查询条件
     *
     * 作    者：lsf linda@vigoicu.com
     *
     * 创建时间：2009-3-27
     *
     * parameters：$fieldName: string, DB table field name
     *             $fieldValue: sting or array，values for searching
     *             $queryMark: int, this is SQL query operator
     *                         1: LIKE
     *                         2: LIKE %%
     *                         3: NOT LIKE
     *                         4: =
     *                         5: !=
     *                         6: REGEXP
     *                         7: NOT REGEXP
     *                         8: IS NULL
     *                         9: IS NOT NULL
     *                         10: >
     *                         11: >=
     *                         12: <
     *                         13: <=
     *                         14: BETWEEN
     *             $queryJoin: string, 'and' 'or' you can choose how to join your multiple search condition
     *             $andOr: int, assign array to which sub-array
     *                     1: and
     *                     2: or
     *             $wholeQueryJoin: string, 'and' 'or' if both 'and sub-array' and 'or sub-array' have values, you can choose to use which relationship.
     ***************************************************/
    public function setupFieldValueArray($fieldName, $fieldValue, $queryMark = 4, $queryJoin = 'and', $andOr = 1, $wholeQueryJoin = 'and') {
        $fieldName = trim($fieldName);
        $valueArr = $this->_getFieldValueMark($fieldValue, $queryMark, $queryJoin);
        
        if (strtolower($andOr) == 1) 
        {
            $this->_setupAndArray($fieldName, $valueArr);
        } 
        else
        {
            $this->_setupOrArray($fieldName, $valueArr);
        }

        $this->_setupWholeQueryJoin($fieldName, $wholeQueryJoin);
    }

    protected function _getMarkStr($fieldName, $queryMark) {
        if ( $fieldName != '' ) {
            $markStr = '';
            
            switch ( $queryMark ) {
                 case 1:
                    $markStr = "LIKE %s";
                    break;
                case 2:
                    $markStr = "LIKE %%s%";
                    break;
                case 3:
                    $markStr = "NOT LIKE %s";
                    break;
                case 4:
                    $markStr = "= %s";
                    break;
                case 5:
                    $markStr = "!= %s";
                    break;
                case 6:
                    $markStr = "REGEXP %s";
                    break;
                case 7:
                    $markStr = "NOT REGEXP %s";
                    break;
                case 8:
                    $markStr = "IS NULL";
                    break;
                case 9:
                    $markStr = "IS NOT NULL";
                    break;
                case 10:
                    $markStr = "> %s";
                    break;
                case 11:
                    $markStr = ">= %s";
                    break;
                case 12:
                    $markStr = "< %s";
                    break;
                case 13:
                    $markStr = "<= %s";
                    break;
                case 14:
                    $markStr = "BETWEEN %s AND %s";
                    break;
                default:
                    break;
            }
            if ($markStr != '') {
                return $fieldName . ' ' . $markStr;
            }
            return '';
        }
    }

    protected function _setupOrArray($fieldName, $arr) {
        $this->_fields[$fieldName]['or'][] = $arr;
    }

    protected function _setupAndArray($fieldName, $arr) {
        $this->_fields[$fieldName]['and'][] = $arr;
    }

    protected function _setupWholeQueryJoin($fieldName, $wholeQueryJoin) {
        $this->_fields[$fieldName]['join'] = $wholeQueryJoin;
    }

    protected function _getFieldValueMark($fieldValue, $queryMark, $queryJoin) {
        $fieldValue = $this->_resetFieldValues($fieldValue);
        return array(
            'mark' => $queryMark,
            'values' => $fieldValue,
            'join' => $queryJoin,
            );
    }

    protected function _resetFieldValues($values, $quote = '') {
        $tmpArr = array();
        if ( is_string($values) && $values != '' ) {
            return $quote . $this->_h($values) . $quote;
        } else if ( is_array($values) && count($values) != 0 ) {
            foreach ($values as $k => $v) {
                if ($v != '') {
                    $tmpArr[$k] = $quote . $this->_h($v) . $quote;
                }
            }
            return $tmpArr;
        }
    }

    protected function _h($text) {
        return trim(htmlspecialchars($text));
    }

    public function getWholeArray() {
        // structure an array
        //    
        //    $arr[field_name] = array(
        //        'and' => array(
        //                   array(
        //                    'mark'=>'=',
        //                    'values'=>array('linda', 'get'),
        //                    'join'=>'and'
        //                    ),
        //                    array(
        //                    'mark'=>'=',
        //                    'values'=>array('linda', 'get'),
        //                    'join'=>'and'
        //                    ),
        //                ),
        //        'or' => array(),
        //        'join'=>'',
        //    )

        return $this->_fields;
    }
}
?>