创建搜索字段

Question

So i'm working on this site and i just added a search button. Everything works fine when i use a simple search query like

SELECT `student_id` FROM `student` WHERE `username` LIKE 'jo%' OR `firstname` LIKE 'jo%' OR `lastname` LIKE 'jo%' OR `surname` LIKE 'jo%' OR `email` LIKE 'jo%' 

This will return all id with a name that matches john from a database by looking at the fields 'username', firstname, lastname, and so on.

Th problem however is, when the user inserts a space between the search parameter (which in essence means he is searching a student by both names), the result is nothing.

I figured that this is because the it is looking for the search parameter (say, 'john doe') independently in each field.

How do i work around this.

Answer 1

If you want to get into searching/matching across multiple columns, you really need to look into MySQL full text search capabilities. See this link for documentation to get you started:

http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html

This will scale much better than trying to LIKE across a bunch of columns.