How do I save the values of checkboxes in a form into a single MySQL field?

Question

I have a form where i have many music genres. An artist can be singing under many genres. I have put the genres in checkboxes. Now i need to store the values of the checkboxes in to a single field.

Can some one help me out with some code snippet as i am new to php programming?

Answer 1

Why do you want to store multiple values in a single field - this will make searching/retrieval needlessly painful.

Either use a field per genre, or better still use a 'genres' table and 'artist_genres' lookup table.

Answer 2

You could use serialize/unserialize, but you shouldn't save serialized data to a database.

You won't be able to use database functions on it (searching, ordering, etc.) i.e.

Answer 3

You could serialize the data, for example:

<input type="checkbox" name="genre[]" value="Genre1"/>
<input type="checkbox" name="genre[]" value="Genre2"/>
<input type="checkbox" name="genre[]" value="Genre3"/>

PHP:

// Don't forget to escape the POST-values
$genre = serialize($_POST['genre']);
$query = "INSERT INTO database (genre) VALUES ('" . $genre . "')";
if(mysql_query($query)){
   // Success
}

You can get the data back with:

$genres = unserialize($serializedGenre);

Answer 4

In general you shouldn't be doing it that way. Your database would not be normalized, and that would make it difficult to build queries on the genre field.

It would be a better idea if you would have a table called artists, and a table called genres. Then you would define the relationship between the artists and the genres in another table artists_genres, which table would simply holds an artist_id and a genre_id. You would still be able to have multiple genres for the same artist.

For example, consider the table structure as defined below:

 TABLE artists
 -------------

 artist_id          name             surname
 1                  Alicia           Keys
 2                  Mariah           Carey
 ...


 TABLE genres
 ------------

 genre_id           name
 1                  R&B
 2                  pop
 3                  hip hop
 4                  dance
 ...


 TABLE artists_genres
 --------------------

 artist_id          genre_id
 1                  1
 1                  2
 1                  3
 2                  1
 2                  2 
 2                  4
 ...

In this case, you would be able to build simple queries such as:

SELECT 
    artists.name, artists.surname
FROM
    artists
INNER JOIN
    artists_genres ON (artists_genres.artist_id = artists.artist_id)
INNER JOIN
    genres ON (genres.genre_id = artists_genres.genre_id)
WHERE
    genre.name = 'pop';

The above would be quite difficult to achieve if the genres of your artists are stored in a single field in the artists table. Apart from the difficulty, it will probably be slow and inefficient, especially if you will be having many records.