Save Array in MySQL Database

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

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

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 to mysql using php

If you're planning on saving all of the array as one string in the database you can just use mysqli_query, and mysqli_real_escape_string:

Saving all of the array as one string:

$arr = ["002012295915", "00971595502", "8885555512", "5555648583", "4085555270", "0562825196", "01147220964"];
$con = mysqli_connect("localhost","root","pass","db");
mysqli_query($con,"insert into TABLE values('".mysqli_real_escape_string($con,$arr)."')");

And if you want to print it later just use the select from TABLE where.

Saving each value by itself (without regex knowledge):

    //LET'S MAKE THE ARRAY MORE BEAUTIFUL FOR THE LOOP WITHOUT REGEX.

    $arr = str_replace('[','',$arr); 
    $arr = str_replace(']','',$arr);
    $arr = str_replace('"','',$arr);

//NOW THE ARRAY LOOKS LIKE THAT: 002012295915, 00971595502, 8885555512, 5555648583, 4085555270, 0562825196, 01147220964
//WE JUST NEED TO SPLIT IT NOW

$new_Array = explode(',',$arr); //DEVIDE ARRAY ELEMENT BY ','

//OK NOW WE JUST NEED TO PUT EACH ONE AS A SINGULAR VALUE IN THE DATABASE.

 $con = mysqli_connect("localhost","root","pass","db");

foreach($new_Array as $data)
{
   mysqli_query($con,"INSERT INTO TABLE VALUES('$data')");

}

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.

---

Related Topics