I am trying to create at user search for my website and I'm not sure where to start.

My table name is users with user_id, user_name, name

I'd like to be to search and have a page come up with the top 5 closest results.

Sorry if this question has been asked and that I have no code.

Thanks I will check those sites out. I make videos and I am a graphic designer for my company so they automatically assume I am a web programmer.

It doesn't seem to like it. One of the links says to say

"SELECT * FROM users LIKE %'John'%"

But I get an error and the query doesn't run when I have % in there.

Put the percentage signs inside the single quotes.

Member Avatar

diafol

Using LIKE has always bugged the hell out of me, as it doesn't take relevancy into account (e.g. exact matches before partials). I did a bit of searching for 'fulltext indexing'. I found this thread:

http://stackoverflow.com/questions/1016804/how-to-sort-mysql-fulltext-search-results-by-relevancy

From which the following example has been cobbled:

ALTER TABLE `users` ADD FULLTEXT INDEX `SEARCH`(`username`);

SELECT *, MATCH(username) AGAINST ('John' IN BOOLEAN MODE) AS relevance 
FROM `users`
WHERE MATCH(username) AGAINST ('John' IN BOOLEAN MODE)
ORDER BY relevance DESC

I *think* you can use multiple fields for searching in the MATCH list too.

Note that you have to have MyISAM tables, to use full-text search.

commented: good spot +14