searching across multiple columns

[1internet]

I am trying to create a search form with one field that will search through one table, but through all the columns. I just can't seem to work it out. Also is there some way to prioritize the results, e.g. if the keyword is in the title then rank it above the results that have the keyword in the description?

[stijn0713]

I just can't seem to work it out.

 

then you might want to take a better look at the select query http://dev.mysql.com.../en/select.html and especially the WHERE statement

 

Also is there some way to prioritize the results, e.g. if the keyword is in the title then rank it above the results that have the keyword in the description?

there is, obviously Edited January 20, 2013 by stijn0713

[1internet]

I understand WHERE statments, but I just don't know what the query would be if I wanted to search multiple fields.

WHERE `firstName` LIKE '%".$searchFirstName."%, but what if I wanted to search other fields, and how do I priortize one over the other, without duplicate entries?

[stijn0713]

e.g. :

 
$sql = "SELECT * FROM test WHERE car LIKE '%$search%' OR colour LIKE '%$search%' ORDER BY
 case
  when car LIKE '%$search%' then 1
  when colour LIKE '%$search%' then 2
 end";

[stijn0713]

forgot: if you want only distinc values, you need to add group by car, colour before order by