mysql查询结果转换为PHP数组的几种方法的区别:
建议使用mysql_fetch_assoc()或者mysql_fetch_array,这两个函数执行速度比较快,同时也可以通过字段名进行引用,比较清楚。
where拼接技巧
将where语句从分支移到主干,解决where在分支上的多种情况,分支条件只需and 连接即可如where1==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; }