How to Store Array in MySQL with PHP

An array is a special variable that allows storing one or more values in a single variable e.g. – holding usernames or details in an Array.


They are easier to manipulate.

Sometimes, require to store Array in the MySQL database and retrieve it.

In this tutorial, I show how you can store an Array in the MySQL database and read it with PHP.


1. Table structure

Create contents_arr table.

The ‘arr_serialize1’ and ‘arr_serialize2’ is used to store serialized value.

CREATE TABLE `contents_arr` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `username` varchar(80) NOT NULL, `name` varchar(80) NOT NULL, `arr_serialize1` varchar(255) NOT NULL, `arr_serialize2` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Configuration

Create a config.php for the database connection.

Completed Code

<?php $host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */ $con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) { die("Connection failed: " . mysqli_connect_error());
}

3. With serialize() and unserialize()

Define two arrays – $names_arr, and $users_arr.

$names_arr Array is Indexed type Array and $users_arr is an Associative Array.

Serialize Syntax – 

serialize([Array]);

Pass the array in the serialize() method and pass the serialized values in the INSERT query.

Unserialize Syntax –

unserialize([Serialized value]);

Fetch records and pass the serialized value in the unserialize() method to convert it to Array format.

Completed Code

<?php include "config.php"; // Indexed Array
$names_arr = array("Yogesh singh","Sonarika Bhadoria","Vijay Maurya"); // Associative Array
$users_arr[] = array("username"=>"yssyogesh","name"=>"Yogesh singh");
$users_arr[] = array("username"=>"bsonarika","name"=>"Sonarika Bhadoria");
$users_arr[] = array("username"=>"vijay","name"=>"Vijay Maurya"); // Serialize the Array
$names_str = serialize($names_arr);
$users_str = serialize($users_arr); // Insert record
$sql = "INSERT INTO contents_arr(arr_serialize1,arr_serialize2) VALUES('".$names_str."','".$users_str."')";
mysqli_query($con,$sql); // Read record
$sql = mysqli_query($con,"SELECT * FROM contents_arr");
while($row = mysqli_fetch_assoc($sql)){ // Unserialize $arr_unserialize1 = unserialize($row['arr_serialize1']); $arr_unserialize2 = unserialize($row['arr_serialize2']); // Display echo "<pre>"; print_r($arr_unserialize1); print_r($arr_unserialize2); echo "</pre>";
}

Output –

Array
( [0] => Yogesh singh [1] => Sonarika Bhadoria [2] => Vijay Maurya
)
Array
( [0] => Array ( [username] => yssyogesh [name] => Yogesh singh ) [1] => Array ( [username] => bsonarika [name] => Sonarika Bhadoria ) [2] => Array ( [username] => vijay [name] => Vijay Maurya ) )

4. With implode() and explode()

Use implode() to separate the $names_arr by separator (” , “) and get a string. Pass the value in the INSERT query.

Fetch records and use explode() to convert a comma-separated string in an Array format.

In the example, I am displaying the value in a string and Array format.

Completed Code

<?php include "config.php"; // Indexed Array
$names_arr = array("Yogesh singh","Sonarika Bhadoria","Vijay Maurya"); // Separate Array by " , "
$names_str = implode(" , ",$names_arr); // Insert record
$sql = "INSERT INTO contents_arr(name) VALUES('".$names_str."')";
mysqli_query($con,$sql); // Read record
$sql = mysqli_query($con,"SELECT * FROM contents_arr");
while($row = mysqli_fetch_assoc($sql)){ $name = $row['name']; $name_explode = explode(" , ",$row['name']); echo "name : ".$name."<br>"; echo "<pre>"; print_r($name_explode); echo "</pre>";
}

Output –

name : Yogesh singh , Sonarika Bhadoria , Vijay Maurya
Array
( [0] => Yogesh singh [1] => Sonarika Bhadoria [2] => Vijay Maurya
)

5. With Loop

Loop on the $users_arr Array.

Read and pass the value in the INSERT query. New record is inserted until data is available.

Completed Code

<?php include "config.php"; // Associative Array
$users_arr[] = array("username"=>"yssyogesh","name"=>"Yogesh singh");
$users_arr[] = array("username"=>"bsonarika","name"=>"Sonarika Bhadoria");
$users_arr[] = array("username"=>"vijay","name"=>"Vijay Maurya"); // Insert record
foreach($users_arr as $userid=>$user){ $username = $user['username']; $name = $user['name']; $sql = "INSERT INTO contents_arr(username,name) VALUES('".$username."','".$name."')"; mysqli_query($con,$sql); } // Read record
$sql = mysqli_query($con,"SELECT * FROM contents_arr");
while($row = mysqli_fetch_assoc($sql)){ $username = $row['username']; $name = $row['name']; echo "username : ".$username.", name : ".$name."<br>";
}

Output –

username : yssyogesh, name : Yogesh singh
username : bsonarika, name : Sonarika Bhadoria
username : vijay, name : Vijay Maurya

6. Conclusion

It is better to use serialize() method which converts an Array to string format and store in a single column.

You need to use the unserialize() method to convert the serialized value and get back in the Array format.

Related Posts