PHP將MySQL的查詢結(jié)果轉(zhuǎn)換為數(shù)組并用where拼接的示例，mysqlwhere

mysql查詢結(jié)果轉(zhuǎn)換為PHP數(shù)組的幾種方法的區(qū)別：?

• $result = mysql_fetch_row()：這個函數(shù)返回的是數(shù)組，數(shù)組是以數(shù)字作為下標(biāo)的，你只能通過$result[0],$Result[2]這樣的形式來引用。
• $result = mysql_fetch_assoc():這個函數(shù)返回是以字段名為下標(biāo)的數(shù)組，只能通過字段名來引用。$result['field1'].
• $result = mysql_fetch_array():這個函數(shù)返回的是一個混合的數(shù)組，既可以通過數(shù)字下標(biāo)來引用，也可以通過字段名來引用。$result[0]或者$result["field1"].
• $result = mysql_fetch_object():以對象的形式返回結(jié)果，可以通過$result->field1這樣的形式來引用。

建議使用mysql_fetch_assoc()或者mysql_fetch_array，這兩個函數(shù)執(zhí)行速度比較快，同時也可以通過字段名進(jìn)行引用，比較清楚。?

where拼接技巧
將where語句從分支移到主干，解決where在分支上的多種情況，分支條件只需and 連接即可如where1==1等

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

因為使用添加了“1=1”的過濾條件以后數(shù)據(jù)庫系統(tǒng)就無法使用索引等查詢優(yōu)化策略，數(shù)據(jù)庫系統(tǒng)將會被迫對每行數(shù)據(jù)進(jìn)行掃描（也就是全表掃描）以比較此行是否滿足過濾條件，當(dāng)表中數(shù)據(jù)量比較大的時候查詢速度會非常慢。優(yōu)化方法
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="/static/imghw/default1.png"  data-src="images/data.jpg"  class="lazy" / alt="PHP將MySQL的查詢結(jié)果轉(zhuǎn)換為數(shù)組并用where拼接的示例，mysqlwhere_PHP教程" ></td> 
<td>備案結(jié)束日期：</td> 
<td width="200"><input type="text" name="search[lt_reg_date]"/><img  src="/static/imghw/default1.png"  data-src="images/data.jpg"  class="lazy" / alt="PHP將MySQL的查詢結(jié)果轉(zhuǎn)換為數(shù)組并用where拼接的示例，mysqlwhere_PHP教程" ></td> 
</tr> 
<tr> 
  <td>產(chǎn)品經(jīng)理：</td> 
  <td><input type="text" class="text" name="search[managerid]"/></td> 

<&#63;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

<&#63;php 
/** 
 * 表單提交值轉(zhuǎn)化成where拼接數(shù)組 
 */ 
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 &#63; ($useOr === false &#63; ' AND ' : ' OR ') : ' WHERE '; 
  return $operator; 
} 
 
/** 
 * aryTerm transArrayTerms轉(zhuǎn)化后的查詢條件 
 * @過濾沒有輸入的sql查詢條件并轉(zhuǎn)化成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; 
}