Checkbox array

[BerksWebGuy]

If have about 20 checkboxes (lets call them skills) and need to know the best way of utilizing them.

#1. Put them in an array, skills[], then implode to comma delimited, and insert into DB. So the record would look like 'HA,FE,AR,HE,FI'. If I wanted to look for HA and AR at the same time, could I use:
LIKE '%HA%AR%'
or
IN('HA','AR')
??

#2 Put these into seperate fields (HA = 'Y' and AR = 'Y'). I want to avoid this cause it just adds 20 fields to a large table.

Ideas?

[HalfaBee]

LIKE '%HA%AR%' should work.
Of course it would always have to be in the correct order and both exist.

[BerksWebGuy]

Thanks, I'll have to sort the array first. I'll give that a shot.

More suggestions are welcome :cool:

[HalfaBee]

I suppose you could do it in two tests

LIKE "%HA" AND LIKE "%AR%"

then it wouldn't matter.

[satis]

honestly, I'd probably put them all in their own column. It's alot uglier when looking at the table, but otherwise it makes more sense. Easier to query, and it'll even take less actual space, since each column would only require 1 bit to store the data (on or off).

Just my 2 cents. :) If you have it in a table with other data and want the view to be prettier, you could always create a new "skills" table and just reference a userid so there's a 1 to 1 relationship between your "skills" table and your user table.

If different people will have different checkboxes available to them, then things get more complicated, of course.

[mitchind]

My belated suggestion - expanding on satis' ideas (sorry was out and about for a month now)

Make it completely relational - might make your query setup a little more expanded but data is nice and neat to work with.

User table - userid, .... other fields
Skills table- skillid, skillabbrev, skilldescription
User Skills table - skillid, userid <combined is primary key>

Here's some pseudo SQL - let me know if you need exact syntax to get it working for you ...

INSERT:
Insert skillid, <userid> INTO UserSkills WHERE (SELECT skillID FROM Skills WHERE Skills.skillabbrev IN (<your checkbox list>))

SELECT
Select User.* FROM User WHERE User.userid = userskills.userid AND Skills.skillid = User.skillid AND Skills.abbrev IN (<your list>)

[jmucchiello]

do the implode like this $val = '|' . implode("|",$cboxs) . '|'
Then the sql can be something like WHERE instr('|HA|', field) > 0 AND instr('|AR|', field) > 0. INSTR should be faster then anything with 3 percent signs.

[BerksWebGuy]

Finally got around to it. The instr works great. I haven't compared it to LIKE%, but I'm keeping it.

Just a side note...it is instr(field, '|AR|') > 0