Search from web site

Background info
I have a simple search function. Just an input field and a submit button that should search a table in mysql.
I do some regexp stuff in mysql so i use the REGEXP syntax but you don't have to bother about that, since we'll just asume that the user have input "x y z" in the search field.

Problem 1
I have three fields in a mysql table that i want to search. The simplest way I came up with to do this was:

SELECT * FROM table WHERE CONCAT_WS(' ',field1,field2,field3) REGEXP 'x y z'

But this won't work if field1 = z, field 2 = y and field3 = x .
How can I achive this? It's quite impossible to ad an OR statement for every possible combination of x, y and z. (perhaps I will need to search more fields in a go in the future)... Can I change the regexp to be able to match words in any combination?

Problem 2
How do I set up an index in mysql for the kind of search mentioned above? Is it usable to have an index on field1, field2, field3 if you're going to do a concatenation on them anyway?

Thanks! Feel free to come with suggestion or partial solutions...

if x y and z represent three different search terms then is your match only when all three of those are found in a single row but in any order? I'm not sure you were clear on that.

I have only one input field and I've limited the fileds to search to `fname`, `sname` and `city`. So you can search for "Bobo Wieland, Helsingborg" (my name + city). Then x = Bobo, y = Wieland z = Helsingborg. This search will match the coresponding row in the db.
So will "Bobo Helsingborg", but "Wieland Bobo helsingborg" will not match and that's my problem...

Perhaps I could use CONCAT_WS(' ',fname, sname,city,sname,fname,city,fname,sname) which would take care of all combinations (right?), but I can't do that if i want to search more fields.

I've done a similar search once, but that only included two fields and I solved it using:
CONCAT(field1,field2) REGEXP "pattern" OR CONCAT(field2,field1) REGEXP "pattern"


btw, guelphdad; thanks for allways helping out! maybe you have an answer for this one (http://www.codingforums.com/showthread.php?t=108685) too?

Does this return the results you want? It will only return those rows having one of the terms in each column (the order doesn't matter). If you have only two terms (or one) matched, then the row isn't returned.


SELECT
id,
foo,
bar,
qux
FROM yourtable
WHERE foo IN ("Bobo","Wieland", "Helsingborg")
AND qux IN ("Bobo","Wieland", "Helsingborg")
AND bar IN ("Bobo","Wieland", "Helsingborg")
GROUP BY
foo,
bar,
qux

No... I think that approach is to restricted. If I search for "Bobo Wieland Helsingborg" then the query should only match the pattern and don't care about which data is in which field.
So the search "Bobo, Helsingbog" would (should) return:

FNAME SNAME CITY
Bobo Wieland Helsingborg
Bobo Smith Helsingborg
Helsingborg Bobo - -
- Helsingborg Bobo



This is how a search for "Klas Erikson, Stokholm" looks as it is right now:

CONCAT_WS(' ',fname,sname,city) REGEXP '.*(k|c|ck|ch)l(a|ah|ae)s.*(e|ä|è|é)ri(k|c|ck|ch)ss?on.*sth?o(k|c|ck|ch)holm.*'

and this code will match Claes Ericsson from Stockholm. But I want it to match no-matter what combination of the search words are entered ("Erikson Stockholm","Stokholm, Klas Erikson","Klas","Stokholm" etc.)

the query I gave above does NOT care what is in what field, only that all three search terms are matched.

What happens if I search for
foo, bar, qux
and find a row that contains
qux, nickel, frisbee

should that row be returned because ONE of the search terms is matched?

If so then change my ANDs to ORs and then when any one of the search terms matches any of those columns then the row is returned.

How can I - if possible - use your syntax together with my REGEXP?

When I tried I couldn't even get:
sname IN ('eric')
to match ericsson. Tried '%eric%' to no vail. And using
sname IN (REGEXP 'eric')
didnät work either...