sql where查询拼接技巧

1.将where语句从分支移到主干，解决where在分支上的多种情况，分支条件只需and 连接即可如where1==1

等

<?php
$name = $userId = 1;
$whereConditions ="";
if($userId){
	$whereConditions .= " and userid={$userId}";
}
if($name){
	$whereConditions .= " and name='{$name}'";
}
$whereConditions = str_replace(array("and", "AND"), "AND", $whereConditions);
$whereConditions = str_replace(array("or", "OR"), "OR", $whereConditions);
$whereConditions = trim($whereConditions);
$whereConditions = ltrim($whereConditions, 'AND');
$whereConditions = ltrim($whereConditions, 'OR');
echo "where ".$whereConditions; //where userid=1 AND name='1'

$sql="SELECT * FROM bb where true ";

因为使用添加了“1=1”的过滤条件以后数据库系统就无法使用索引等查询优化策略，数据库系统将会被迫对每行数据进行扫描（也就是全表扫描）以比较此行是否满足过滤条件，当表中数据量比较大的时候查询速度会非常慢。优化方法

test.html

<td>商品名称：</td>
<td width="200"><input type="text" class="text" name="kit_name" id="fn_kit_name"/></td>
<td align="right">备案开始日期：</td>
<td width="200"><input type="text" name="search[or_get_reg_date]"/><img src="images/data.jpg" /></td>
<td>备案结束日期：</td>
<td width="200"><input type="text" name="search[lt_reg_date]"/><img src="images/data.jpg" /></td>
</tr>
<tr>
    <td>产品经理：</td>
    <td><input type="text" class="text" name="search[managerid]"/></td>

<?php
$postData = array(
    'managerid' => '21',
    'or_get_reg_date' => '09',
    'lt_reg_date' => '2012-12-19',
    'in_id' => array(1, 2, 3),
);
$tmpConditions = transArrayTerms($postData);
echo $whereCause = getWhereSql($tmpConditions);
// WHERE managerid like '21%' OR reg_date<'09' AND reg_date>'2012-12-19' AND id in ('1','2','3')

处理where条件的sql

<?php
/**
 * 表单提交值转化成where拼接数组
 */
function transArrayTerms($infoSearch) {
    $aryRst = array();
    $separator = array('lt'=>'<', 'let'=>'<=', 'gt'=>'>', 'get'=>'>=', 'eq'=>'=', 'neq'=>'<>');
    foreach ($infoSearch as $term => $value) {
        if (empty($value)) continue;

        $name = $term;
        if (strpos($term, "or_") !== false) { //添加or连接符
            $terms['useOr'] = true;
            $name = str_replace("or_", "", $term);
        }

        if (strpos($name, "in_") !== false) {
            $terms['name'] = str_replace("in_", "", $name);
            $terms['charCal'] = " in ";
            $terms['value'] = "('" . implode("','", $value) . "')";
        } else {
            $terms['name'] = $name;
            $terms['charCal'] = " like ";
            $terms['value'] = "'" . trim($value) . "%'";
        }
        //放在else后面
        foreach($separator as $charCalName =>$charCalVal){
            if (strpos($name, $charCalName."_") !== false) {
                $terms['name'] = str_replace($charCalName."_", "", $name);
                $terms['charCal'] = $charCalVal;
                $terms['value'] = "'" . trim($value) . "'";
            }
        }
        $aryRst[] = $terms;
        unset($terms);
    }
    return $aryRst;
}

function whereOperator($has_where, $useOr) {
    $operator = $has_where ? ($useOr === false ? ' AND ' : ' OR ') : ' WHERE ';
    return $operator;
}

/**
 * aryTerm transArrayTerms转化后的查询条件
 * @过滤没有输入的sql查询条件并转化成where条件.
 */
function getWhereSql($aryTerm) {
    $whereCause = '';
    if (count($aryTerm) > 0) {
		$has_where = '';
        foreach ($aryTerm as $value) {
            $has_where = whereOperator($has_where, isset($value['useOr']));
            $whereCause .= $has_where . $value['name'] . $value['charCal'] . $value['value'];
        }
    }
    return $whereCause;
}