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