SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Posts
    613
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Advanced SQL Question

    I am trying to create a serch. In this search the user can search for something like:

    "This is a test search"

    it will search the database for

    field LIKE '%This%' OR field LIKE '%test%' OR field LIKE '%search%'

    this it does just fine and gives me the search results that I want. The problem is that the user can also select different categories they wish to search in. so if they select PHP and MYSQL as their categories then it has to search a types field for those id's.

    IE:
    it has to find '1,2' IN '1,10,2,3,6,12' and return true.

    I cannot seem to figure this one out.

    Thanks in advance.

  2. #2
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    Moscow, Russia
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, would you post CREATE TABLE statement for the table being searched because it's not clear how IDs stored and what datatypes used

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Webnet
    IE:
    it has to find '1,2' IN '1,10,2,3,6,12' and return true.

    I cannot seem to figure this one out.
    no wonder

    it's not possible in sql

    the problem is that your comma-separated list if id numbers is not in first normal form

    redesign your table and it will not only be simple to query but also efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    Moscow, Russia
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Webnet, but it's possible using MySQL server proprietary extensions: SET datatype and FIND_IN_SET function. Check table searching for similar discussion

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yes, but look at the example given -- find '1,2' IN '1,10,2,3,6,12'

    FIND_IN_SET will return false

    you have to test each of '1,2' individually

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    Moscow, Russia
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    you have to test each of '1,2' individually
    That's right, but i certainly assumed that '1,2' was for the sample only and the sequence doesn't matter, i.e. we need to find both '1' & '2' chars within search string, but not '1,2' string

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    so how would you write the sql to find '1,3,7'

    you would have to break it apart, and do a separate FIND_IN_SET for each of 1, 3, and 7, or alternatively something like
    Code:
    where concat(',',field,',') like concat('%,', 1, ',%')
      and concat(',',field,',') like concat('%,', 3, ',%')
      and concat(',',field,',') like concat('%,', 7, ',%')
    and the only way to break up '1,3,7' is by using a script

    (actually, you could probably do it with a cross-join to an integers table (numbers of commas in '1,3,7' string plus 1, but now it's getting really complex as sql)

    my point was, don't try to do this with sql, it's (next to) impossible, you'll have to write some code anyway

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •