[RESOLVED] Multiple checkboxes

[bengt]

Hello.

I have a sql sb and in one of the tables it is a field called group.
This field contains some numbers, etc "2 5 4"

In a form i have som checkboxes

[ ] Some text (value 1)
[x] Some text (value 2)
[ ] Some text (value 3)
[X] Some text (value 4)
[X] Some text (value 5)

I want these checkboxes to be checked accordingly to the field in the db. In this case with my example above the second, fourth and fifth checkbox is selected.

Need help with the coding to do this. Also on how to insert the values into the DB.
This is probably very simple, but I have a bad day, and i can't figure this out.

Hope you understand my problem.

[leatherback]

inserting, e.g., for a form element 'show':

$show = $_POST['show'];
      if($show == "on")
        {
        $show = 1;
        }
      else
        {
        $show = 0;
        }
// insert value in a binary field

for subsequent showing e.g.,

$checked = "";
if($row['show'] == 1)
  {
  $checked = "checked"; // or should it be selected!?
  }
echo "<input type=\"checkbox\" ".checked." name=\"show\"> 

[wilku]

It is against database normalization (I think 😉 ) to have table structured in such way that multiple information is stored in one column (like the info about checking 2, 5 and 4 in one column named group). What would you do to query the database with "give me all records that have group number 4"? You should have separate fields of bool type (if you know the count of groups won't change), or even better have separate table just for cross-referencing records and groups; example:
id = (let's say 58739), record_content=(some content), group(2 5 4)
would become:
id = (58739), record_content=(some content) in one table
id = (58739), group=2//
id = (58739), group=5//
id = (58739), group=4//in second table (cross-reference)

[leatherback]

Hehe,

completely read over that aprt of the post. Good catch Wilku.

Indeed:

Have a separate table with the numbers, as Wilku suggested

[bengt]

Thanx for the input.

I made another table naming the different values

|uid  | group 1 | group 2 | group 3 | group 4 | group 5 |
+-----+---------+---------+---------+---------+---------+
| 1   |   0     |    1    |     0   |   1     |    1    |

Will show the checkbox values for user ID 1

[ ] Group 1
[x] Group 2
[ ] Group 3
[X] Group 4
[X] Group 5

[leatherback]

Hi Bengt,

Just to make sure you are not going to cut yourself in your fingers (As I did myself recently, felt like a tupid P*).

The word 'group' gives me the sneaking suspision that in the future you might have a new group. group 6. This would give you headaches troughout, as you will have to reqrite all your queries with the structure you show. Not good!

IF you are sure that this never going to happen no problem, and you can actually just include the 5 colums in your main table with the rest of the record.

What WIlku and I were referring to is the other situation. For instance. You are making a competition ladder. This is where my foolishness is a good example: I built a squash ladder. Keping track of the games in a games table:

[gameid][challenger][challenged][set1r][set1d][set2r][set2d][set3r][set3d]

Now, I assumed they played the best of three sets. However.. I got the thing online, and someone told me... We are playing the best of 5 sets. This meant my table had to be changed; my queries to that table (Insert, update) had to be changed, the winning score had to be changed; all the forms & tables had to be changed. I was kicking myself for making that stupid mistake. (in my defense: It is MUCH EASIER to code like this, and I was on a deadline).

but.. Quick and dirty is just that. Dirty. Solution:
Take out all the score colums, and add a table sets:
[set_id][game_id][set_nr][challenger_score][challenged_score].

You can now query all the scores from the sets table, where game_id matches a certain ID (Which you link using a LEFT JOIN). And off you go. You want to do the score of 20 sets? No problem.

In the form side of things I made it variable too, and just define the number of sets in an include, and have a for-loop to spit out the individual fields for each set. Completely flexible, but I lost a full night of sleep (A bit horrible, if you have to rebuilt 2 days before the ladder has to start).

So in your case..

if you are sure the groups will not change: Just include the 5 colums in your main table
If they might change: Have a table with:
[group][uid]

With:
Group => Group number, currently 1-5 (unsigned tinyint)
uid => the uid from the other table, foreign key, unsigned int.

You can than select all from the main table, and all affiliated from the groups table. You do not need to store the ones which are not selected: The relation is exclusive: Only store the ones which are selected. When updating, remember to check the table, and e.g., remove all the values from the table, before inserting the new selected values.

You can of course also doa full-blown table, where yu include all groups for each uid (so 5 entries) and add a colum selected (binary) where 0=not selected, 1; selected.

[bengt]

Thanx for your concern. But I am pretty well sure that the number of groups won't change. The groups spoken of here is some, call it departements, in a voluntary organisation.
I'll just leave it like this for now, but the issue will probably come up later, and I will definetively have these advices in mind then.

Again thanx...🙂