Storing Multiple Items In One MySQL Field
A little while ago I posted about storing more than one piece of information in a MySQL table field. This can help with storing arrays of data that do not need to be included in a relationship.
Anyway the trick here is to first place the information you want to store in your MySQL table into an array, so let’s for an example take URL’s & their alt text.
|
1 2 |
$info = array('http://wordpress.org' => 'A excellent blogging platform', 'http://reddit.com' => 'The home page of the internet'); |
I think you get the idea. So normally storing this info in a database would require two fields and two rows. However you can store this in a single field by using serialize(), this compacts the array into a byte string so that you can store it in a single field. All you need to do when it is retrieved from the database is use unserialize() and it will be turned back into an array again. Here is a quick example:
|
1 2 3 4 5 |
$info = array(...) //the elipsis represents the information written above. $serial_info = serialize($info); // Now we store the info inside $serial_info into a database... // Ok. Now we have retrieved the information from the db field 'links' into a var named $row $info = unserialize($row['links']); |
That would give you back an array, like the one you started with, inside the variable $info. Just remember that when you serialize an array or object the resulting byte string can be rather large so you will probably need to store it in a suitably large field type, text is the most commonly used.
I feel I should add some updated information on this. Using serialization to store data is very helpful, but please be weary. If you are going to need to search through the data in some way, or be able to find a row by data stored in a serialized field, do not serialize it. Any data that will need to be used to find an item should be stored in a separate column. If you are going to be storing a lot of abstract data that might be needed to be searched upon, then you might want to consider whether a traditional MySQL database is correct for your application.
Hopefully that was helpful. Please remember to only use this in situations where it would be useful. It should not be a replacement for making tables when they would be needed.
4 Comments
Anupam
Hi I am trying to do the same thing. I would like to save the Facebook user timeline details OR messages in MySQL database. Timeline details are visible in screen total posts are like this http://screenshot.co/#!/2dcbee03ff , if I want to see the ‘messages’ only I can see in the screen like http://screenshot.co/#!/635a038dd5 but not able to store in database. I tried with the following Code snippet but I am not getting expected result. I am getting “Array” in the message field http://screenshot.co/#!/9385792e8a . Please help me how to store all messages in single row for single user. I have used the serialize() and unserialize() but no results
Paul Robinson
Hi Anupam,
Sorry about your comment being moderated, it’s because you had more than 1 hyperlink in your comment.
Honestly I’m not sure. Serializing the data you are getting should work. Have you tried serializing the array and then
var_dump-ing that to see if it serializes correctly? I’ve never had any issues throwing even large multi-dimensional arrays into a database using serialize. The best example is WordPress, it stores the data for plugin updates as a serialized array and it can get pretty large.Please do let me know what result you get and I’ll see if I can help further.
Anupam
No I have tried , but it is not showing the results in database. !!
Paul Robinson
Hi,
I gathered you’d already tried serializing the data from your first post, but have you tried serializing the data and dumping out to the screen (before inserting the row) so you can see if the data is being serialized correctly? Even dumping your $sql variable so you can see what statement is going to be sent to MySQL?
The issue you are having implies the data isn’t being serialized as it is printing Array into the database as an array cannot be cast into a string and so will print the word Array instead.