Multi word search in PHP/MySQL

Question

I'm struggling to create a search that searches for multiple words. My first attempt yielded no results whatsoever and is as follows:

  require_once('database_conn.php');
  if($_POST){
  $explodedSearch = explode (" ", $_POST['quickSearch']);


  foreach($explodedSearch as $search){
  $query = "SELECT * 
            FROM jobseeker 
            WHERE forename like '%$search%' or surname like '%$search%' 
            ORDER BY userID 
            LIMIT 5";
  $result = mysql_query($query);
}

while($userData=mysql_fetch_array($result)){
    $forename=$userData['forename'];
    $surname=$userData['surname'];
    $profPic=$userData['profilePicture'];
    $location=$userData['location'];

    echo "<div class=\"result\">
    <img class=\"quickImage\" src=\"" . $profPic. "\" width=\"45\" height=\"45\"/>
    <p class=\"quickName\">" . $forename . " " . $surname . "</p>
    <p class=\"quickLocation\"> " . $location . "</p>
    </div>";

}
}  

I also tried the following, which yielded results, but as you can imagine, I was getting duplicate results for every word I entered:

if($_POST){
$explodedSearch = explode (" ", $_POST['quickSearch']);


foreach($explodedSearch as $search){
$query = "SELECT * 
          FROM jobseeker 
          WHERE forename like '%$search%' or surname like '%$search%' 
          ORDER BY userID 
          LIMIT 5";
$result .= mysql_query($query);


while($userData=mysql_fetch_array($result)){
    $forename=$userData['forename'];
    $surname=$userData['surname'];
    $profPic=$userData['profilePicture'];
    $location=$userData['location'];

    echo "<div class=\"result\">
    <img class=\"quickImage\" src=\"" . $profPic. "\" width=\"45\" height=\"45\"/>
    <p class=\"quickName\">" . $forename . " " . $surname . "</p>
    <p class=\"quickLocation\"> " . $location . "</p>
    </div>";
}
}
}

I'm pretty much at a loss as to how to proceed with this, any help would be greatly appreciated.

EDIT:

if($_POST){
$quickSearch = $_POST['quickSearch'];
$explodedSearch = explode (" ", trim($quickSearch));


$queryArray = array();

foreach($explodedSearch as $search){
$term = mysql_real_escape_string($search);
$queryArray[] = "forename like '%" . $term .  "%' surname like '%" . $term . "%'";
}

$implodedSearch = implode(' or ', $queryArray);

$query="SELECT *
        FROM jobseeker
        WHERE ($implodedSearch)
        ORDER BY userID
        LIMIT 5";

$result = mysql_query($query);

while($userData=mysql_fetch_array($result, MYSQL_ASSOC)){
    $forename=$userData['forename'];
    $surname=$userData['surname'];
    $profPic=$userData['profilePicture'];
    $location=$userData['location'];


    echo "<div class=\"result\">
    <img class=\"quickImage\" src=\"" . $profPic. "\" width=\"45\" height=\"45\"/>
    <p class=\"quickName\">" . $forename . " " . $surname . "</p>
    <p class=\"quickLocation\"> " . $location . "</p>
    </div>";

}
}

Solution 1

I've been working on the same subject (search with keywords) for a while and this how i did it :

$words = $_POST['keywords'];
if(empty($words)){
    //redirect somewhere else!
}
$parts = explode(" ",trim($words));
$clauses=array();
foreach ($parts as $part){
    //function_description in my case ,  replace it with whatever u want in ur table
    $clauses[]="function_description LIKE '%" . mysql_real_escape_string($part) . "%'";
}
$clause=implode(' OR ' ,$clauses);
//select your condition and add "AND ($clauses)" .
$sql="SELECT * 
      FROM functions 
      WHERE
      user_name='{$user_name}'
      AND ($clause) ";
$results=mysql_query($sql,$connection);
 if(!$results){
    redirect("errors/error_db.html");
 }
 else if($results){
 $rows = array();
<?php 
 while($rows = mysql_fetch_array($results, MYSQL_ASSOC))
{
   // echo whatever u want !
}
?>

-- Now this is how it look when i tried to run it with FULLTEXT search : But you should set the table type as "MyISAM"

<?php
$words = mysql_real_escape_string($_POST['function_keywords']);
if(empty($words)){
    redirect("welcome.php?error=search_empty");
}
//if the columns(results)>1/2(columns) => it will return nothing!(use "NATURAL LANGUAGE"="BOOLEAN")
$sql="SELECT * FROM functions
     WHERE MATCH (function_description)
     AGAINST ('{$words}' IN NATURAL LANGUAGE MODE)";
$results=mysql_query($sql,$connection);
 if(!$results){
    redirect("errors/error_db.html");
 }
 else if($results){
$rows = array();
while($rows = mysql_fetch_array($results, MYSQL_ASSOC))
{
     // echo 
}
}
?>

Solution 2

Perhaps what you are looking for is a MySQL full-text search.

For your example, you could do something like:

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $search = $_POST['quickSearch'];
    // Todo: escape $search
    $sql = "
        SELECT
            *,
            MATCH (`forename`)
            AGAINST ('{$search}' IN NATURAL LANGUAGE MODE) AS `score`
        FROM `jobseeker`
        WHERE
            MATCH (`forename`)
            AGAINST ('{$search}' IN NATURAL LANGUAGE MODE)";
    // Todo: execute query and gather results
}

Note that you will need to add a FULLTEXT index to the column forename.

Solution 3

Take a look at MySQL fulltext searches, if you must use MySQL. Otherwise take a look at SOLR, which is a fulltext search engine. You can use MySQL and SOLR in combination to provide enterprise level search capabilities for your apps.

Solution 4

here's what i did

if (isset($_POST['search'])){
$words = mysql_real_escape_string($_POST['searchfield']);   
$arraySearch = explode(" ", trim($words));       
$countSearch = count($arraySearch);
$a = 0;
$query = "SELECT * FROM parts WHERE ";
$quote = "'";
while ($a < $countSearch)
{
  $query = $query."description LIKE $quote%$arraySearch[$a]%$quote ";
  $a++;
  if ($a < $countSearch)
  {
    $query = $query." AND ";
    }   
  }
    $result=mysql_query($query) or die(error);

//you could just leave it here, short and sweet but i added some extra code for if it doesnt turn up any results then it searches for either word rather than boths words//

$num = mysql_num_rows($result);
if ($num == 0){
 $a = 0;
 $query = "SELECT * FROM parts WHERE ";
while ($a < $countSearch)
{
  $query = $query."description LIKE $quote%$arraySearch[$a]%$quote ";
  $a++;
  if ($a < $countSearch)
  {
    $query = $query." OR ";
    $msg = "No exact match for: $words. Maybe this is what you're looking for though? If not please try again.";
    }

  }
  }
  $result=mysql_query($query) or die($query);
  if (mysql_num_rows($result) == 0){
    $msg = "No results, please try another search";
    }

}

View more solutions

Comments

[Richie]

I'm struggling to get my head around this way of doing things? Would I have to build a query for each column?

[Richie]

SOLR is very interesting but way beyond the scope of what I am working on right now, which is the searching and messaging features of a social network that is being built as a group project for university. However I will bookmark the link you provided and read up on it for future projects. Thanks a lot.

[Mike Purcell]

@RichieVikinglordTerry: No problem. You definitely want to check out MySQL's fulltext searching then. Just note that if you implement fulltext it can only be done on MyISAM tables (vs InnoDB).

[Dejan Marjanović]

Using LIKE '%x%', isn't using indexes, just so you know.

[Med Akram Z]

what is the difference between using an index or not ?

[Dejan Marjanović]

Your search will be pretty slow on large tables. Read this en.wikipedia.org/wiki/Index_%28database%29

[Med Akram Z]

soo should i replace this method by a fulltext search ?

[Richie]

Hey thanks for the heads up, your answer seems to be the most similar to the level I am currently working at. The only thing is I am getting the following error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource And I have no idea why

[Med Akram Z]

i think its because the query didnt work at first .. verify if u really conected to your database and all ur variables and try again ..

[Med Akram Z]

yea im a beginner too and i just want to share info and help , but as the other guy said we need something more powerful since this will work slowly on large tables , but it works fine for me til now .

[Richie]

I have editted my post and added the most up to date version of my code, I am definitely connected to the database, the site wouldn't function without the connection and I am including the same connection file in every page. I can't see any inconsistencies in the variables either. This is going to be one to make me pull my hair out I think haha.

[Med Akram Z]

its hard to figure it out , but when im on something like this i try to search with every line til a get to the error .. try to echo back $sql on ur page (just /* every thing else and echo $sql;) just to see it , and then apply the $sql statement in ur phpmyadmin sql command to see why it not working and then the problem will apear ! (just type any keywords and echo $sql and try it) .

[Richie]

Fantastic advice, I was missing the word "OR" in concatenated string in the foreach loop. Took me a while to find it even with phpMyAdmin. But that was some great advice sir, and I will take it to the grave. Gonna have to do some reading on these fulltext indexed searches for when I graduate, but I am a final year and these things haven't even had a mention yet, so I don't know how relevant they will ever be to my course. Thanks again, you've been a fantastic help.

[Mike Purcell]

@Richie: Nice that it works for you, but those ORs and LIKE are going to crush your database performance. You may want to keep an eye out on your slow query log and runs some EXPLAINS.

[Richie]

@DigitalPrecision yeah, I know what you mean, but the amount of ORs isn't likely to exceed 2 or 3, and the database is pretty small. It's for a university project and they haven't once mentioned to us FULLTEXT searching, the module isn't really even going to be marking us on the quality of our SQL, as long as it works. But I will discuss with my tutor if I will get more marks for FULLTEXT and implement if so. If not I will definitely do some reading on it before I graduate so I have at least working knowledge of it. Thanks for the heads up on why LIKE '%x%' is bad practice.

[scipilot]

@Richie, (or anyone reading this 4yrs later!) no, you can specify multiple columns docs. MATCH (col1,col2,...) AGAINST (expr [search_modifier])