Home >Backend Development >PHP Tutorial >PHP example of converting MySQL query results into arrays and splicing them with where, mysqlwhere_PHP tutorial

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

WBOY
WBOYOriginal
2016-07-12 08:51:55781browse

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

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

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1127911.htmlTechArticleExample of PHP converting MySQL query results into arrays and splicing them with where, mysqlwhere mysql query results into PHP arrays The difference between several methods: $result = mysql_fetch_row(): This function...
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn