PHP search function for multiple keywords

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

What does $search_items and $search_users look like after your logic has finished building it? — MonkeyZeus
– MonkeyZeus, Commented Mar 15, 2014 at 21:50

Sean · Accepted Answer · 2014-03-15 21:55:33Z

2

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";

answered Mar 15, 2014 at 21:55

Sean

12.4k3 gold badges31 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rumesh Over a year ago

YESS!! It worked, can't believe it was something dumb like that. Thanks :)

Collectives™ on Stack Overflow

PHP search function for multiple keywords

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related