Store Multiple Values In A Single Row MySQL – Revisited

January 7, 2011 / PHP / by Paul Robinson / 42 Comments

This post was published back on January 7, 2011 and may be outdated. Please use caution when following older tutorials or using older code. After reading be sure to check for newer procedures or updates to code.

After a few requests and because the previous post is old (and written when I was inexperienced), I have decided to revisit this topic. Storing values in a MySQL database is easy, but storing multiple values can seem difficult to those starting out as it’s not exactly obvious from the name of the process used.

Note: I am assuming you already have knowledge of using MySQL or MySQLi to select, and insert data into a MySQL database. If you do not there are a few tutorials here on Return True that can help you. Just search for MySQL.

Serialization

The process used to store multiple values in a single row in MySQL is called serialization. This is the act of converting a PHP array into a string that represents the array (you can also serialize objects, but that’s for another tutorial). This string is special as PHP can recreate the original array from the string allowing you to store the data, then at a later date retrieve it, and then manipulate it as an array once again as if nothing had ever happened. There are a lot of well known web apps that use this method, one of the most famous is WordPress.

Serialization Example

Let’s take a look at a quick example of serialization.

//Data in array format
$data = array('item 1', 'item 2', 'item 3', 'item 4', 'item 5');
//Serialize the data
$data = serialize($data);

//Data in array format

$data = array('item 1', 'item 2', 'item 3', 'item 4', 'item 5');

//Serialize the data

$data = serialize($data);

You would then take the content now in the variable $data and store that in a MySQL database. Normally the field type text is sufficient to store it. Let’s take a look at some serialized data.

a:5:{i:0;s:6:"item 1";i:1;s:6:"item 2";i:2;s:6:"item 3";i:3;s:6:"item 4";i:4;s:6:"item 5";}

1	a:5:{i:0;s:6:"item 1";i:1;s:6:"item 2";i:2;s:6:"item 3";i:3;s:6:"item 4";i:4;s:6:"item 5";}

It looks complicated, but it is just a string representation of the array you created.

Unserialization Example

First you would need to retrieve your data back from the MySQL database. You would do that in the standard way for whichever MySQL connection method you are using.

//Data retrieved from MySQL, stored in var $data
$data = unserialize($data);

1 2	//Data retrieved from MySQL, stored in var $data $data = unserialize($data);

That’s it. The variable $data now contains the array exactly as it was when it was created. You can now manipulate it using any of the normal PHP array functions such as array_pop, or run it through a foreach to iterate through the values/keys.

I hope that all makes sense. It can be a little difficult to grasp at first, but if you have any trouble at all just drop me a comment & I’ll try my best to help you out.

Array MySQL MySQLi PHP Serialize

Paul Robinson

WordPress plugin & theme developer, user of Laravel and learner of React.js. Can usually be found listening to Japanese, and Korean Pop or Vocaloid music while developing.

42 Comments

Oh well…You are fast! I posted the results of my serialize experience in the other post…should I repost here for records sake?

author

Just start posting the comments in this post. It’s more focused on serialize so would probably make more sense to start posting the comments about it in here. 😉

Cool.

About your last comment in the other post, I have no idea what you meant 🙁

I serialized the data and is stored in my database, but the unserializing is not working for me

<? echo unserialize($artist["genres"]);	?>

1	<? echo unserialize($artist["genres"]); ?>

This method is obviously wrong, but the data I believe, was serialized correctly. If I just echo $artist[genre] I get

a:1:{i:0;s:4:"Rock";}

1	a:1:{i:0;s:4:"Rock";}

Which is cool, but it’s not ROCK! LOL.

I’m assuming from your post that I should use PRINT to show the info in the page, so, let me check that out and get back here again.

Something wrong with this,

<?
$genres = unserialize($artist["genres"]);
print_r($genres);
?>

$genres = unserialize($artist["genres"]);

print_r($genres);

This gives me Array ( [0] => Rock [1] => Electronic [2] => Reggae ) .

Which is better but still not right.

author

Well yes that is right. They are the items in your array. Now that you have retrieved them you can manipulate them how you wish. I didn’t include anything about it in this tutorial as it is outside it’s scope, but I can certainly help here in the comments if you let me know what it is you want to do with the data now that you have it back in it’s original array format.

Your guide is an awesome start for anyone wanting to learn how to do this. The real problem is my lack of knowledge on php.

What I want is jut to echo my unserialized data where now is showing up the word “Array” as in this example. Array is next to Genres

http://lobotoradio.com/artistinfo.php?id=5

I can’t understand why is not coming up!

Also, you have a talent to put things down in a very simple way. It would be awesome if you wrote a tutorial on how to implement this on forms. I implemented a drop down menu that serializes choices after hours and hours of reading forums and getting bits of info here and there. In fact, finding info about how to serialize from textareas seems mission impossible at this point.

author

I’m very, very sorry about not getting back to you quickly. I’ve had no internet over the weekend due to a fault with our broadband.

You have to use a for each loop to run through each item in the array. It can be used in any number of ways but the basic usage for arrays is like this:

//$data holds the array
foreach($data as $item) {
   echo $item;
}

//$data holds the array

foreach($data as $item) {

echo $item;

}

You can add anything you want to the echo too. A foreach loop iterates over the items in the array. It assigns the item inside the array to the variable $item. The foreach will repeat the part between the curly braces for each item inside the $data array.

I’ll see what I can do about making a tutorial for taking data from a form, serializing it & placing it into a database and then retrieving it later. I’ve just got to catch up with all my emails etc. 🙁

Tell me about delays…Real life hitting me hard right now. Anyway, this works like a Rolex.

My code:

<?php
											$genres = unserialize($artist["genres"]);
											foreach ($genres as &$genre) {
											echo $genre.",";
											}
											unset($genre);
											?>

<?php

$genres = unserialize($artist["genres"]);

foreach ($genres as &$genre) {

echo $genre.",";

}

unset($genre);

Man, I’m happy I got to your page 🙂

Saying in the same topic, tough, how do you manage to make this list comma separated. Right now I have a “space” after each item of the list because if I put a comma I get a comma after the last item. Tried several solutions around to no avail and the PHP page doesn’t mention it in the foreach discussion.

author

Yep, it’s a common problem and there is probably more that one answer for how to do it, but here is how I do it.

$o = ''; //create output variable
$genres = unserialize($artist['genres']);
foreach($genres as $genre) {
    $o .= $genre.', ';
}
$o = substr($o, 0, -2);
echo $o;
unset($genre);

$o = ''; //create output variable

$genres = unserialize($artist['genres']);

foreach($genres as $genre) {

$o .= $genre.', ';

}

$o = substr($o, 0, -2);

echo $o;

unset($genre);

I think that is right. The -2 may be wrong if it doesn’t cut off enough characters just decrease it further.

You are awesome. Your comma solution worked. It’s missing a ; in the example after the first variable, for those trying to implement it. I also began looking into how to get the comma for another set of data and got this working

$data = addslashes($artist[artist_name]);
$result = mysql_query("SELECT label, artist, album
			       FROM songlist
                               WHERE (artist='$data')
                               GROUP BY label
                               ORDER BY label ASC");
$i = 0;
while($result_ar = mysql_fetch_assoc($result)){
    if($i<>0)
    {
        echo ", ";
    }
    else
    {
        $i = 1;
    }
    echo $result_ar['label'];
}

$data = addslashes($artist[artist_name]);

$result = mysql_query("SELECT label, artist, album

FROM songlist

WHERE (artist='$data')

GROUP BY label

ORDER BY label ASC");

$i = 0;

while($result_ar = mysql_fetch_assoc($result)){

if($i<>0)

{

echo ", ";

}

else

{

$i = 1;

}

echo $result_ar['label'];

}

This took me soooo long. I get so frustrated with how hard it is for me to grasp PHP. Sigh… Just sent a bit for the server fund. Will get back to you when I hit another wall…

By the way, are you in England? I was surprised by the British Pound setting in Paypal.

author

I cleaned your code up a little, just to make it easier to read in the code box. Not sure what’s with the MySQL but it doesn’t want to indent correctly no matter what I do. 😆

By the way you could still use the same method for that too, although I must admit that is very innovative. It would probably look like this:

$o = '';
while($result_ar = mysql_fetch_assoc($result)) {
    $o .= $result_ar['label'].', ';
}
$o = substr($o, 0, -2);
echo $o;

$o = '';

while($result_ar = mysql_fetch_assoc($result)) {

$o .= $result_ar['label'].', ';

}

$o = substr($o, 0, -2);

echo $o;

Just thought I’d share it so you could choose which to use. 😉

Also thanks for spotting the missing ‘;’ I’ve added it in now. 🙂

PHP can be difficult to grasp, but the understanding of it it all falls into place eventually. I’ve been working with it for a long time now and I still learn new things everyday. 😆

Yep, I’m in England. Server is hosted in USA though. Thank you for the donation as well it’s very much appreciated. 🙂

This worked. I tried several combinations (random combinations, that is) of this code and couldn’t make it work. Have a lot of problems figuring out the position of ; , { and almost everything else. The thing is, I just do it in my free time as a hobby and because I got in my head this project of Lobotoradio and really want to make it work. I need to learn a LOT. Nevertheless, I’m way more advanced than I thought possible. Thanks to you and other professionals. Right now I have to go to bed, but tomorrow (later) I’ll hit you up with a big structural problem I really don’t know how to address. I’d like to hear your opinion on that.

author

No worries.

As for ‘;’ and ‘{}’ there is a simple rule. ‘;’ goes at the end of a line unless it has a ‘{‘ or ‘}’ on the end. ‘{}’ open and close statements such as if…else, functions, while, foreach and so on.

There are, of course, exceptions but that is a general rule that works for most cases.

I’m always here so feel free to shoot me that problem & I’ll take a look. 😉

Thanks man. I really appreciate all your help. Should I send you a message somewhere instead of posting here? My problem has nothing to do with serialization so probably will be better if posted on its own. Let me know.

author

Yep. You can either get me via email on admin [at] gmail [dot] com, or via IM on Skype with the username PaulBRobinson.

Hey man, thanks for the info. I sent you an Skype invite. I have another question about this post. By now I understand the mechanics of serializing data coming from, in my case a drop down menu with multiple selection. It works marvels with the storage of genres in my website. But the last couple of days I’ve been trying to adapt the script to make it work with a textarea. Right now I have the text area set up as was explained in your original post (that however antiquated, works quite well), but I’d definitely like to serialize the data coming from that textarea, which in my website contains the members of music bands. I suppose I should use commas to separate the data, but how do I explain PHP that each comma separated entity is a data entry.

author

I’d probably be better to explain how to do that on Skype.

I didn’t get an invite on Skype though? Did you use ‘paulbenrobinson’? There might be a few though. Try adding my personal email address as an extra filter pablorobinson [at] gmail [dot] com.

Sorry about that. I forgot how many Paul Robinson’s there was on Skype. 😆

I got it! I don’t know if it’s efficient or clean or fast. LOL. But it works.

Here’s my serialize variables

$members = explode("n", $_POST['members']);

$serializedmembers = serialize($members);

$members = explode("n", $_POST['members']);

$serializedmembers = serialize($members);

In the artist page, I unserialize the artists by using the same method we used in Genres, but substituting the comma by a BR

$o = '';
$members = unserialize($artist['members']);
foreach($members as $member) {
$o .= $member.'<br>';
}
$o = substr($o, 0, -2);
echo $o;
unset($members);

$o = '';

$members = unserialize($artist['members']);

foreach($members as $member) {

$o .= $member.'<br>';

}

$o = substr($o, 0, -2);

echo $o;

unset($members);

I think this solves the textarea serialization problem.

I had to correct the previous code because it was inserting a serialized empty code in Mysql as

a:1:{i:0;s:0:"";}

1	a:1:{i:0;s:0:"";}

This caused a problem because my page depends on calling that column if empty, and with that code inside it was never empty.

My solution was to condition the serialization to only post if not empty.

if($members === '')
{
$members = '';
}
else {
$members = explode("n", $_POST['members']);
$serializedmembers = serialize($members);
}

if($members === '')

{

$members = '';

}

else {

$members = explode("n", $_POST['members']);

$serializedmembers = serialize($members);

}

sigh…

author

As far as I can see it’s fine. Right now I can’t think of any way to make it better.

There is one thing though. Your substr only removes 2 characters & a HTML line break is 4. You’ll end up with a half a break at the end of your last member.

Sorry about not replying on Skype too. 7:30 is a little early for me on a Sunday morning. 😆

I’m sorry Paul. Didn’t even occurred to me to check the time difference. I apologize if I woke up anybody. Hate when that happens to me too…

author

No worries. You didn’t wake anyone up so don’t worry about that. I just didn’t get your message until I got up. 😆

I’m generally around on Skype between 11:00 and 22:00-23:00. That would be about 03:00 – 14:00-15:00 LA time. Sorry it’s a pain because of the time difference. You can always drop me an email if you can’t get me on Skype.

Hey Bud, sorry for getting lost. Work crazy these days. I have another question about serialization (I’m guessing they’ll be coming and coming).

I’ve been working for a few days in a mysql/php form. The way I want it is to be autofilled via ajax.

So, I found and customized a form to update artist info (name, dob, etc.) that I found on the internet that works well, except for the text areas.

In the create form, as we worked out in the past, I add band members via textarea with each member set in a different line of said textarea. The problem is that I’m really stuck with trying to unserialize the data to a textarea. Let me illustrate with the code.

This is my original code for the update form:

if(isset($_GET['id'])){
$res = mysql_query("select * from artists where id='".$_GET['id']."'") or die(mysql_error());
  
if($inf = mysql_fetch_array($res)){
echo "formObj.artist_name.value = '".$inf["artist_name"]."';n";
echo "formObj.real_name.value = '".$inf["real_name"]."';n";
echo "formObj.members.value = '".$inf["members"]."';n";
echo "formObj.dob.value = '".$inf["dob"]."';n";
echo "formObj.death.value = '".$inf["death"]."';n";
echo "formObj.type.value = '".$inf["type"]."';n";
echo "formObj.website.value = '".$inf["website"]."';n";

}

else{
echo "formObj.artist_name.value = '';n";
echo "formObj.real_name.value = '';n";
echo "formObj.members.value = '';n";
echo "formObj.dob.value = '';n";
echo "formObj.death.value = '';n";
echo "formObj.type.value = '';n";
echo "formObj.website.value = '';n";
}

if(isset($_GET['id'])){

$res = mysql_query("select * from artists where id='".$_GET['id']."'") or die(mysql_error());

if($inf = mysql_fetch_array($res)){

echo "formObj.artist_name.value = '".$inf["artist_name"]."';n";

echo "formObj.real_name.value = '".$inf["real_name"]."';n";

echo "formObj.members.value = '".$inf["members"]."';n";

echo "formObj.dob.value = '".$inf["dob"]."';n";

echo "formObj.death.value = '".$inf["death"]."';n";

echo "formObj.type.value = '".$inf["type"]."';n";

echo "formObj.website.value = '".$inf["website"]."';n";

}

else{

echo "formObj.artist_name.value = '';n";

echo "formObj.real_name.value = '';n";

echo "formObj.members.value = '';n";

echo "formObj.dob.value = '';n";

echo "formObj.death.value = '';n";

echo "formObj.type.value = '';n";

echo "formObj.website.value = '';n";

}

What I’ve been trying to do is to REUSE the code we used before to deserialize data, turn it into a variable and pass it as MEMBERS.

First I add this after the mysql Select:

$o = '';
$members = unserialize($inf["members"]);
foreach($members as $member) {
$o .= $member.'<br>';
}
$o = substr($o, 0, -4);

$o = '';

$members = unserialize($inf["members"]);

foreach($members as $member) {

$o .= $member.'<br>';

}

$o = substr($o, 0, -4);

And then I add the following to formObj.members.value:

echo "formObj.members.value = '$o';n";

1	echo "formObj.members.value = '$o';n";

This, of course fails big time. Have you worked on this before?

author

Hi Gustavo,

Well I’m not sure about the text area. The only thing I can see that might cause problems is the breaks. A text field can’t render them, you would probably have to try changing your single quotes to double quotes and using “\n” which is a new line in PHP.

Also just a small note, but you have a huge MySQL security flaw in your code by putting the value of $_GET straight into your query without escaping it in any way. MySQL escaping is a complicated subject so I’d suggest using Google to look up some tutorials, although bare in mind it is easier to escape in MySQLi (the newer OOP based system) than in old MySQL like you are using.

Just a final note. It may be slightly harder, but worthwhile to change how your members entry works. Rather than a text area you may be better of using multiple text fields added via Javascript. If you make them so they are added to an array in the $_POST you can grab them & serialize them all, then retrieve them as normal. The difficult part would be making the Javascript system to add & remove text fields.

Not sure if any of that has helped. Let me know how you get on. 😉

Your advice is always welcome and useful! I saw the multiple field added by users in the Discogs add release page, and I really wanted that. But I think it would be easier to understand Eisntein Lensing Theory.

That way is better to avoid error by users, but I don’t think the one artist per line is that bad, either. I mean, if an user cannot undertand that it is one artist per line he/she is kind of a lost cause LOL. But now that you bring it back to mind I’m gonna check it out again to see how hard it is to implement.

Nevertheless, and now that mention it…Because I read advice against it everywhere, I avoid javascript like the plague. I finally accepted a couple of scripts because it was the only way to have a rating system and an autofill search tool, but is it bad, ok, or good having javascript in your page? Is it good practice? I mean, Amazon.com uses JS.

Now, first of all, tonight I’ll look into that security issue you pointed out.

author

The strange thing is I do have a basic knowledge of Gravitational Lensing Theory, lol.

The thing is it’s best to assume that everyone wants to break your code. It seems like a paranoid assumption, but it’s the best way to think. Once you have that idea it helps you make your code so that it isn’t breakable.

In the case of your text area it is possible that someone could enter a blank new line and that could cause some problems as your code explodes via the “\n” new line character.

I always found Javascriptphobia to be a bit of an idiotic thing. Your browser is technically built upon Javascript.

Personally Javascript is the bane of my existence the only thing that makes using it bearable is using a JS library like jQuery & I’d highly recommend using one. It helps deal with a lot of browser quirks without the need to repeat your code a massive amount of times.

your advice was really helpful but i am weak at php so can you give a code so that i can retrieve the combined string from the mysql database and then separate them.
Actually i want to run a search query on the basis of keyword entered that matches any of the keyword that has been stored in the combined string and then show the results.This has to be done on every record present in the database’s table. Hoping for a reply soon.
Thanks.

author

Hi Gaurav,

A search would normally be done in a very different method. Storing multiple values in a database like this is useful for quite a few things, such as storing preferences or settings for options (like WordPress does). I’m not sure I would recommend it for a search though.

You would probably be better off using a MySQL query such as LIKE on the table’s field that contains the data you’d like to match.

If you need any help with the MySQL connection code there is a MySQLi basics part 1 & part 2 tutorial here on Return True.

If you have any other questions please feel free to email me through the contact form or leave another comment.

I don’t understand one thing, how do you put the serialized data on the database.
insert $data on database x?

author

You would insert the $data variable into your database using whatever method required. I didn’t show how to do as that wasnt really the focus of the tutorial. However if you search for mysql here on Return True you should find a tutorial on using MySQLi, which would be the best connection method to use.

author

Just an update to this. MySQLi is still much preferred to MySQL (procedural) but I would now recommend the use of PDO if possible. It is just as powerful as MySQLi and allows you to swap to a different database type without any code changes. Handy!

hi,
if we are storing more than one column with multiple values. those are stored as a string format then we want to retrieve those values in
corresponding values based on primary key id then how can i? please help me.
Thanks in advance.

author

Hi Kumari,

I’m not sure I understand. If you are asking can you retrieve a specific row by searching the serialized values then no. You could try full text searching, but it probably won’t work very well.

You should only serialize values that need to be stored, but will not need to be searched.

How can I do a search on the Serialized field on the table??

Let’s say I want to search who has Item 3 selected on their profile.

It is expensive for the database?

Thanks a lot for your post!

author

Hi Alex,

You shouldn’t is the answer. If you need to search on a value it should be in the database as a separate field. You can do it but in large databases it will start to become extremely slow since there is no index available to search on.

Hello.
Please help Me….
If I want to save array of 3 elements in 3 different fields (one row) of database, How to store those data.Please help me … its urgent.Please

author

Hi,

You would need to split the array up and create a query that inserts each of those elements. To be honest that sort of thing is just standard array manipulation & doesn’t relate to this tutorial.

The standard MySQL insert query would be:

INSERT INTO tbl_name VALUES(1,2,3);

1	INSERT INTO tbl_name VALUES(1,2,3);

So you would loop through the array and fill in the value like this:

//Assuming the array is called $items & has 3 items inside
$query = 'INSERT INTO tbl_name (col1, col2, col3) VALUES(';
foreach($items as $item) {
    $query .= "'" . $item . "',";
}
$query = rtrim($query, ',');
$query .= ')';
//Send to MySQL

//Assuming the array is called $items & has 3 items inside

$query = 'INSERT INTO tbl_name (col1, col2, col3) VALUES(';

foreach($items as $item) {

$query .= "'" . $item . "',";

}

$query = rtrim($query, ',');

$query .= ')';

//Send to MySQL

Of course it’s your responsibility to validate your data first if it is user input.

Thank You.. My problem is solved only because of you.Thanx again

author

No worries.

I retrieved a serialize row from database now I want to display it in table but I have a problem , the word ((Array)) is printed before the values ,, I do not want it to be shown at all.
here is my code

echo “” . $array= unserialize($row[‘Favorite_courses’]) ;
for($i=0; $i < 7; $i++)
{
echo($array[$i] . ", ");
} "”;

author

Hi Ahmed,

I’m going to assume that the weird quotes & echo at the start and end of your code are a WordPress quirk, because otherwise the code doesn’t make any sense to me.

((Array)) appears when you tell PHP to echo an array since it cannot output the contents of an array without using print_r or something similar. I’m unwilling to say what I think it the cause, because as I said I think WP has changed the code, but try looking at your echos as they would normally be the cause.

Older Comments

Newer Comments

Serialization

Serialization Example

Unserialization Example

Follow Me

Categories

Recent Posts

Recent Comments

I'll keep your WordPress site up-to-date and working to its best.