Entry

How do I store an array in a MYSQL field

Apr 25th, 2001 11:42
Shannon Hale, Darrell Oller,

If you want to have a field in a table that can have multiple values, 
you need to create a separate table for it, and "relate" each record in 
that table back to the first table (hence the term "relational 
database").

I know, sounds confusing. Here's an example:

Suppose you have a database to store information about CDs. You might 
have a table called cds, which contains three fields: id, artist, title.

Now suppose you want to store the track information for each CD. But 
each CD can have multiple tracks (or an array of tracks). So instead of 
storing the tracks in the cds table, you create a separate table, 
perhaps called songs. This table has three fields: cdId, trackNum and 
songTitle. But the primary key will be a combination of all three 
fields (a composite key).

When you add songs to the songs table, the value in the cdId field will 
be the same as the value in the id field in the cds table, for the CD 
you are adding songs to. So if you have, for example, the following 
entry in the cds table (sorry, I know I have weird taste in music, I 
just picked up what I have lying around):

id: 106
artist: Crooked Fingers
title: Bring on the Snakes

Then you might have the following in the tracks table:

cdId: 106
trackNum: 1
songTitle: The Rotting Strip

cdId: 106
trackNum: 2
songTitle: Devil's Train

cdId: 106
trackNum: 7
songTitle: Here Come the Snakes

Note that the cdId is the same for each, because they are all 
associated with record #106 in the cds table. That's the reason for 
having all three fields as the primary key - you will have multiple 
records with the same cdId, and multiple records with the same 
trackNum, and possibly even multiple records with the same song title, 
but not in the exact same combination.

So then, if you wanted to get all the information about the Crooked 
Fingers CD, you would write a query something like this:

select artist, title, trackNum, songTitle
from cds, songs
where cd.id = songs.cdid

(and then whatever ordering and grouping you want).