Inserting a multidimensional array into a MySQL Table text field

Question

I'd like to insert a multidimensional array into a MySQL Database field so that it can then easily be read from the database at a later date back into an array. What's the best way to achieve this?

I've tried the following to no avail:

$id    = "MXB-487"
$items = Array(Array("Coffee", "Blend", "500"), Array("Coffee1", "Blend1", "250"));
$items = implode(",", $items);
mysqli_query($con,"INSERT INTO carts (id, items) 
VALUES ($id, $items)");

/*Code that pulls the array from the Database based on id and stores in variable $info*/
restored_mdarray = explode(",", $info);

casraf · Accepted Answer · 2014-02-09 14:36:56Z

ID in MySql, is usually unique (I'm pretty sure you specified it that way). So, you can't share the ID for multiple items. Also, imploding will end up with the following query:

INSERT INTO carts (id, items) VALUES(MXB-487, Array, Array)

Because you have a multidimensional array you're trying to implode, it doesn't recursively implode.

What you should do is loop through the objects, and I'm not sure how the relationship here works, but it looks like you need a relation table to connect those items. Consider the following structure:

    Carts:
    +----------+-----------+
    |    ID    |   Name    |
    +----------+-----------+
--<-|  MXB-487 | Blah blah |
|   +----------+-----------+
|
|   Items:
|   +----------+-----------+----------+-----------+
|   | Cart_ID  | Type1     | Type 2   | Amount    |
|   +----------+-----------+----------+-----------+
--->| MXB-487  | Coffee    | Blend    | 500       |
    +----------+-----------+----------+-----------+

And in order to implement that in PHP, you'd do something like this:

<?php
$id = "MXB-487";
$items = array(
    array("Coffee", "Blend", "500"),
    array("Coffee1", "Blend1", "500"),
);

$sql = "INSERT INTO actions (cart_id, type1, type2, amount) VALUES ";

$items_sql = array();
if (count($items)) {
    foreach ($items as $item) {
        $items_sql[] = "('$id', '{$item[0]}', '{$item[1]}', '{$item[2]}')";
    }
}

$sql .= implode(", ", $items_sql);

And then run the query.

It will look like this:

INSERT INTO actions (cart_id, type1, type2, amount) VALUES ('MXB-487', 'Coffee', 'Blend', '500'), ('MXB-487', 'Coffee1', 'Blend1', '500')

Which you can later select as such:

<?php
$id = "MXB-487";

$sql = "SELECT * FROM actions WHERE (cart_id = '$id')";

Though as a side note, I suggest you look at PDO and how to bind values, or at least learn to escape your values in the SQL to prevent future injections.

I speculated the structure of the tables, of course you can modify to your needs.

To connect the tables properly via SQL (to fasten the fetching later on) you can use FOREIGN KEY when you define the table:

CREATE TABLE actions (
    id INT(11) NOT NULL AUTO_INCREMENT,
    cart_id VARCHAR(32) NOT NULL,
    type1 VARCHAR(32) NOT NULL,
    type2 VARCHAR(32) NOT NULL,
    amount INT NOT NULL,

    PRIMARY KEY (id),
    FOREIGN KEY (cart_id) REFERENCES carts(id)
)

svoop · Accepted Answer · 2014-02-09 14:13:19Z

2

Use serialize:

$a = array(array(1,2,3), array(3,4,5));
$b = serialize($a);
# write $b to and later read $b from database
$c = unserialize($b);
$a == $c   # => true

answered Feb 9, 2014 at 14:13

svoop

3,4841 gold badge28 silver badges42 bronze badges

2 Comments

casraf Over a year ago

I don't suggest you use that though, as it seems you're trying to attach an array to a table - that is what one-to-many relations are for, and what relational databases in general (a-la MySQL) are for. Check my answer for a smarter solution that will also allow you to index those rows later on

svoop Over a year ago

There are situations, however, where it's legitimate not to break every bit down into relations. Some databases are aware of this on a data type level, most notably PostgreSQL which features array, hash and specialized structures (e.g. for IP addresses, coordinates etc).

Collectives™ on Stack Overflow

Inserting a multidimensional array into a MySQL Table text field

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related