PHP+Mysql implements functions for generating SQL statements with multiple keywords and multiple fields, mysqlsql_PHP tutorial

PHP+Mysql implements functions for generating SQL statements with multiple keywords and fields, mysqlsql

The example in this article describes the method of PHP+Mysql to implement functions for generating SQL statements with multiple keywords and multiple fields. Share it with everyone for your reference. The specific implementation method is as follows:

Look at the example first:

Copy code 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 BY

Generate:

Copy code The code is as follows:

SELECT * FROM `enter_gongyin_pic` WHERE `a` LIKE '%1%' OR `a` LIKE '%2 %' OR `a` LIKE '%3%' OR `b` LIKE '%1%' OR `b` LIKE '%2%' OR `b` LIKE '%3%' OR `c` LIKE '%1 %' OR `c` LIKE '%2%' OR `c` LIKE '%3%'

$keyword is obtained by POST or GET. Separate by spaces and can be searched in multiple fields.

The implementation function is as follows:

Copy code The code is as follows:

function search($keyword,$table,$field)
{
//================================================ ========

//Formal parameter description:
//keyword is the keyword, such as "Beijing Capital Direction Train". With or without spaces
//table is the table name, such as enter_gongyin_pic.
//Field is a combination of fields. If you want to find a field, just write name
//If you want to find more than two, use name+picdir
//================================================ ========
//First determine the field
$new_field=explode("+",$field); //Press + to peel
$field_count=count($new_field); //The number of results obtained


$newstring=explode(" ",$keyword); //Split by spaces
$newstring2=array();
//Remove useless spaces and uncle elements from the string
$i=0;
foreach ($newstring as $key => $value) {
if($value!="")
{ 
$newstring2[$i]=$value;
$i++;
}  
}  
//Remove useless space elements from the string,
                                                                            $result_count=count($newstring2); //The number of results obtained

//Generate SQL statement below


//************************ if($field_count==1) //Find 1 field START *********** *****************
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) //Judge if there are 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 1 fieldEND ************* *****************


//************************ if($field_count>1) //Find multiple fields START ************ ****************
if($field_count>1) //Find multiple fields. At this time, $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 the keyword
         $sql="SELECT *                                                                         FROM `$table`
WHERE ";
          $sql_add="";//Newly added fields
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) //Judge if there are multiple key segments (multiple keywords) ==========================
{
$sql="SELECT *
FROM `$table`
WHERE ";
$sql_add="";//Newly added fields
foreach ($new_field as $key => $value)
{
if($key==0) //When encountering $new_field[0] Example: `a` LIKE '%1%' OR `a` LIKE '%2%' OR `a` LIKE '%3%'
{ //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
//(If it is multi-field, such as checking the name+picdir table) Start a FOREACH continuous loop, and execute ELSE $new_field[1] $new_field[2] $new_field[3] each time.
//The corresponding value is $value
{
//Nested foreach (multiple fields and multiple keywords)
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)end
}//if($field_count>1) end
//************************ if($field_count>1) //Find multiple fieldsEND ************ ****************
return $sql;
}

I hope this article will be helpful to everyone’s PHP programming design.

How to achieve unlimited level classification with php+mysql and how to design the database? A SQL statement reads the data and generates an array

The infinite levels you are talking about are not infinite in the form of a tree. They are just saying that the number of categories is unlimited and there are only two levels, right?

Generally, there are not many major categories and can be represented by one character. For example, use A～Z to encode, A is news, B is sports,...

The category of news can be set to character type, the first one represents the big tired, and the following represents the small category, such as A001 international news , A002 domestic news, B001 basketball,...

Create two tables, one is the news category table, with two fields: category code, category name; the other category table also has two fields: Category code, category name

In your news table, just use a character type category field.

To generate a tree directory output like yours, it is very simple to start. Just sort your SQL statements according to categories. The program checks whether each piece of data is the same as the category above to determine whether the previous one is displayed. + or |, that's all.

What to do if there are keywords in the sql statement in mysql

insert into tb_test (`use`) values ​​('test');

Use this point, which is the one to the left of the number key 1 above

http://www.bkjia.com/PHPjc/906678.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/906678.htmlTechArticlePHP+Mysql realizes the function of generating SQL statements with multiple keywords and multiple fields, mysqlsql This article describes PHP+Mysql with examples Method to implement functions for generating SQL statements with multiple keywords and multiple fields. Share with everyone...