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?
Use the PHP function serialize() to convert arrays to strings. These strings can easily be stored in MySQL database. Using unserialize() they can be converted to arrays again if needed.
Although an array is one of the most common data types in the world of programming, MySQL actually doesn't support saving an array type directly. You can't create a table column of array type in MySQL. The easiest way store array type data in MySQL is to use the JSON data type.
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; }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With