Save PHP array to MySQL?

Solution 1:

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

Solution 2:

Generally, yes, serialize and unserialize are the way to go.

If your data is something simple, though, saving as a comma-delimited string would probably be better for storage space. If you know that your array will just be a list of numbers, for example, then you should use implode/explode. It's the difference between 1,2,3 and a:3:{i:0;i:1;i:1;i:2;i:2;i:3;}.

If not, then serialize and unserialize work for all cases.

Solution 3:

Just use the serialize PHP function:

<?php
$myArray = array('1', '2');
$seralizedArray = serialize($myArray);
?>

However, if you are using simple arrays like that you might as well use implode and explode.Use a blank array instead of new.

Solution 4:

Serialize/Unserialize array for storage in a DB

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

From the PHP Manual:

Look under "Return" on the page

Returns a string containing a byte-stream representation of value that can be stored anywhere.

Note that this is a binary string which may include null bytes, and needs to be stored and handled as such. For example, serialize() output should generally be stored in a BLOB field in a database, rather than a CHAR or TEXT field.

Note: If you want to store html into a blob, be sure to base64 encode it or it could break the serialize function.

Example encoding:

$YourSerializedData = base64_encode(serialize($theHTML));

$YourSerializedData is now ready to be stored in blob.

After getting data from blob you need to base64_decode then unserialize Example decoding:

$theHTML = unserialize(base64_decode($YourSerializedData));

Solution 5:

The best way, that I found to myself is save array as data string with separator characters

$array = array("value1", "value2", "value3", "...", "valuen");
$array_data = implode("array_separator", $array);

$query = "INSERT INTO my_tbl_name (id, array_data) VALUES(NULL,'" . $array_data . "');";

You can then search data, stored in your array with simple query

$query = "SELECT * FROM my_tbl_name WHERE array_data LIKE '%value3%'";

use explode() function to convert "array_data" string to array

$array = explode("array_separator", $array_data);

note that this is not working with multidimensional arrays and make sure that your "array_separator" is unique and had not exist in array values.

Be careful !!! if you just will take a form data and put in database, you will be in trap, becous the form data isn't SQL-safe ! you must handle your form value with mysql_real_escape_string or if you use MySQLi mysqli::real_escape_string or if value are integer or boolean cast (int) (boolean) on them

$number = (int)$_POST['number'];
$checked = (boolean) $_POST['checked'];

$name = mysql_real_escape_string($db_pt, $_POST['name']);
$email = mysqli_obj->real_escape_string($_POST['email']);