PHP+Mysql Realizing the Function of Generating SQL Statement by Multi keywords and Multi fields

This paper describes the method of PHP + Mysql to realize the function of generating SQL statement with multi-keywords and multi-fields. Share it for your reference. The specific implementation method is as follows:

Look at the example first:

$keyword="1 2 3";

echo $sql=search($keyword,"enter_gongyin_pic","a+b+c"); // Function, there is no LIMIT, No ORDER BY

Generate:

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. Multiple fields can be found by space separation.

The implementation functions are as follows:

function search($keyword,$table,$field)  

{  

//========================================================  

  

// Description of formal parameters:   

//keyword For keywords, such as "Beijing Capital"   Direction   Train ". With or without spaces   

//table Is the table name, such as enter_gongyin_pic .   

//field For field combinations, such as finding 1 Write a field  name   

// If you look for more than two, use  name+picdir  

//========================================================  

// First determine field  

$new_field=explode("+",$field); // Press + Stripping   

$field_count=count($new_field); // Number of results obtained   

  

  

$newstring=explode(" ",$keyword); // Strip by space   

$newstring2=array();  

   // Remove the useless space uncle element from the string   

   $i=0;  

   foreach ($newstring as $key => $value) {  

   if($value!="")  

   {  

   $newstring2[$i]=$value;  

   $i++;  

   }  

   }  

// Remove the useless space uncle element from the string,   

          

$result_count=count($newstring2); // Number of results obtained   

  

// Generate the following SQL Statement   

  

  

//********************** if($field_count==1) // Looking for 1 Fields  START ****************************  

if($field_count==1) // Looking for 1 Fields   

{  

if($result_count==1) // Judge if it is 1 Key segments   

   {  

   $newstring_search=$newstring2[0];  

$sql="SELECT *   

FROM `$table`   

WHERE `".$new_field[0]."` LIKE '%$newstring_search%'";  

   }  

       

   if($result_count>1) // If it is a plurality of key segments, it is judged   

   {  

  

$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) // Looking for 1 Fields  END ****************************  

  

  

//********************** if($field_count>1) // Find multiple fields  START ****************************  

if($field_count>1) // Find multiple fields , At this time $new_field Yes 1 An array of. Have multiple fields   

{  

if($result_count==1) // Judge if it is 1 Key segments   

{  

        $newstring_search=$newstring2[0]; //$newstring_search Is a keyword   

        $sql="SELECT *   

        FROM `$table`   

        WHERE ";  

        $sql_add="";// Newly added 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) // If it is a plurality of key segments (a plurality of keywords), it is judged ==========================  

{  

$sql="SELECT *   

FROM `$table`   

WHERE ";  

$sql_add="";// Newly added field   

foreach ($new_field as $key => $value)  

{  

  if($key==0) // Encounter $new_field[0] Time   Example: `a` LIKE '%1%' OR `a` LIKE '%2%' OR `a` LIKE '%3%'  

   { // Nesting 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."%'";  

                   }  

        }  

    // Nesting foreach  

   }  

   else   

   //( If it is multi-field, such as checking name+picdir Table ) Begin FOREACH Continuous loop, each execution ELSE $new_field[1] $new_field[2] $new_field[3] .   

   // The corresponding value is $value  

  {  

   // Nesting 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."%'";  

                   }  

           }  

   // Nesting foreach  

   }  

          

}//foreach ($new_field as $key => $value) End   

$sql=$sql.$sql_add;  

}//if($result_count>1) End   

}//if($field_count>1)  End   

//********************** if($field_count>1) // Find multiple fields  END ****************************  

return $sql;  

}

I hope this article is helpful to everyone's PHP programming.