How to Store PHP Arrays in a MySQL Database

Want to store PHP Arrays in MySQL Database, then your are in the right place. In this tutorial you will learn the technique to store PHP arrays in a MySQL database with code example.

PHP arrays are versatile data structures widely used in web development for organizing and manipulating data. However, persisting array data in databases requires careful handling due to the hierarchical nature of arrays. In this guide, we’ll explore a straightforward approach to store PHP arrays in MySQL databases using serialization.

PHP Array Serialization:

Serialization is the process of converting a PHP array into a string representation that can be stored in a database. PHP provides built-in functions like serialize() and json_encode() for this purpose. Serialization preserves the array structure and allows it to be reconstructed when retrieved from the database.

<?php
$user = array(
    'name' => 'Aakash Chopra',
    'age' => 30,
    'email' => '[email protected]',
    'skills' => array('PHP', 'MySQL', 'JavaScript')
);

// Serialize the array
$serializedData = serialize($user);
echo $serializedData;

Output:

a:4:{s:4:"name";s:13:"Aakash Chopra";s:3:"age";i:30;s:5:"email";s:15:"[email protected]";s:6:"skills";a:3:{i:0;s:3:"PHP";i:1;s:5:"MySQL";i:2;s:10:"JavaScript";}}

Storing the Serialized Array in the Database:

To store serialized array data in a MySQL database, establish a connection using PHP’s MySQLi extension or PDO. Create an appropriate table with a column to store the serialized data. Ensure that the column type (e.g., VARCHAR, TEXT) accommodates the serialized data without truncation.

Code Example:

<?php
// Sample PHP array
$data = array(
    'name' => 'John Doe',
    'age' => 30,
    'email' => '[email protected]',
    'skills' => array('PHP', 'MySQL', 'JavaScript')
);

// Serialize the array
$serializedData = serialize($data);

// Database connection parameters
$servername = "localhost";
$username = "username";
$password = "password";
$database = "my_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// SQL query to insert serialized data into the database
$sql = "INSERT INTO array_data (serialized_data) VALUES ('$serializedData')";

if ($conn->query($sql) === TRUE) {
    echo "Array data stored successfully!";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

// Close connection
$conn->close();

Deserialize the Serialized Array:

To retrieve the serialized array data from the database, execute a SELECT query and fetch the serialized data. Use the unserialize() function to deserialize the string back into a PHP array. Here is an example:

<?php
$serializedData = 'a:4:{s:4:"name";s:13:"Aakash Chopra";s:3:"age";i:30;s:5:"email";s:15:"[email protected]";s:6:"skills";a:3:{i:0;s:3:"PHP";i:1;s:5:"MySQL";i:2;s:10:"JavaScript";}}';

$backIntoArray = unserialize($serializedData);

print_r($backIntoArray);

Output:

Array
(
    [name] => Aakash Chopra
    [age] => 30
    [email] => [email protected]
    [skills] => Array
        (
            [0] => PHP
            [1] => MySQL
            [2] => JavaScript
        )

)