How should I store array values in a MySQL database?

Question

To give some background, I'm creating a web app using mostly jQuery. I scrape a number of values from a page on another domain; 3 arrays of integers and a date in string format. These values are then displayed on my page. Each of these arrays has 7 numbers.

It makes sense to me to store each array as a single entry in my MySQL database. My original idea was to store the values of each array as a JSON object and insert that into my entry in the DB from reading around SO this doesn't seem like the approach to take. I don't envisage having any need to sort the values at any stage in the future. I will just need the value of each integer and don't need to know how each integer in each array relates to one another.

Can someone point me in the right direction regarding the storing and retrieval of these values?

It's exactly the opposite, it does not make any sense to store a complex value like an array in a column of a relational database — Esailija
– Esailija, Commented Nov 24, 2012 at 11:35

toxicate20 · Accepted Answer · 2012-11-24 11:33:06Z

2

Usually arrays are kind of iffy to handle. In most cases arrays are values that I need to process later in the front end for the user with javascript (can't think of another use anyway). Therefore what I usually do is store them as text by using

$array = array('one', 'two', 'three');
$textToSaveinDB = implode(',', $array);

When I retrieve this you would just use the other way around

$textFromDB = "one,two,three";
$array = explode(textFromDB, ',')

Afterwards you can do whatever you need to do with the array. For example use JSON_encode() and send it to the user via ajax.

answered Nov 24, 2012 at 11:33

toxicate20

5,4103 gold badges28 silver badges38 bronze badges

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

5 Comments

garethdn Over a year ago

What type would you suggest storing the array as? If each array contains 7 numbers and and each number can only be between 1 and 100 does it make sense to use CHAR(30)? I'm not too familiar with data types.

toxicate20 Over a year ago

Well technically you would only need 7*3 + 6 = 27 chars, so CHAR(27) should suffice. If this is not user input then no problem. Before writing the array you would have to check if the input was correct.

garethdn Over a year ago

Currently I have the values stored in a JavaScript array. What is the best way to assign these to PHP variables in order to store them in the MySQL database? Or is there another, better way to insert my JS array directly into the DB?

toxicate20 Over a year ago

generally you would do this via an ajax call via jquery.

knittl Over a year ago

This will not work when your array values contained a comma. Better use something like CSV or JSON to store the array serialized in your db.

Akhilesh B Chandran · Accepted Answer · 2012-11-24 11:32:58Z

1

Maybe serialize() and unserialize() would help you. But I won't suggest this as the best approach.

answered Nov 24, 2012 at 11:32

Akhilesh B Chandran

6,6087 gold badges33 silver badges60 bronze badges

Collectives™ on Stack Overflow

How should I store array values in a MySQL database?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related