创建搜索字段

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.

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.

SELECT * FROM student 
    WHERE username LIKE REPLACE(username, ' ', '') = REPLACE("Twitt er", ' ', '')

I'm not exactly sure how you would like to treat these, you could add this to your WHERE clause to also search against the firstname and lastname fields with a space in between them.

CONCAT(`firstname`, ' ', `lastname`) LIKE 'jo%'

or you may want to drop the wildcard and do an exact search.

CONCAT(`firstname`, ' ', `lastname`) = 'jo'

You could assume that the existence of a space means that the user is searching for a first/last name combination. (in fact your UI could state this).

then use

$name = split(' ' , $incoming);

Then you create a conditional check to whether $name[1] exists, and then work out whether to search for

$where_clause = WHERE firstname = $name[0] AND lastname = $name[1];

If that fails, fall back to:

$where_clause = WHERE firstname = $name[0] 

or

$where_clause = WHERE lastname = $name[1] 

and so on.

Then use it like:

SELECT `student_id` FROM `student` . $where_clause

This is somewhat pseudocodish, but you get the picture I hope.

There are more elegant ways of achieving such an sql-builder, but I offer it as a way of illustrating how you could start to think about it -- the "use FULLTEXT" comments aside.

There are two ways to do this:

1) As you are trying to

`

     $search_term= "Mike Brant";
      $term_part = preg_split('/ /', $search_term);
     /*
      term_part[0]=Mike
      term_part[1]=Brant
    */
     foreach($term_part as $value){
    $like. = " `username` LIKE '".$value."%' OR `firstname` LIKE '".$value."%' OR `lastname` LIKE '".$value."%' OR `surname` LIKE '".$value."%' OR `email` LIKE '".$value."%' OR "
    }
    $like = substr($like, 0, -3);
    $sql = "SELECT `student_id` FROM `student` WHERE ".$like.";
`

2) Use FULLTEXT search table engine MYISAM learn more about FULLText

FULLTEXT is best option for search applications