User Search

[thecameraman]

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.

[pritaeas]

You can use a SELECT query using LIKE and LIMIT. Start here:

http://php.net/mysql_query
http://dev.mysql.com/doc/refman/5.6/en/select.html

[thecameraman]

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.

[thecameraman]

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.

[pritaeas]

Put the percentage signs inside the single quotes.

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.

[pritaeas]

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

[thecameraman]

thank you guys!