advanced search (mysql)

[eluser]tkyy[/eluser]
im going to be creating a form where you can search users by first and last name, but i want the application to be smart and return results if you have only entered a first name.

of course i was thinking something along the lines of this pseudo code

Code:

if input post name contained a space

check if entry exists in users table
return results

elseif input post did not contain space

do a LIKE statement and return results from the firstname table where name LIKE "name%"

endif

since the usernames and passwords are stored in seperate rows, i think im going to do a statement like this when a user searches for a first and last name

Code:

if input post contains " "

explode the string on the space
SELECT statement on the users table for people with first name = the first part of the string

if there are results
do a similar if statement to above on the last name string
endif

endif

but i really want to avoid the overhead of a like statement, even if indexing applies to the statement while the wildcard is at the end of the string- i just really dont like "LIKE" and i sure as hell dont want to use regex mysql commands in an application of this size.

also i would like it to be a little smarter than the average user-search engine, so am debating taking some characters off the end of the input strings and doing additional queries (or using additional OR statements). all in all it could turn out to be a really expensive query

i was definitely thinking query caching for this, but id be interested in hearing alternatives. ive been toying with creating seperate, sharded database tables to store userdata for search as well (maybe a directory shard by the first character in the first name) but also have had problems with that, because if i do that i may as well get sharding out of the way completely and just completely shard the users table.

[eluser]TheFuzzy0ne[/eluser]
Why not have the three fields in your table. Username, first name and surname? The question is, to people have to type the name correctly, or can they not type a partial name, or better yet, partial first and last name? For Example, if a user wanted to look up David Holts, they could search for Dav H or something like that.

[eluser]tkyy[/eluser]
the 3 fields are in my table (didnt mean to say rows, been a long day)

thats exactly what ive been thinking about doing as i said above with the partial names. partial first names would be great and so would nicknames, queries that would be relatively inexpensive as well.

[eluser]jedd[/eluser]
I'd probably wimp out and go for multiple fields in your form.

Remember that some names (certainly some surnames) have spaces in them - so you can't assume that if you have a space then you've necessarily got a first/surname situation.

[eluser]tkyy[/eluser]
very true- ill wait for some more input, right now i have to work out how im going to display the page- im doing a jquery browse form, but im storing the search params inside of a new cookie so if the user hits a result, the page changes with a flashdata status-box that gives them the option of returning to the search page and the ajax content that loads on ready(). when they come back to the search page the query was cached and the paramaters are the same.

it makes urls pretty too, www.url.com/search/25 etc.

[eluser]Jondolar[/eluser]
If you are interested in doing "fuzzy" searches, you might want to store the first name and last name as a soundex of those and then search on the soundex.