How to Store an Array into MySQL

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 an array into mysql?

You can always serialize the array and store that in the database.

PHP Serialize

You can then unserialize the array when needed.

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!

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);
}

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).

How to Store Array in Mysql Using PHP

Use json_encode() to store data in mysql table.

<?php
$name= "prashant";
$lname= "kumbhar";
$test = "testing";

$array = array('fname'=>$name,
               'lname'=>$lname,
               'test' => $test
         );

$res = json_encode($array);
echo "Convert from array to json :" .$res;

echo "\n\nFrom json to array:\n";
print_r(json_decode($res));

output

Convert from array to json :
{"fname":"prashant","lname":"kumbhar","test":"testing"}

From json to array:
stdClass Object
(
    [fname] => prashant
    [lname] => kumbhar
    [test] => testing
)

At the time of retrieve data in array form then use json_decode()

Working Demo : Click Here

How to store an array in a mysql database

use serialize

$varible = serialize($_SESSION['cart']);

And store it in a VARCHAR TYPE

And When you returning data use unserialze

Save PHP array to MySQL?

There is no good way to store an array into a single field.

You need to examine your relational data and make the appropriate changes to your schema. See example below for a reference to this approach.

If you must save the array into a single field then the serialize() and unserialize() functions will do the trick. But you cannot perform queries on the actual content.

As an alternative to the serialization function there is also json_encode() and json_decode().

Consider the following array

$a = array(
    1 => array(
        'a' => 1,
        'b' => 2,
        'c' => 3
    ),
    2 => array(
        'a' => 1,
        'b' => 2,
        'c' => 3
    ),
);

To save it in the database you need to create a table like this

$c = mysql_connect($server, $username, $password);
mysql_select_db('test');
$r = mysql_query(
    'DROP TABLE IF EXISTS test');
$r = mysql_query(
    'CREATE TABLE test (
      id INTEGER UNSIGNED NOT NULL,
      a INTEGER UNSIGNED NOT NULL,
      b INTEGER UNSIGNED NOT NULL,
      c INTEGER UNSIGNED NOT NULL,
      PRIMARY KEY (id)
    )');

To work with the records you can perform queries such as these (and yes this is an example, beware!)

function getTest() {
    $ret = array();
    $c = connect();
    $query = 'SELECT * FROM test';
    $r = mysql_query($query,$c);
    while ($o = mysql_fetch_array($r,MYSQL_ASSOC)) {
        $ret[array_shift($o)] = $o;
    }
    mysql_close($c);
    return $ret;
}
function putTest($t) {
    $c = connect();
    foreach ($t as $k => $v) {
        $query = "INSERT INTO test (id,".
                implode(',',array_keys($v)).
                ") VALUES ($k,".
                implode(',',$v).
            ")";
        $r = mysql_query($query,$c);
    }
    mysql_close($c);
}

putTest($a);
$b = getTest();

The connect() function returns a mysql connection resource

function connect() {
    $c = mysql_connect($server, $username, $password);
    mysql_select_db('test');
    return $c;
}

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."

Storing arrays in MySQL?

There are two options for storing as an array:

The first, which you mentioned, is to make one, or several, tables, and enumerate each possible key you intend to store. This is the best for searching and having data that makes sense.

However, for what you want to do, use serialize(). Note: DO NOT EVER EVER EVER try to search against this data in its native string form. It is much faster (and saner) to just reload it, call unserialize(), and then search for your criteria than to develop some crazy search pattern to do your bidding.

EDIT: If it were me, and this were something I was seriously developing for others to use (or even for myself to use, to be completely honest), I would probably create a second lookup table to store all the keys as columns; Heck, if you did that, mysql_fetch_assoc() could give you the array you wanted just by running a quick second query (or you could extract them out via a JOINed query). However, if this is just quick-and-dirty to get whatever job done, then a serialized array may be for you. Unless you really, really don't care about ever searching that data, the proper column-to-key relationship is, I think most would agree, superior.

---

Related Topics