CODEWITHSUNDEEP
phpmysql
user3523177
user3523177

Reputation: 51

How to multiple keyword search with php

I have a database of tb_user and I want to search using keywords. if enter these keywords via text field. I am able to display the results if the user enters one search word in the text field however i am not sure how to perform the query if the user enters multiple keywords separated by a comma in the text area. please help

I have column in my table that contains the values like that:

user_current_location

  1. Delhi,bihar,mumbai
  2. Noida
  3. Pune,delhi
  4. Goa,Up,Simla
  5. delhi

There are probably better ways, but If I had to achieve it with existing knowledge, I would do something like this:

$wordString = $_POST['search'];

if (strpos($wordString, ",")){

  $words = explode(",", $wordString);
  $sql = "SELECT * FROM tb_user WHERE";

  foreach($words as $word){
    $sql.=" field LIKE '%$word%' OR";
  }

  $sql = substr($sql, 0, -3);
} 
else {
  /* NO comma separated values, build normal query */
  $sql = "SELECT * FROM tb_user WHERE user_current_location LIKE '%$wordString%'";
}

If the user enters 'delhi,noida' the resulting query would be:

"SELECT * FROM tb_user 
WHERE user_current_location LIKE '%delhi%' 
OR user_current_location LIKE '%noida%'"

Upvotes: 1

Views: 818

Answers (2)

Levite
Levite

Reputation: 17637

First I have to mention, that depending on what you are using to access the database (PDO or mysql_*), you should make it safe against sql-injection, and depending on that you would have your appropriate escaping in the foreach loop.

Other than security, I see no problem with doing it your way (except maybe filtering empty strings so something like delhi, does not match everything (%dehli%, and %%).

E.g.:

foreach ($words as $word)
{
    $sql .= " field LIKE '%" + mysql_real_escape_string($word) + "%' OR ";
}

(It is actually recommended to use PDO instead, but I thought you might be using the mysql_* functions)

Upvotes: 1

majidarif
majidarif

Reputation: 20105

Or you can use REGEX:

SELECT *
  FROM tb_user 
 WHERE user_current_location REGEXP "*delhi*|*noida*";

Upvotes: 1

Related Questions