Storing Arrays in the Database

How to store arrays in MySQL?

The proper way to do this is to use multiple tables and JOIN them in your queries.

For example:

CREATE TABLE person (
`id` INT NOT NULL PRIMARY KEY,
`name` VARCHAR(50)
);

CREATE TABLE fruits (
`fruit_name` VARCHAR(20) NOT NULL PRIMARY KEY,
`color` VARCHAR(20),
`price` INT
);

CREATE TABLE person_fruit (
`person_id` INT NOT NULL,
`fruit_name` VARCHAR(20) NOT NULL,
PRIMARY KEY(`person_id`, `fruit_name`)
);

The person_fruit table contains one row for each fruit a person is associated with and effectively links the person and fruits tables together, I.E.

1 | "banana"
1 | "apple"
1 | "orange"
2 | "straberry"
2 | "banana"
2 | "apple"

When you want to retrieve a person and all of their fruit you can do something like this:

SELECT p.*, f.*
FROM person p
INNER JOIN person_fruit pf
ON pf.person_id = p.id
INNER JOIN fruits f
ON f.fruit_name = pf.fruit_name

How to store a array in a database?

Usually you shouldn't store arrays in a single column in a db. It is preferable to have a table with tags and another one that links your entity with its tags. So, before try to store an array in a table just think if it is really the right thing to do in your specific case (usually not).

If you are sure you want to do that then you have serialize and unserialize functions.

UPDATE (after five years)
serialize and unserialize methods are inherently unsecure and should be avoided. It is preferable to store data in JSON format using json_encode and json_decode.

What's the best way to store an array in a relational database?

If that collection of values is atomic, store them together. Meaning, if you always care about the entire group, if you never search for nested values and never sort by nested values, then they should be stored together as a single field value.

If not, they should be stored in a separate table, each value bring a row , each assigned the parent ID (foreign key) of a record on the other table that "owns" them as a group.

For example, a clump of readings from a scientific instrument that are only ever used together as a collection for analysis should be stored together in a field. In contrast, a list of phone numbers for a customer that may often need to be queried for an individual number should probably be broken up into single phone number per row in a related child table.

For more info, search on the term "database normalization".

Some databases, support an array as a data type. For example, Postgres allows you to define a column as a one-dimension array, or even a two dimension array.

If your database does not support array as a type of column definition, then you may have three alternatives:

• XML/JSON
  Transform you data collection into an XML or JSON document if your database your database supports that type. For example, Postgres has basic support for storing, retrieving, and non-indexed searching of XML using XPath. And Postgres offers excellent industry-leading support for JSON as a data type including indexed support on nested values with its jsonb data type where incoming JSON is parsed and stored in an internally-defined binary format. This feature addresses one of the main reasons people consider using the so-called “NoSQL” systems, looking to store and search semi-structured data.
• Text
  Create a string representation of your data to store as text.
• BLOB
  Create a binary value to store as a binary large object (BLOB).

How to store array values in a MySQL database

I'm not sure I understand your question. Are you asking how to insert data from an array into multiple rows of your table? For that, you just need to create multiple INSERT queries, one for each array element, and execute them with the proper data bound to them. It's easiest to iterate through the array for this.

For example:

foreach ($cart_row as $value) {
    $stmt = "INSERT INTO user_cart(ProductName, Qty, Price) VALUES (?, ?, ?)";
    $stmt = mysqli_stmt_prepare($con, $stmt);
    mysqli_stmt_bind_param($stmt, "sii", $value["product_name"], $value["quantity"], $value["price"]);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
}

Best way to store an array in MySQL database?

Definitely not a text field, but a varchar -- perhaps. I wouldn't recommend parsing the results and storing them in individual columns unless you want to take advantage of that data in database sense -- statistics etc.

If you never see yourself asking "What is the average volume that users use?" then don't bother parsing it.

To figure out how to store this data ask yourself "How will i use it later?" If you will obtain the array and need to utilize it with PHP you can use serialize function. If you will use the values in JavaScript then JSON encoding will probably be best for you (plus many languages know how to decode it)

Good luck!

Storing an array in MySQL database, is there any other approach other than JSON

I recommend creating another table:

SHOPPING CART | PRODUCT | PRICE

The SHOPPING CART column is a foreign key to your shopping table.

The PRODUCT column is a foreign key to your products table.

In this table, you can insert any number of rows for the same shopping cart, with one product per row.

You should copy the price of each product to this table because that records what the customer paid for the product when they purchased it. Just in case the product price changes next week.

This is much better than using JSON. It's easy to insert another row. It's also easy to delete a row, in case the customer puts a product back. It's also easy to do things like SUM() the prices for the shopping cart. Any of those things are harder if you use JSON.

In general, you should try to design databases in normal forms instead of using JSON wherever possible. It makes your code easier for a wider variety of queries, and your data has better integrity if you avoid using JSON.

storing array in a sql database

You could serialize your array with the serialize() function.

Example:

serialize($event_items);

Generates a storable representation of a value.

This is useful for storing or passing PHP values around without losing
their type and structure.

http://php.net/manual/en/function.serialize.php

Alternative to store array in mySQL database. Multiple dates per order

I would create two tables. Orders and Order_dates. Orders will just list the order info including the order_id. Order_dates would have the dates stored using the DATE data type like so:

+----------+------------+------------+
| order_id | start_date | end_date   |
+----------+------------+------------+
| 1        | 2016-01-01 | 2016-01-03 |
+----------+------------+------------+
| 1        | 2016-01-05 | 2016-01-05 |
+----------+------------+------------+
| 1        | 2016-01-07 | 2016-01-07 |
+----------+------------+------------+
| 2        | 2016-02-07 | 2016-01-07 |
+----------+------------+------------+

that way you can easily query it the right way if needed in the future using SQL functions and indexing. As @Oliver said in one of his comments: "Storing serialized data is okay if you don't need to search through it, especially if it would require lots of columns or even a dedicated table. However, if one ever has the need to use it for search, order, group by... then don't even think about serializing such data."

Save array in mysql database

You can store the array using serialize/unserialize. With that solution they cannot easily be used from other programming languages, so you may consider using json_encode/json_decode instead (which gives you a widely supported format). Avoid using implode/explode for this since you'll probably end up with bugs or security flaws.

Note that this makes your table non-normalized, which may be a bad idea since you cannot easily query the data. Therefore consider this carefully before going forward. May you need to query the data for statistics or otherwise? Are there other reasons to normalize the data?

Also, don't save the raw $_POST array. Someone can easily make their own web form and post data to your site, thereby sending a really large form which takes up lots of space. Save those fields you want and make sure to validate the data before saving it (so you won't get invalid values).

Is it ever a good idea to store an array as a field value, or store array values as records?

I think you should read about Database normalization and decide for yourself. In short though, there are a number of issues with your proposal, but you may decide you can live with them.

The most obvious are:

1. What if an additional tag is added to row(1)? Do you have to first parse, check if it's already present then update the row to be tags.append(newTag).
2. Worse still deleting a tag? Search tags, is present, re-create tags.
3. What if a tag is to change name - some moderation process, perhaps?
4. Worse again, what about dfferent people specifying a tag-name differently - it'd be hard to rationalise.
5. What if you want to query data based on tags? Your query becomes far more complex than it would need to be.
6. Presentation: The client has to parse the tag in order to use it. What about the separator field? Change that and all clients have to change.

In short, all of these operations become harder and more cumbersome. Normalization is designed to overcome such issues. Probably the only reason for doing what you say, IMO, is that you're capturing the data as a one-off and it's informational only - that is, makes sense to a user but not to a system per-se. This is kind of like saying it's probably best avoided (again, IMO).

---

Related Topics