An example of PHP converting MySQL query results into arrays and splicing them with where, mysqlwhere

The differences between several methods of converting mysql query results into PHP arrays:

• $result = mysql_fetch_row(): This function returns an array. The array is subscripted with numbers. You can only reference it in the form of $result[0], $Result[2].
• $result = mysql_fetch_assoc(): This function returns an array subscripted by the field name, which can only be referenced by the field name. $result['field1'].
• $result = mysql_fetch_array(): This function returns a mixed array, which can be referenced by either numeric subscript or field name. $result[0] or $result["field1"].
• $result = mysql_fetch_object(): Returns the result in the form of an object, which can be referenced in the form of $result->field1.

It is recommended to use mysql_fetch_assoc() or mysql_fetch_array. These two functions execute faster and can also be referenced through field names, which is clearer.

Where splicing skills
Move the where statement from the branch to the trunk to solve the various situations of where on the branch. The branch conditions only need to be connected with and, such as where1==1, etc.

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

Because the database system cannot use query optimization strategies such as indexing after adding the "1=1" filter condition, the database system will be forced to scan each row of data (that is, a full table scan) to compare this row Whether the filtering conditions are met, the query speed will be very slow when the amount of data in the table is relatively large. Optimization method
test.html

<td>商品名稱：</td> 
<td width="200"><input type="text" class="text" name="kit_name" id="fn_kit_name"/></td> 
<td align="right">備案開(kāi)始日期：</td> 
<td width="200"><input type="text" name="search[or_get_reg_date]"/><img src="images/data.jpg" /></td> 
<td>備案結(jié)束日期：</td> 
<td width="200"><input type="text" name="search[lt_reg_date]"/><img src="images/data.jpg" /></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') 

SQL processing where conditions

<&#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)化后的查詢條件 
 * @過(guò)濾沒(méi)有輸入的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; 
}