Marking checkboxes as checked based on database data

[Venom Rush]

Hi all

I'm having a hard time selecting data from my database. What I want to do is when I view the page that allows me to edit a team name (team1 for instance) I want to list all the product types and then check the checkboxes of the product types that are currently associated with the team (eg team1).

My tables are as follows:

Table teams

*------------*
| id | team  |
|------------|
|  1 | team1 |
|  2 | team2 |
*------------*

Table product_types

*--------------------*
| id | product_type  |
|--------------------|
|  1 | bangle        |
|  2 | necklace      |
|  3 | ring          |
|  4 | men's rings   |
|  5 | women's rings |
*--------------------*

Table teams_types (products associated with each team)

*--------------------------*
| id | product_type | team |
|--------------------------|
|  1 | 1            | 1    |
|  2 | 2            | 1    |
|  3 | 3            | 1    |
|  4 | 4            | 1    |
|  5 | 2            | 2    |
|  6 | 4            | 2    |
*--------------------------*

I'd appreciate any help. Any attempts at using a join statement have been a failure.

[Venom Rush]

Added info just in case my post was a bit obscure. I want to list all of the product_type's from the table 'product_types' on the page with a checkbox next to each one. Then have the checkboxes of the product_type's that are associated with the team to be checked (this info of course would be pulled from the table 'teams_types').

My issue at the moment is that no matter what type of join statement I've tried, I only ever get a list of the product_type's associated with the team and not the product_type's that aren't. I want to be able to see all the product_type's regardless of whether they are checked or not.

[martin5211]

Is this what you're looking?

SELECT * 
FROM 
`teams_types` 
JOIN  `teams` ON teams_types.team = teams.id
JOIN `product_types` ON teams_types.product_type = product_types.id

[Venom Rush]

Is this what you're looking?

SELECT * 
FROM 
`teams_types` 
JOIN  `teams` ON teams_types.team = teams.id
JOIN `product_types` ON teams_types.product_type = product_types.id

Hi martin

Won't that just result in a list of the associated product_types? For instance if I want to edit team2 then I'll only see a list of:

- necklaces
- men's rings

I need to show all product_type's so that if I want team2 to display bangles I can check the checkbox for 'bangle' to associate the product_type with team2.

[Fbody]

Then that's exactly what you need. As you go through the rows, if a row with the appropriate ID exists, echo a 'checked="checked"' as part of the checkbox's <input /> HTML tag. That will make it render in a "checked" state.

[Venom Rush]

This isn't working for me. The mysql query only returns the product_type that's associated with the team.

I created "team2" and when I did so I checked the boxes for "necklace" and "men's rings".

Now when I go to edit "team2", the MySQL query suggested by martin only returns "necklace" and "men's rings" with both checkboxes checked. I also want to display "bangle", "ring" and "women's rings" so that if I want to associate any of those three with "team2" I can check their respective checkboxes.

[Venom Rush]

I seem to have solved my problem.

This selects all product types related to any and every team.

SELECT * 
FROM 
`product_types`
LEFT JOIN `teams_types` ON product_types.id = teams_types.product_type

I then use an if statement to only display the product types where team is equal to NULL or the id of the team I'm editing

if($team == NULL || $team == $id)