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!!
