Save PHP array in MySQL?

Question

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

Also, as soon as I ask for this array in mysql table, what is a good way to return it to array form?

Serializes and non-serializes the response?

Answer 1

Unable to save the array in one field.

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

If you have to store the array in one field, serialize() and unserialize() will do the trick. But you cannot fulfill requests for actual content.

As an alternative to the serialization function, 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 to 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 records, you can perform such requests (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(); 

Connect connect() function returns mysql connection resource

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

Answer 2

Generally, yes, serializing and non-esterizing is the way to go.

If your data is simple, but saving as a comma-delimited string is likely to be better for storage. If you know that your array will be just a list of numbers, for example, then you should use implode / explode. This is 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 non-serialize work for all cases.

Answer 3

Serialize / Unserialize an array to store in the database

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

From the PHP manual:

Look in the “Return” section on the page

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

Note that this is a binary string that can contain null bytes and should be stored and processed as such. For example, the output of serialize () should usually be stored in the BLOB field in the database, and not in the CHAR or TEXT field.

Note. If you want to save html in blob, be sure to encode it with base64 or it may break the serialization function.

Coding Example:

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

$YourSerializedData now ready to be saved in blob.

After receiving the data from blob, you need base64_decode, then unserialize Decoding example:

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

Answer 4

Just use the serialize PHP function:

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

However, if you use simple arrays like this, you can also use implode and explode.

Answer 5

The best way I've found myself is to save the array as a data string with delimiter 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 for the data stored in your array with a simple query

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

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

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

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

Be careful! if you just take the form data and put it in the database, you will fall into the trap because the form data is not safe for SQL! you must process the value of your form with mysql_real_escape_string either if you use mysqli mysqli :: real_escape_string or if the value is an integer or logical (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']); 

Answer 6

Serialized and non-sterilized are quite common for this. You can also use JSON through json_encode and json_decode for a less PHP-specific format.

Answer 7

As mentioned earlier - if you do not need to search for data in an array, you can use serialize - but this is only "php". Therefore, I would recommend using json_decode / json_encode not only for performance, but also for portability!

Answer 8

Uhh, I don’t know why everyone offers serialization of the array.

I say the best way is to actually put it in a database schema. I have no idea (and you didn’t give any hints) about the actual semantic meaning of the data in your array, but usually there are two ways to store such sequences

 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 save your array on one line.

 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 elements in your array, working with this table will not be the most elegant. It is also impractical (perhaps, but rather inelegant - just make the columns null) to work with variable-length sequences.

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

In any case, any of these methods takes the 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.

How to get it back. Well, take the appropriate line / sequence of lines with the query and, well, use a loop. Right?

Answer 9

You can save your array as json.
there is documentation for json data type: https://dev.mysql.com/doc/refman/5.7/en/json.html
I think this is the best solution and will help you keep your code more readable while avoiding insane functions.
I expect this to be useful to you.

Answer 10

Yup, serialize / unserialize is what I saw in many open source projects.

Answer 11

check the implode function, since the values ​​are in the array, you want to put the values ​​of the array in a mysql query that inserts the values ​​into the 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); 

In addition, if you do not want to duplicate values, switch "INto" to "IGNORE" and only unique values ​​will be inserted into the table.

Answer 12

I would suggest using implode / explode with a character that, as you know, will not be contained in any of the individual elements of the array. Then save it in SQL as a string.

Answer 13

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

What many php applications do (e.g. sugarcrm), just use var_export to echo all the array data into a file. This is what I use to save my configuration data:

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

I think this is the best way to save your data!