PHP converts MySQL query results to arrays and where to stitch examples _php tips

Source: Internet
Author: User
Tags mysql query

MySQL query results are converted to a PHP array of several methods of the difference:

    • $result = Mysql_fetch_row (): This function returns an array with numbers as the subscript, and you can only refer to them by $result[0], $Result [2].
    • $result = Mysql_fetch_assoc (): This function returns an array of fields named Subscript, which can only be referenced by field names. $result [' field1 '].
    • $result = Mysql_fetch_array (): This function returns a mixed array, which can be referenced either by a numeric subscript or by a field name. $result [0] or $result["field1"].
    • $result = Mysql_fetch_object (): Returns the result as an object, which can be referenced in the form of $result->field1.

It is recommended to use MYSQL_FETCH_ASSOC () or mysql_fetch_array, the two functions are faster to perform, and can be referenced by field names, more clearly.

Where stitching Tips
move the where statement from the branch to the trunk, resolving the multiple cases where it is on the branch, where the branching condition is only connected with and, as Where1==1.

$sql = "SELECT * from BB where true";

Because the database system will not be able to use query optimization strategies such as indexes after using the filter condition with the addition of "1=1", the database system will be forced to scan each row of data (that is, a full table scan) to compare whether this row satisfies the filter condition, and the query speed is very slow when the table has a larger amount of data. Optimization method
Test.html

 <td> product name:</td> <td width= "" ><input type= "text" class= "text" Name= " Kit_name "id=" Fn_kit_name "/></td> <td align=" Right "> Filing start date:</td> <td width=" ">< Input type= "text" Name= "search[or_get_reg_date]"/></td> <td> Filing End Date:</td> <td width= "><input type=" text "Name=" search[lt_reg_date] "/></td> </tr> <tr> <td> product manager:</td> <td><input type=" Text "class = "text" name= "Search[managerid]"/></td> <?php $postData = Array (' ManagerID ' => '), ' Or_get_reg_ 
Date ' => ', ' 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< ' and reg_date> ' 2012-12-19 ' and IDs in (' 1 ', ' 2 ', ' 3 ') 

SQL for handling where conditions

<?php/** * Form submission value into where concatenation array/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) {//Add or connector $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). 
    "%'"; ///($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 the converted query condition * @ Filters the SQL query condition without input and converts it to where condition. 
  * * 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; 
 }
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.