Function for generating SQL statements using multiple keywords and fields in PHP
- $ Keyword = "1 2 3 ";
- Echo $ SQL = search ($ keyword, "enter_gongyin_pic", "a + B + c"); // function generation, no LIMIT, no ORDER
-
Generate an SQL statement:
- SELECT * FROM 'Enter _ gongyin_pic 'WHERE 'A' LIKE' % 100' OR 'A' LIKE '% 100' OR 'A' LIKE' % 100' OR 'B' LIKE '% 100' OR' B 'LIKE' % 100' OR 'B' LIKE '% 100' OR 'C' LIKE' % 100' OR 'C' LIKE '% 100' OR 'C' LIKE '% 3%'
$ Keyword is obtained by POST or GET. it is separated by spaces and can be searched by multiple fields. Functions for generating SQL query statements with multiple keywords and fields:
/**
- * Query condition construction statement
- * Edit: bbs.it-home.org
- */
- Function search ($ keyword, $ table, $ field)
- {
- /**
- // Parameter description:
- // Keyword is a keyword, for example, "Beijing Capital direction train ". With or without spaces
- // Table is the table name, such as enter_gongyin_pic.
- // Field is a combination of fields. for example, you can write the name after you find a field.
- // Use name + picdir if you search for more than two
- */
- // First, confirm the field
- $ New_field = explode ("+", $ field); // strip by +
- $ Field_count = count ($ new_field); // The number of results.
-
-
- $ Newstring = explode ("", $ keyword); // strip by space
- $ Newstring2 = array ();
- // Remove the useless space ancestor element from the string
- $ I = 0;
- Foreach ($ newstring as $ key => $ value ){
- If ($ value! = "")
- {
- $ Newstring2 [$ I] = $ value;
- $ I ++;
- }
- }
- // Remove the useless space ancestor element from the string,
-
- $ Result_count = count ($ newstring2); // The number of results.
-
- // Generate an SQL statement
- // * If ($ field_count = 1) // find the START Field **
- If ($ field_count = 1) // Find 1 field
- {
- If ($ result_count = 1) // judge if it is a key segment
- {
- $ Newstring_search = $ newstring2 [0];
- $ SQL = "SELECT *
- FROM '$ table'
- WHERE "'. $ new_field [0]."' LIKE '% $ newstring_search % '";
- }
-
- If ($ result_count> 1) // you can specify multiple key segments.
- {
-
- $ SQL = "SELECT *
- FROM '$ table'
- WHERE ";
- $ SQL _add = "";
- Foreach ($ newstring2 as $ key => $ value)
- {
- If ($ key = 0)
- {
- $ SQL _add = $ SQL _add. "" '. $ new_field [0]. "'like' %". $ value. "% '";
- }
- Else
- {
- $ SQL _add = $ SQL _add. "OR" '. $ new_field [0]. "'like' %". $ value. "% '";
- }
- }
$ SQL = $ SQL. $ SQL _add;
- }
- }
// ***** If ($ field_count = 1) // find the END field ******
- // ***** If ($ field_count> 1) // find multiple fields START *****
- If ($ field_count> 1) // find multiple fields. $ new_field is an array. Has multiple fields
- {
- If ($ result_count = 1) // judge if it is a key segment
- {
- $ Newstring_search = $ newstring2 [0]; // $ newstring_search is a keyword.
- $ SQL = "SELECT *
- FROM '$ table'
- WHERE ";
- $ SQL _add = ""; // add a new field
- Foreach ($ new_field as $ key => $ value)
- {
- If ($ key = 0)
- {
- $ SQL _add = $ SQL _add. "" '. $ value. "' LIKE '%". $ newstring_search. "% '";
- }
- Else
- {
- $ SQL _add = $ SQL _add. "OR" '. $ value. "' LIKE '%". $ newstring_search. "% '";
- }
- }
- $ SQL = $ SQL. $ SQL _add;
- }
- If ($ result_count> 1) // you can specify multiple key segments (multiple keywords) =
- {
- $ SQL = "SELECT *
- FROM '$ table'
- WHERE ";
- $ SQL _add = ""; // add a new field
- Foreach ($ new_field as $ key => $ value)
- {
- If ($ key = 0) // in case of $ new_field [0: 'A 'like' % 100' OR 'A' LIKE '% 100' OR 'A' LIKE' % 100'
- {// Nested foreach
- Foreach ($ newstring2 as $ key2 => $ value2)
- {
- If ($ key2 = 0)
- {
- $ SQL _add = $ SQL _add. "" '. $ value. "' LIKE '%". $ value2. "% '";
- }
- Else
- {
- $ SQL _add = $ SQL _add. "OR" '. $ value. "' LIKE '%". $ value2. "% '";
- }
- }
- // Nested foreach
- }
- Else
- // (For multi-field queries, such as querying the name + picdir table) start the FOREACH continuous loop, and each execution of ELSE $ new_field [1] $ new_field [2] $ new_field [3].
- // The corresponding value is $ value
- {
- // Nested foreach (multi-field and multi-keyword)
- Foreach ($ newstring2 as $ key2 => $ value2)
- {
- If ($ key2 = 0)
- {
- $ SQL _add = $ SQL _add. "OR" '. $ value. "' LIKE '%". $ value2. "% '";
- }
- Else
- {
- $ SQL _add = $ SQL _add. "OR" '. $ value. "' LIKE '%". $ value2. "% '";
- }
- }
- // Nested foreach
- }
-
- } // Foreach ($ new_field as $ key => $ value) ends
- $ SQL = $ SQL. $ SQL _add;
- } // If ($ result_count> 1) ends
- } // If ($ field_count> 1) ends
- // *** If ($ field_count> 1) // find multiple fields 'end ***
- Return $ SQL;
- }
|