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

PHP example of converting MySQL query results into arrays and splicing them with where, mysqlwhere_PHP tutorial

WBOY

Release： 2016-07-12 08:51:55

Original

789 people have browsed it

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 ";

Copy after login

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">备案开始日期：</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> 

<&#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')

Copy after login

SQL processing where conditions

<&#63;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 &#63; ($useOr === false &#63; ' 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; 
}

Copy after login