Help with REGEXP or alternative solution sought

[lxndr]

I have a database field that is composed of 16 characters each of which can be set to '0' or '1'.  The characters represent data from a form with 16 checkboxes.  What I want to able to do now is query this field.

 

I was almost able to do it using REGEXP with the following as an example:

 

SELECT * FROM `library` WHERE responses REGEXP '^1......1........$'

 

This works if searching for items that have both the 1st and 8th elements set to 1 but what I really want is for it to search for rows where this field has EITHER 1st or 8th elements set to 1.

 

e.g.

 

If the user does a search and fills out both 1st and 8th checkboxes I'd like the MYSQL query to return the following as an example

 

1000000000000000

0000000100000000

1000000100000000

 

currently it's acting in a logical 'AND' kind of way and only returning:

 

1000000100000000

 

Can anyone tell me the best way to do what I want?

 

TIA

 

 

[teng84]

sample output and input?

[lxndr]

sample output and input?

 

I'm not sure what you mean by sample output and input but maybe if I paint one specific example I can apply the proposed solution as a generality.

 

The database field (called responses) is VARCHAR, length 16

 

Example data item:

 

1000000100000000

 

 

I then have an HTML form with 16 checkboxes on, i.e.

 

Item 1

Item 2

 

.

.

.

Item 16

 

Now I want the above data item (1000000100000000)  to be a match if the user selects EITHER item 1 or item 8 and was trying to generate the most elegant SQL solution.  Obviously I can generate multiple OR statements .. ie..

 

SELECT * from table WHERE responses REGEXP '^1...............$' OR responses REGEXP '^.......1........$'

 

what i was hoping is that there's be a neater way of doing it ?

 

 

[effigy]

what i was hoping is that there's be a neater way of doing it ?

 

I don't believe so. MySQL's regular expressions are POSIX-based, and therefore quite weak. You could use the substring functions.

[teng84]

i guess mysql string function or wild card can help you or if your using script this is also doable on that

 

maybe

where field like %_ _1 _ _%

 

for php if will use it i guess split can help