Save PHP array to MySQL?

Question

What is a good way to save an array of data to a single mysql field?

Also once I query for that array in the mysql table, what is a good way to get it back into array form?

Is serialize and unserialize the answer?

Answer 1

Serialize and unserialize are pretty common for that. You could also use JSON via json_encode and json_decode for a less PHP-specific format.

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

Answer 3

Yup, serialize/unserialize is what I've seen the most in many open source projects.

Answer 4

check out the implode function, since the values are in an array, you want to put the values of the array into a mysql query that inserts the values into a table.

$query = "INSERT INto hardware (specifications) VALUES (".implode(",",$specifications).")";

If the values in the array are text values, you will need to add quotes

$query = "INSERT INto hardware (specifications) VALUES ("'.implode("','",$specifications)."')";

mysql_query($query);

Also, if you don't want duplicate values, switch the "INto" to "IGNORE" and only unique values will be inserted into the table.

Hope thats what youre asking for.

Answer 5

Uhh, I don't know why everyone suggests serializing the array.

I say, the best way is to actually fit it into your database schema. I have no idea (and you gave no clues) about the actual semantic meaning of the data in your array, but there are generally two ways of storing sequences like that

create table mydata (
  id int not null auto_increment primary key,
  field1 int not null,
  field2 int not null,
  ...
  fieldN int not null
)

This way you are storing your array in a single row.

create table mydata (
    id int not null auto_increment primary key,
    ...
)

create table myotherdata (
    id int not null auto_increment primary key,
    mydata_id int not null,
    sequence int not null,
    data int not null
)

The disadvantage of the first method is, obviously, that if you have many items in your array, working with that table will not be the most elegant thing. It is also impractical (possible, but quite inelegant as well - just make the columns nullable) to work with sequences of variable length.

For the second method, you can have sequences of any length, but of only one type. You can, of course, make that one type varchar or something and serialize the items of your array. Not the best thing to do, but certainly better, than serializing the whole array, right?

Either way, any of this methods gets a clear advantage of being able to access an arbitrary element of the sequence and you don't have to worry about serializing arrays and ugly things like that.

As for getting it back. Well, get the appropriate row/sequence of rows with a query and, well, use a loop.. right?

Answer 6

Just use the serialize PHP function:

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

However if you are using simple arrays like that you might as well use implode and explode.

Answer 7

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

Answer 8

Instead of saving it to the database, save it to a file and then call it later.

What many php apps do (like sugarcrm) is to just use var_export to echo all the data of the array to a file. This is what I use to save my configurations data:

private function saveConfig() {
    file_put_contents($this->_data['pathtocompileddata'],'<?php' . PHP_EOL . '$acs_confdata = ' . var_export($this->_data,true) . ';');        
}

I think this is a better way to save your data!