Checkbox Selected from database values (theory)

[Minklet]

I have an audio mix submitting form on my website. The first part of the form takes the details from the user and inserts a new row in the first table 'mix', auto incrementing to generate a mix_id primary key.

 

The second part then uses a series of check boxes  to select which genres the mix contains and a foreach for the resulting array to insert, into the 2nd table 'genres_for_mix', a new row for each genre id (foreign key for the genres table) that is selected and the mix_id that it concerns.

 

Now I'm starting to think this is a bad way to do this because:

 

1. I don't know how to echo this out when a user edit's the mix to make all the relevant checkboxes selected.

2. When a user deselects a choice, it would be quite long winded to delete the nessesary row.

 

 

So instead I'm thinking that it would be much wiser to user the serialise functions to put the array into the database

 

 

However, I still don't know how to make the right checkboxes selected when I echo out the edit form. Do I use something like inarray()? If so how?

 

Also, it's really important that I can query the database for mixes that are associated with a specific genre. Without unserialising the array and therefore echo'ing out all the results, I don't know how to query this. Would I just use a 'WHERE genre_field LIKE 'genre_id'?

 

Can anyone help me with this please?

[jammesz]

Why not make three database tables that link to each other through one of the tables

Example:

 

Table mixes

------------------

mix_id | mix_name | mix_info | etc

2            Song22      random

 

Table genre

------------------

genre_id | genre_name | etc

1              rock

2              pop

3              classical

4              hard rock

 

Table link

------------------

link_mix_id | link_genre_id

2                  1

2                  4

 

Example sql

// find out what genre's Song22 has
$sql="SELECT genre.genre_name FROM genre,link WHERE link.link_mix_id='2' && link.link_genre_id=genre.genre_id";
//  output
rock
hard rock

// find out what mixes are under hard rock
$sql="SELECT mixes.mix_name FROM mixes,link WHERE link.link_genre_id='4' && link.link_mix_id=mixes.mix_id";
//output
Song22

 

[Minklet]

Thats exactly what I'm already doing at the moment...but like I said, when editing the entry, how do I get the relevant check boxes to check? And when the person then unchecks a previously checked box I have to delete that row.

 

Unless on submit I delete all rows for that mix_id, then repopulate the whole lot again, which is possible. But I still have the problem of echo ing out the checked checkboxes, or they'll have to reenter them everytime.

 

 

Also, I still think it would be better to store them in the same table as an array because I want to echo out every genre attributed to EACH mix in the table, and (as far as I can make out) I would have to have a 2nd select query inside the while{} for the first, which isnt possible.

 

 

[Minklet]

This does what I want it to do when it comes to kicking it out of the database

 

http://snook.ca/archives/php/how_i_added_tag

 

 

But he adds them manually.

 

 

If I added all my genres as an array, then assigned it to a variable when I retrieved the info, then could I use if (in_array('rock', $genres)){ echo 'checked' } for each checkbox? Would that work?