What is the best way to store and retrieve an array in PHP/MySQL DB

Question

Lets say I wanted to build an app that will pull url links from a database and show 50 on a page. I am just using links as an example.

What if I had to store multiple values, an array into 1 mysql field for each link/record posted.

If there is 5 items for every Link, I could have an array of 5 items on the page, a list of 5 items seperated by commas, or I could use json encode/decode, What would be best for performance for saving a link to the DB and showing it on the page, something like implode/explode with a list of item, json encode/decode, or serialize/un-serialize the array?

Answer 1

Serializing is probably the best if you don't want to use multiple tables. The reason being that it deals with the special characters. If you use a comma separated list, then you'll need to worry about values that have commas in them already. Serialize/unserialize deals with this already. But the issue is the serializing is not terribly fast, although your arrays sound quite simple.

The best is still to have multiple tables as it allows you to search and/or manipulate the data much easier at a later date. It also isn't hard in PHP to create a loop to generate a SQL that adds multiple records to the second table (relating them back to the parent in the main table).

BTW, there are many other questions similar to this on SO: http://stackoverflow.com/questions/761170/optimal-way-to-store-retrieve-array-in-table

Answer 2

The best way, since you ask for it, is to create a table that describes the data you're going to save, and then insert a row for each element. In your example, that would mean you need two tables; pages and links, where there is a foreign key in links.page_id referring pages.id.