Advanced SQL Question

[Webnet]

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.

[Tangerine_Dream]

Hi, would you post CREATE TABLE statement for the table being searched because it's not clear how IDs stored and what datatypes used

[r937]

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

[Tangerine_Dream]

Webnet, but it's possible using MySQL server proprietary extensions: SET datatype and FIND_IN_SET function. Check table searching for similar discussion

[r937]

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

[Tangerine_Dream]

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

[r937]

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

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