PHP converts the MySQL query result to an array and uses the where concatenation example, mysqlwhere

PHP converts the MySQL query result to an array and uses the where concatenation example, mysqlwhere

Differences between several methods for converting mysql query results to PHP Arrays:

• $ Result = mysql_fetch_row (): This function returns an array. The array uses numbers as the lower mark. You can only reference the array in the format of $ result [0] and $ Result [2.
• $ Result = mysql_fetch_assoc (): This function returns an array with the field name as the bottom mark and 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 numerical subscript or field name. $ Result [0] or $ result ["field1"].
• $ Result = mysql_fetch_object (): returns results in the form of objects, which can be referenced in the form of $ result-> field1.

We recommend that you use mysql_fetch_assoc () or mysql_fetch_array. The two functions are fast to execute and can be referenced by field names.

Where splicing skills
Move the where statement from the branch to the trunk to solve the various situations where the branch is located. The branch condition only needs to be connected by and, for example, where1 = 1.

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

Because the database system will not be able to use index and other query optimization policies after the "1 = 1" filter condition is added, the database system will be forced to scan each row of data (that is, full table scan) to compare whether this row meets the filtering conditions. When the table has a large amount of data, the query speed will be very slow. Optimization Method
Test.html

<Td> product name: </td> <td width = "200"> <input type = "text" class = "text" name = "kit_name" id = "fn_kit_name"/> </td> <td align = "right"> ICP filing start date: </td> <td width = "200"> <input type = "text" name = "search [or_get_reg_date]"/>  </td> <td> ICP filing end date: </td> <td width = "200"> <input type = "text" name = "search [lt_reg_date]"/>  </td> </tr> <td> product manager: </td> <input type = "text" c Lass = "text" name = "search [managerid]"/> </td> <? Php $ postData = array ('managerid' => '21', 'or _ get_reg_date' => '09', 'lt _ reg_date '=> '2017-12-19 ', 'In _ id' => array (1, 2, 3),); $ tmpConditions = transArrayTerms ($ postData); echo $ whereCause = getWhereSql ($ tmpConditions ); // WHERE managerid like '20140901' OR reg_date <'09' AND reg_date> '2017-12-19 'AND id in ('1', '2', '3 ')

SQL statement for processing where conditions

<? Php/*** convert the form submission value to the 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 ). "% '";} // put it behind 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? ($ UseOr === false? 'AND': 'OR'): 'where'; return $ operator ;} /*** query conditions converted by aryTerm transArrayTerms * @ filter SQL query conditions that are not input and convert them into where conditions. */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 ;}