CODEWITHSUNDEEP
phpsqlmysqlhtml
Reagan
Reagan

Reputation: 31

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

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?

Upvotes: 3

Views: 1102

Answers (5)

Jessica Brown
Jessica Brown

Reputation: 8312

MySQL has a SET datatype that would lend itself well to storing a set of checkboxes in a single column.

Upvotes: 0

Daniel Vassallo
Daniel Vassallo

Reputation: 344261

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.

Upvotes: 4

Harmen
Harmen

Reputation: 22438

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);

Upvotes: 1

Karsten
Karsten

Reputation: 14642

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.

Upvotes: 1

John Parker
John Parker

Reputation: 54415

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.

Upvotes: 2

Related Questions