PHP + Mysql functions that generate SQL statements using multiple keywords and fields

This article describes how to use PHP + Mysql to generate SQL statements with multiple keywords and fields. it involves string and array operations and is a very practical technique for constructing SQL statements, for more information about how to use PHP + Mysql to generate SQL statements using multiple keywords and fields, see the following example. Share it with you for your reference. The specific implementation method is as follows:

First look at the instance:

The code is as follows:

$ Keyword = "1 2 3 ";
Echo $ SQL = search ($ keyword, "enter_gongyin_pic", "a + B + c"); // function generation, no LIMIT, no ORDER

Generation:

The code is as follows:

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. multiple fields can be searched separately by space.

The implementation functions are as follows:

The code is as follows:

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.

// The following SQL statement is generated:


// *********************** 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 ****************************


// *********************** 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 fields) ======================================
{
$ 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;
}

I hope this article will help you with PHP programming.