PHP & SQL - A better Search

PHP
1

I currently have a search page for example:

http://www.freemoviedb.net/search/avatar.html

Query:


$get = $_GET['search'];
mysql_query("SELECT * FROM `table` WHERE `name` LIKE '%" . $get . "%'");

Obviously that isn’t how I’m doing it… its a lot more secure than that, thats just an example.

I want to enhance this… as people do not search for the exact thing, as you probably know.

Some people may search Avatar 2009, The Avatar Movie, etc.

What suggestions do you have to improve search results?

2

First of all, don’t forget to escape user input if you’re using it in a query:


$get = $_GET['search'];
mysql_query("SELECT * FROM `table` WHERE `name` LIKE '%" . mysql_real_escape_string($get) . "%'");

You might want to look at fulltext indexing: http://devzone.zend.com/article/1304 , I think that’s what you’re looking for :slight_smile:

3

Obviously that isn’t how I’m doing it… its a lot more secure than that, thats just an example.

I have my own premade functions, everythings secure… I just typed out the php quickly for the post as an example, it wasn’t a direct copy.

I will look at that now.

Thank you

4

Fulltext search could be your right choice there but there are some restrictions like you must use MyISAM engine to work with this indexing. Read the following page once and go.
http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

Otherwise if you are using InnoDB then you should do something else like just split the words by space and use each words with LIKE operators in all possible fields/columns.