SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    410
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    concat for search results

    Hey...

    From the db 'products' i have two fields, 'code1 and 'code2'

    Data could be:

    Code 1 Code2
    1002 5660
    1025 1512

    i have a simple query that is used for search results:

    Code:
     $query = "SELECT * FROM products WHERE code1 LIKE \"%$trimm%\" OR code2 LIKE \"%$trimm%\" " ;
    If i search '1002' or '5660' alls fine.....however the actual product codes are (code1.code2), so when i search for 1002.5660 i get no results.

    Ive had a quick look around.... can i define CONCAT AS 'test' then have WHERE test LIKE \"%$trimm%\".....or am i missing the whole point!

    cheers in advance

  2. #2
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can define CONCAT AS test and then have HAVING (not WHERE) test LIKE '%$trimm%'. However doing it like that would force the dbms to do concat on each and every row in the table. No index can be used. So basically it will be really slow for large tables.

    If you always search for code1.code2 (like 1002.5660) then I would recommend splitting the search term in php and then running a query like:
    Code:
    WHERE code1 = '$firstPartOfSearchWord' and code2 = '$secondPartOfSearchWord'

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    410
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey joool

    Unfortunatley the user myay chose a 3d profile (code1) or a colour scheme (code2) or the search for a specific product (code1.code2)

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there no way you can tell if the user is searching for code1 or code2 or code1.code2?

    If there never is a dot in either of the codes then you can at least tell that someone is looking for code1.code2 if there is a dot. Then I think this would work:
    Code:
    // If there is no dot in search term
    WHERE code1 LIKE '$search' or code2 LIKE '$search'
    
    // If there is a dot in search term, split search
    WHERE code1 LIKE '$firstPartOfSearch' AND code2 LIKE '$lastPartOfSearch'


Bookmarks

Posting Permissions

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