Blog

In this tutorial I am going to explain how to store Arrays in a database regardless of their complexity.

With what I am going to explain to you next, you will be able to, in detail, store and retrieve your arrays with a simple column of text in the database.

You will also be able to use what you have learned for any type of relational database, be it PostgreSQL, MySQL, MariaDB, etc.

Step by step I explain the following programming concepts with php and sql:

1. Explanation of the purpose of storing php Arrays in databases.
2. How to store arrays in database.

Content

• Why store Arrays in database?
  • Why not use the Array representation of the Database?
• How to store an array in database
  • 1. Create a database column
• 2. Convert php array to text
• How to retrieve the Array from the database
• Extra: Utility to encode and decode Database Arrays
• Conclusion
• Review of what was explained

Why store Arrays in database?

The first thing that should be considered is: do I really need to store an Array instead of creating a new table in the DBMS? This question has a clear answer: it depends. Everything will depend on the case you are solving and the needs of the project.

This functionality is extremely useful when we want a database column to store complex information.

For my part I find it really useful to store an Array in a database column when it comes to information that is difficult to specify, that is, data records that can increase outside of my control. These records that will hardly be repeated in structure, and for which new columns should be generated.

It is also useful when we talk about application configuration information, data that is generated directly in the code and is specifically to control, for example, which places the user can access or what range of colors has been chosen.

Why not use the Array representation of the Database?

In some DBMS (database management system) as postgresql there is a data type array for a column.

I absolutely do not recommend using these types of columns when developing web applications. The representation by the DBMS does not usually give good results and in some cases we could solve it much faster with a simple parsing of the fields of the array to a text string of comma-separated values: 1, 33, hello, quiz, world.

How to store an array in database

Now yes, I am going to show you how we can simply store any Array in a database column, for example in MySQL.

1. Create a database column

The first thing you will need is to create one column of the board lens capable of storing large text. Keep in mind that the size of the text field will be proportional to the size of the Array you want to store. It is not advisable to go crazy reserving space, but you should study how much information can be stored in the array.

for a array configuration data, where what I store is a PHP Associative Array (key => value) with information of the type es_admin => 1, we could use a column of the usual type Text.

2. Convert php array to text

Once you have your database column ready to store the Array as you just need to pass the array to text. For this task I will use the function PHP json_encode. The function json_encode of PHP transforms an Array to a representation of Javascript objects (JSON).

A PHP usage example:

<?php
$array = array(‘test’ => 1, ‘borrar’ => 0);
$array_texto = json_encode($array);
?>

The result of this transformation ($array_text) is a text string encoded in a special way to be able to represent the Array in JSON, which in PHP would be a string or text string. The result is similar to the following example:

{"a":1,"b":2,"c":3,"d":4,"e":5}

As a curiosity, if you are familiar with the JSON format, you will see how this output complies with its structure and that this same function (json_encode) would serve to transfer arrays PHP to javascript.

Finally, before storing this text directly in the database, I like to encode it in base64 to avoid character representation problems. If you don’t know what it is, I’ll leave you with a small definition from Wikipedia:

Base 64 is a positional numbering system that uses 64 as the base. It is the largest power of two that can be represented using only the printable ASCII characters. This has led to its use for email encryption, PGP, and other applications.

The encoded text resulting from this transformation of the Array in Json to base64 in the previous example is:

eyJhIjoxLCJiIjoyLCJjIjozLCJkIjo0LCJlIjo1fQ==

You already have the Array transformed and encoded, now you can store it in the database with SQL as any text:

INSERT INTO tabla_destino (array_codificado) VALUES (‘$array_php_codificado’);

How to retrieve the Array from the database

Now we are going to see the process of how to retrieve the Array encoded in the previous point and make it ready for use in your PHP code.

The first thing: you have to retrieve Array with any SELECT query and the one you like best among the possible ways to read a column from the database.

Once this variable is read, you have to perform the inverse transformation to the insertion process in the DBMS (like MySQL).

First you decode from base64:

<?php
$json = base64_decode($columna_array);
Y para terminar descodificas desde JSON:
$arrayPHP = json_decode($json ,true);
?>

The array is already in the same state as before it was inserted. You are ready to work with arrays php and consultations in a simple and efficient way.

Extra: Utility to encode and decode Database Arrays

As I like to end each tutorial with a professional development advice, I am going to leave you an example of good work to use the solution worked on. A PHP utility to encode and decode Arrays at will and should be available in your project.

Ideally, you should have one php class of utilities in a file with the same name (utilHelper.php) and in it add the two functions:

class UtilHelper {
   /* Crea un string codificado a partir de un array
   * @param Array array: array asociativo clave => valor
   * @return cadena de texto con el array listo para insertarse en BD
   */
   static function arrayEncode($array){
      return base64_encode(json_encode($array));
   }

   /* Crea un array a partir de un string codificado
   * @param String array_texto : string codificado de un array asociativo clave => valor
   * @return Array php
   */
   static function arrayDecode($array_texto){
      return json_decode((base64_decode($array)),true);
   }
}

If you include this file in your php-script where you want to store or retrieve an Array from the database you can use the utilities:

<?php
require_once ‘utilHelper.php’;
 
$array = [‘a’ => 1, ‘b’ => 2];
$array_codificado = UtilHelper::arrayEncode($array);
 
//aquí puedes insertar el array en BD
 
$array_deco = UtilHelper::arrayDecode($array_codificado);
 
// listo!!
?>

Conclusion

Storing Arrays in the database is extremely simple, as is retrieving them.

Either for readability, to increase productivity as a developer or for project requirements, I think that using this possibility wisely in your developments can allow you to extend the functionality of your database, avoiding the usual need to increase the number of columns of data. our boards to the point of making them untenable and tedious to use.

Review of what was explained

Description of the steps to insert the array associative in Database:

1. Encode the array in JSON
2. Encode the JSON in base64
3. Insert to database as any value of type VARCHAR or TEXT

Description of the steps to retrieve and decode the array from Database:

1. Retrieve, as usual, from a database table the column of type VARCHAR or TEXT.
2. Decode the String from base64 to get a JSON result.
3. Decode the JSON to get an Array.