How to Store PHP Arrays into 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 into MySQL database with code example.

You can’t store PHP arrays directly into a SQL database. First you have to convert an array into a string, then you can store the string in a database.

Step 1: 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";}}

Step 2: Storing the Serialized Array in the Database:

To store serialized array data in a MySQL database:

1. Establish a connection using PHP’s MySQLi extension or PDO.
2. Create an appropriate table with a column to store the serialized data.
3. Ensure that the column type (e.g., VARCHAR, TEXT) accommodates the serialized data without truncation.

Here is an 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();

Step 3: Deserialize the Serialized Array:

To retrieve the serialized array data from the database:

1. Execute a SELECT query and fetch the serialized data.
2. Then 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
        )

)