How to search properly using keyword in PHP and MySQL

Question

I can not search the value from Database putting the key word using PHP and MySQL. Here is my code:

$searchKey=$_GET['searchKey'];
$special_id=$_GET['special_id'];
$city_id=$_GET['city_id'];
$day_id=$_GET['day_id'];
$keyword = '%'.$searchKey.'%';
$data=array();
$sql =mysqli_query($connect,"SELECT * FROM  db_restaurant_basic  WHERE rest_name LIKE '".$keyword."' and special='".$special_id."' and city='".$city_id."' and status=1 GROUP BY member_id ORDER BY member_id DESC ");
if(mysqli_num_rows($sql) > 0){
    while($row=mysqli_fetch_array($sql)){
        $data[]=array("restaurant_name"=>$row['rest_name']);
    }
}
$result=array("data"=>$data); 
echo json_encode($result);

When i am calling the following URL to get the result.

http://localhost/spesh/mobileapi/categoryproduct.php?item=2&acn=5&special_id=1&city_id=1&day_id=4&searchKey=on

Its giving me the following result.

{"data"[{
   "restaurant_name":"Sonoma on 9th"
},{
    "restaurant_name":"Jamesons Irish Pub 17th Ave. SW"
},{
   "restaurant_name":"Jamesons Irish Pub Brentwood NW"
},{
    "restaurant_name":"National on 17th"
}
]
}

My problem is I need to search the Restaurant name which contains the keyword on but I am getting some other Restaurant name which doesn't contain keyword on. I need when user will search the restaurant name using keyword on it should give the restaurant name which contains that word on only not other restaurant name.

score 3 · Accepted Answer · answered Jul 21 '16 at 05:49

3

you are using keyword "like" in your query which search "on" everywhere in restaurant name.

you should use spaces to make it specific

$keyword = '% '.$searchKey.' %';

check the spaces after first % sign and before the second one

answered Jul 21 '16 at 05:49

Muhammad Nouman

180
4

Jens · Answer 2 · 2016-07-21T06:02:34.970

2

add blanks around the keyword:

$keyword = '% '.$searchKey.' %';

By the way: Learn abut prepared statements to prevent SQL-injection.

edited Jul 21 '16 at 06:02

answered Jul 21 '16 at 05:45

Jens

67,715
15
98
113

Tough luck. You answered first, but OP still picks the other one. Upvoted you. – Logan Wayne Jul 21 '16 at 06:27
@LoganWayne Not the first answer should be accepted. The best answer should be accepted and maybe the other is better. – Jens Jul 21 '16 at 06:37
Looks the same to me – Logan Wayne Jul 21 '16 at 06:42

score 0 · Answer 3 · edited Dec 11 '21 at 06:49

Instead of using direct substitution values, you could use below methods to avoid sql injection.

You basically have two options to achieve this:

Refer docs

For example, using MySQLi (for MySQL):

$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name);

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // do something with $row
}

Please refer How can I prevent SQL-injection in PHP?

How to search properly using keyword in PHP and MySQL

3 Answers3