Storing a PHP Array in a MySQL Database

Transferring list data between PHP and MySQL

Although there isn't an explicit MySQL data type for arrays or list data, there are ways to store such data in a MySQL database. I've often found it useful to be able to associate lists with data in MySQL tables. For instance, if you were making a database for articles and you wanted to implement a keyword system, it would be useful to be able to associate a list of keywords with each article in the MySQL table. Or even better, you might have a separate table with keywords, and then fill the appropriate lists with keyword id numbers that are in turn associated with keywords. However you choose to store your data, storing lists are inevitably useful. Using the LONGTEXT MySQL data type and PHP string parsing functions, you can store PHP arrays as strings within a MySQL database.

For the sake of this article, we're going to store the arrays in their own table. If you like, you can always merge the same MySQL columns in an existing table.

To start off, let's setup the array table, aptly named "array." We only need three columns, the id, the size of the array, and the storage string. It's good to store the size just in case you only want to retrieve the size of the array without actually looking at the array, that way you don't have to loop through the actual data in order to find out its size.

$sql = "CREATE TABLE 
'array' 
('id' INT NOT NULL AUTO_INCREMENT,
'size' INT NOT NULL DEFAULT \'0\', 
'array' LONGTEXT NOT NULL, 
PRIMARY KEY ('id'))";
mysql_query($sql);

To store the actual array data, we are simply appending the array items together in a string. In order to know when one item stops and another item begins, we need to split the individual items with a standard string. This "spliter" string is arbitrary, as long as it's unique in that none of the items will contain this string. For this example, I'm going to split the items with "%". Here is a little example of how this is going to work.

//Original PHP Array
$array=Array(0,1,2,3,4);

//Converted to a string
$strarray="0%1%2%3%4%5";

Now all we need to do is come up with a set of functions to handle the conversion process. First let's create a function that takes an array and converts it to a string. We can do this by looping over the array and appending it's contents to a string.

$array=Array(0,1,2,3,4);
$strarray="";
for ($i=0;$i<sizeof($array);$i++) {
	if($i!=0) {
		$strarray=$strarray."%".$array[$i];
	} else {
		$strarray=$array[$i];
	}
}

Second, we need to insert the string into the MySQL database, combined with the size of the array. We might as well write a nice function for this process.

function insertArray($array) {
	$strarray="";
	for ($i=0;$i<sizeof($array);$i++) {
		if($i!=0) {
			$strarray=$strarray."%".$array[$i];
		} else {
			$strarray=$array[$i];
		}
	}
	$size=sizeof($array);
	$sql="INSERT INTO array (size, array)
	VALUES('$size','$strarray') ";
	mysql_query($sql) or die(mysql_error());
}

Now we need to make a function to reverse the process, or take the MySQL data and create a PHP array. This is simple, thanks to a built in PHP method, split(), which takes a string and converts it to an array by splitting the string. We'll make our function take only the id of the mysql array as an argument.

function getArray($id) {
	$query = "SELECT * FROM array WHERE id='".$id."'"; 
	$result = mysql_query($query) or die(mysql_error());
	$row = mysql_fetch_array($result) or die(mysql_error());
	$strarray = $row['array'];
	$array=split("%",$strarray);
	return $array;
}

Finally, we need to be able to retrieve the size of the array.

function getArraySize($id) {
	$query = "SELECT * FROM array WHERE id='".$id."'"; 
	$result = mysql_query($query) or die(mysql_error());
	$row = mysql_fetch_array($result) or die(mysql_error());
	$size = $row['size'];
	return $size;
}

So hopefully this tutorial can help add another storable data type to your MySQL databases. If you find any bugs or have questions or comments, email me at asa [at] asakusuma [dot] com.

Username
Password