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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
keverw
Forum Newbie
Posts: 2
Joined: Sat Sep 11, 2010 11:53 pm

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

Post 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.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

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

Post 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.
keverw
Forum Newbie
Posts: 2
Joined: Sat Sep 11, 2010 11:53 pm

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

Post by keverw »

Thanks! That helped me a lot.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

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

Post by JellyFish »

Your welcome $hithead!! :twisted:
Post Reply