Storing Multiple Values In A MySQL Database

by Paul Robinson on 09.14.2008 | 11 comments

So after a little rest from coding I’m back with this little conundrum that hit me about 4 or 5 year ago, but seems to [...]

So after a little rest from coding I’m back with this little conundrum that hit me about 4 or 5 year ago, but seems to have come back to haunt a lot of people recently.

The problem is storing multiple values in one field when using a MySQL database. The project I worked on all that time ago was a music database and one of the fields held the members of the band. I could have created a secondary table to hold members and used a single to many relationship but personally I feel it would be inefficient since it’s very rear to have the same band member in two different bands. It does happen, but not often enough to warrant another table.

So how do I carry all that information in one database field? I hear you cry. Well I found the best way is to place some sort of seperator in the database field. Preferably a character you probably won’t use. So my members field looked a little like this:

Cheryl Cole^
Sarah Harding^
Nicola Roberts^
Nadine Coyle^
Kimberley Walsh

I used the carrat since I was never likely to use it in this field. Now when you retreive the information from the database you use a small PHP function to seperate each member from the database. Here is the function I wrote:

function seperateMembers($members)
{
	if(strpos($members, '^') === FALSE)
	{
		return '<ul><li>'.$members.'</li></ul>';
	}
	else
	{
		$memberArray = split('^', $members);

		$o = '<ul>';

		foreach($memberArray as $member) {
			$o .= '<li>' . $member . '</li>';
		}

		$o .= '</ul>';

		return $o;
	}
}

This makes a standard X/HTML unordered list with each item in. So it will end up looking something like this:

• Cheryl Cole
• Sarah Harding
• Nicola Roberts
• Nadine Coyle
• Kimberly Walsh

The PHP function is pretty much self explanatory but here is a quick run through.

We first check to see if there are any carrats by using strpos which returns false if it can’t find one. In this case we assume there is only one member and return that single member in a unordered list.

If there is a carrat we use split() which accepts a regex pattern for splitting. Since the carrat(^) has a special meaning in regex we backslash it so that PHP will interpret it literally. Then we use a simple foreach loop to go through each item in the array we created using the split, and place them into a unordered list. Finally we return that list. Throughout the last part I concatenate to a variable and return that. I find this is the best way, but there are probably other ways to do that.

So there you go. That’s a simple way of storing multiple values in a single MySQL table field. If you have any questions or you have anything to add please feel free to comment.