Page 1 of 1

User Network search by First, Last, User and First last

Posted: Sat Sep 11, 2010 11:58 pm
by keverw

Code: Select all

$searchTerm = $_GET['q'];
$searchTerm = mysql_real_escape_string($searchTerm);
$sql = mysql_query("SELECT * FROM Users WHERE firstname LIKE '%$searchTerm%' OR lastname LIKE '%$searchTerm%' OR username LIKE '%$searchTerm%' OR (firstname LIKE '%$searchTerm%' AND lastname LIKE '%$searchTerm%') AND email_activated='1' AND banned='0'"); 
If i search Billy, It pulls up Bill Gates
If i search Gates, It pulls him up in search.
If i search Bill, It pulls him up in search.

If i search Bill Gates, It does not pull him up in search.

Not really sure how to fix this. It would be helpful if i could get help fixing this as i need to build a few different search engines for my site.

Re: User Network search by First, Last, User and First last

Posted: Sun Sep 12, 2010 1:56 am
by JellyFish
Try adding a FULLTEXT index to your table (make sure the engine you're using for the table is MyISAM):

Code: Select all

ALTAR TABLE `User` ADD FULLTEXT (`firstname`, `lastname`, `username`);
Than use this query:

Code: Select all

SELECT * FROM `Users` WHERE MATCH (`firstname`, `lastname`, `username`) AGAINST ('$searchTerms' IN BOOLEAN MODE);
I think you'll need to create separate indexes for each column if you want to match them individually.

Re: User Network search by First, Last, User and First last

Posted: Sun Sep 12, 2010 10:59 am
by keverw
Thanks! That helped me a lot.

Re: User Network search by First, Last, User and First last

Posted: Sun Sep 12, 2010 8:14 pm
by JellyFish
Your welcome $hithead!! :twisted: