用于多个关键字的PHP搜索功能

Question

I have created a search bar where users can enter their keywords and the form php code explodes the input to form an array of words. I have then used a foreach loop to traverse through the list of words and search for match phrases in two tables in my database.

The key functionalities are as follows:

• Support multiple word inputs
• If the user is not admin only search the "ecom_products" table
• If the user is logged in as admin then search the "ecom_products" table AND the "ecom_users" table where only other admin details are returned (not customer info)

Everything worked fine about two weeks ago but now for some reason it throws an error when I search for more than one keyword when logged in as admin. I have checked my code extensively and I have tried making changes to it but it still doesn't work. Below is the mysql_error that I was shown:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR fname LIKE '%string%' OR lname LIKE '%string%' OR email LIKE '%string%' OR ma' at line 1

Below is my code:

if(empty($_POST)=== false){
        require ('core/dbconnection.php');
        $output = '';
        $error = '';
        $input = mysql_real_escape_string($_POST['search_input']);
        $i=0;
        if($input){
            $keyword = explode(" ", $input);
            if($admin == 1){
                //If a user is logged in and the user is an Admin.
                $search_items = "SELECT * FROM ecom_products WHERE ";
                $search_users = "SELECT * FROM ecom_users WHERE ";
                foreach($keyword as $k){
                    $k = mysql_real_escape_string($k);
                    $i++;
                    if($i == 1){
                        $search_items .= "name LIKE '%$k%' OR description LIKE '%$k%'";
                        $search_users .= "is_admin='1' AND (fname LIKE '%$k%' OR lname LIKE '%$k%' OR email LIKE '%$k%' OR main_tel LIKE '%$k%'";
                    }else{
                        $search_items .= " OR name LIKE '%$k%' OR description LIKE '%$k%'";
                        $search_users .= " OR fname LIKE '%$k%' OR lname LIKE '%$k%' OR email LIKE '%$k%' OR main_tel LIKE '%$k%'";
                    }
                    $search_users .= ") ORDER BY lname DESC";
                }

PS. I'm in the process of updating all my "msql" extensions to "mysqli", in fact I tried mysqli and it did not even run the query so I want to fix this issue before altering my mysql exntensions. Your help will be much appreciated

Answer 1

You need to move

$search_users .= ") ORDER BY lname DESC";

outside the foreach($keyword as $k) loop. Right now it is adding at the end of each loop, so your query will look like this

SELECT * FROM ecom_users WHERE 
  is_admin='1' AND (fname LIKE '%$k%' OR lname LIKE '%$k%' OR email LIKE '%$k%' OR main_tel LIKE '%$k%'
  ) ORDER BY lname DESC
  OR fname LIKE '%$k%' OR lname LIKE '%$k%' OR email LIKE '%$k%' OR main_tel LIKE '%$k%'
  ) ORDER BY lname DESC

It should be

foreach($keyword as $k){
   $k = mysql_real_escape_string($k);
   $i++;
   if($i == 1){
      $search_items .= "name LIKE '%$k%' OR description LIKE '%$k%'";
      $search_users .= "is_admin='1' AND (fname LIKE '%$k%' OR lname LIKE '%$k%' OR email LIKE '%$k%' OR main_tel LIKE '%$k%'";
   }else{
      $search_items .= " OR name LIKE '%$k%' OR description LIKE '%$k%'";
      $search_users .= " OR fname LIKE '%$k%' OR lname LIKE '%$k%' OR email LIKE '%$k%' OR main_tel LIKE '%$k%'";
   }
   // REMOVE FROM HERE
}
// PLACE HERE
$search_users .= ") ORDER BY lname DESC";