another set_checkbox thread

[eluser]Samuurai[/eluser]
Hi everyone,

I'm trying to get my head around using set_checkbox to populate a form from the database if, for example, a user wants to edit their settings.

Say I have three tables User, Skills and User_Skills. User is a normal user table, Skills has a skill id and the name of the skill and the User_Skills table has two fields, user_id and skill_id.

In my controller, I first get the skills so my view can generate the checkboxes using:

Code:

SELECT name FROM Skills

I then get the user details

Code:

SELECT * FROM User WHERE id = $id

My view then has this line:

Code:

<input type="checkbox" class="checkbox" name="skills[]" value="<?=$skills[$i]['id']?>" <?=set_checkbox('skills[]',$skills[$i]['id'],WHAT_DO_I_PUT_HERE)?> />

I thought about doing a third query on user_skills and then doing an in_array() function to get a true/false output... but seriously...three queries?

I also thought I could do a GROUP_CONCAT in my User query and return the skill ID's comma seperated and then doing a strstr() but that could be prone to error.

How else can I do this?

[eluser]danmontgomery[/eluser]
I would do something like:

Code:

SELECT Skills.id, Skills.name, IF(User.id IS NULL, false, true) AS has_skill
FROM Skills
JOIN User_Skills ON User_Skills.skill_id = Skills.id
LEFT JOIN User ON User.id = User_Skills.user_id AND User.id = $id
ORDER BY Skills.name

Would give you all of the skills, and true if they have the skill or false if they don't.

Code:

// Controller
foreach($query->result() as $row) {
    $this->load->view('checkbox', $row);
}

Rather than just having skills[] = $id, I would do skills[$id] = true/false. Then something like this should work:

Code:

// View
<input type="checkbox" class="checkbox" name="skills[<?=$id?>]" value="1" <?=set_checkbox("skills[$id]", true, $has_skill);?>/>

[eluser]Samuurai[/eluser]
THAT is what I was after... excellent approach, though it's returning some odd results. I tried to troubleshoot it, but couldn't get it to return the correct result.

This is my Skills table

Code:

name     title
HGV1     HGV1 Driver
HGV2     HGV2 Driver
No.1 Mechanic     No.1 Mechanic
No.2 Mechanic     No.2 Mechanic
Designer     Designer
Engineer     Engineer
Data Analysis     Data Analyst
Coaching/Inst.     Coach/Driver Instructor
Hospitality     Hospitality Organiser
Promo Girl     Promo Girl
Public Relations     Public Relations Expert
Photographer     Photographer
Film Crew     Film Crew
Physiotherapist     Physiotherapist
Volunteer     Volunteer

And the result of your query is as follows.

Code:

id      name            has_skill
8     Coaching/Inst.     0
8     Coaching/Inst.     0
7     Data Analysis     0
7     Data Analysis     0
5     Designer     0
5     Designer     0
6     Engineer     0
1     HGV1     0
2     HGV2     0
9     Hospitality     0
9     Hospitality     0
3     No.1 Mechanic     0
10     Promo Girl     0
10     Promo Girl     0
10     Promo Girl     1
11     Public Relations     1
15     Volunteer     1

[eluser]danmontgomery[/eluser]
Ahh, that's a problem with the query I just realized.

You will probably be better off just fetching all skills, then doing something like:

Code:

SELECT skill_id
FROM User_Skills
WHERE User_Skills.user_id = $id

To just get the IDs of the skills that user has... You could construct the array that way.

[eluser]Samuurai[/eluser]
I got an SQL expert at explainextended.com to help me.. this query does it.

Code:

SELECT  s.*, us.Skill_id IS NOT NULL AS has_skill
FROM    User u
CROSS JOIN
        Skills s
LEFT JOIN
        User_Skills us
ON      us.User_id = u.id
        AND us.Skill_id = s.id
WHERE   u.id = $id