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?

Community · Accepted Answer · 2017-05-23 10:32:56Z

3

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: Optimal Way to Store/Retrieve Array in Table

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Dec 31, 2009 at 0:24

Darryl Hein

145k96 gold badges224 silver badges265 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

troelskn · Accepted Answer · 2009-12-31 00:32:58Z

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.

answered Dec 31, 2009 at 0:32

troelskn

118k27 gold badges135 silver badges156 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related