MySQL: Saving an array with serialize()

Using serialize is one of the most effective and safest methods for parsing an array to a MySQL database table. The PHP serialize() function allows you to make safe an array, saving essential data and array structure.

The following tutorial will show you how to implement serialize into a simple INSERT query and then retreive it with SELECT.

Connecting to DB

Firstly, we will need to establish a connection to our MySQL database. For this, I am going to use the ‘mysqli’ Extension.

/// CONNECT TO DATABASE
$hostname="****"; // specify host, i.e. 'localhost'
$user="****"; // specify username
$pass="****; // specify password
$dbase="****"; // specify database name
$conn = new mysqli($hostname, $user, $pass, $dbase);
if ($conn->connect_error) {
 die("Connection failed: " . $conn->connect_error);
}

INSERTING array with serialize()

<?php
/// CONNECTION CODES GO HERE (OR INCLUDE TO CONNECTION CODE PHP FILE)

/// Build Array
$my_array = array("Joe", "Bloggs", "22, Letsby Avenue");

/// Serialize Array
$my_array = serialize($my_array);

/// Add to MySQL database table
$query = "INSERT INTO my_table (id, member)
VALUES ('', '$my_array')";
$conn->query($query);
?>

Unserializing your data

Now that your array has been ‘serialised’ and stored within your database table, to retrieve that data we will need to extract it with unserialize() .

<?php
/// CONNECTION CODES GO HERE

/// Retrieve array
$query = "SELECT member
FROM my_table
WHERE id = 'the_id_of_this_entry'  ";
$snglQuery = $conn->query($query);
$row = $snglQuery->fetch_assoc();
$member = $row["member"];
/// Retrieve array data and structure with unserialize()
$member = unserialize($member);
?>

As long as you unserialize() your serialized array before use, you will be able to rebuild the original structure of the array. You can also check to see if the data has already been serialized:

<?php
if(unserialized($member))  
{ echo "This is a serialized string."; } 
else { echo "Nope, this is a normal array"; }
?>

Another great thing about serialize() is that it is not only retains an arrays structure, but it will also work well with multidimensional arrays.

A word of warning about serialize()

You must be careful with storing your serialized arrays within a MySQL database table. You must ensure you save serialized data in a VARCHAR or TEXT field. If you overwrite any previously saved array, the new array will replace the old. This can cause problems if you are using serialize to build and array. You will need to do the following:

  1. Retrieve the array with a SELECT MySQL query
  2. Unserialize the array
  3. Make your changes to the array
  4. Save your array to your database with an UPDATE MySQL query in conjunction with serialize()

Do be cautious about the serialize() function. It will limit the type of MySQL executions you have at your disposal and may complicate your project.

Leave a Reply

Your email address will not be published. Required fields are marked *