Mysql Search Box using LIKE

Question

I created a search engine on my website where user can input whatever they like and show the result. Below is the code I use to get the result but it is not working because of the syntax error.

In my database I have 10 rows, each rows contain 4 columns(id, author, second_author, book_name). My goal is, if the user enter a name that is found in 'author', I want to retrieve that result, 'OR' if they enter a name that is found in book_name I want to retrieve that result and so on for the other columns..

I know the proper way to do this is by using LIKE Operator, but how about if you want to compare the keywords to several columns, what should you do?

I've tried this but was not working:

SELECT * FROM book_list WHERE author OR second_author OR book_name LIKE '%".$search_key."%'

score 2 · Answer 1 · answered May 01 '13 at 09:07

2

It's just a syntax tweak:

SELECT * FROM book_list
WHERE author LIKE '%".$search_key."%'
OR second_author LIKE '%".$search_key."%'
OR book_name LIKE '%".$search_key."%'

Hope that helps

answered May 01 '13 at 09:07

Chris

2,955
1
30
43

@Waygood: how do you know the way `$search_key` is sanitized? – zerkms May 01 '13 at 09:13
1

A fair point, but the OP doesn't specify whether has has 'cleaned' $search_key for SQL Injection risks, this is a sql syntax question after all. – Chris May 01 '13 at 09:13
@zerkms I dont know and neither do you. Thats the point! Suggesting: you should consider sanitizing if you haven't already – Waygood May 01 '13 at 09:27
@Waygood: we don't know either how the data is used. If it is output to the HTML page - there is a risk for XSS and CSRF. If it's sent to the C++ daemon - there is a risk of stack overflow if it was poorly implemented. How about mentioning every problem we may guess is relevant? – zerkms May 01 '13 at 09:30
@zerkms Fabio SUGGESTED it. I posted a funny link, stop Trolling – Waygood May 01 '13 at 09:33

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

Your query has an error, you have to search on each column as

SELECT * FROM book_list WHERE author  LIKE '%".$search_key."%' OR second_author  LIKE '%".$search_key."%' OR book_name LIKE '%".$search_key."%'

I would also like you to be aware that you are at risk of sql injection, have a look here How can I prevent SQL injection in PHP?. You should use prepared statment to avoid any risk

edited May 23 '17 at 11:43

Community

1
1

answered May 01 '13 at 09:09

Fabio

23,183
12
55
64

Mysql Search Box using LIKE

2 Answers2