Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Aug 2006
    Location
    Sweden
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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:
    Code:
    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...

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.

  • #3
    New Coder
    Join Date
    Aug 2006
    Location
    Sweden
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Code:
    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:
    Code:
    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 too?
    Last edited by bitbob; 03-19-2007 at 01:06 PM.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.

    Code:
    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

  • #5
    New Coder
    Join Date
    Aug 2006
    Location
    Sweden
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:
    Code:
    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:
    Code:
    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.)
    Last edited by bitbob; 03-19-2007 at 03:10 PM.

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.

  • #7
    New Coder
    Join Date
    Aug 2006
    Location
    Sweden
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How can I - if possible - use your syntax together with my REGEXP?

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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •