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 access and 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.
Contents
- Table structure
- Configuration
- With serialize() and unserialize()
- With implode() and explode()
- With Loop
- Conclusion
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 stores it in a single column. You need to use the unserialize() method to convert the serialized value and get it back in the Array format.
If you found this tutorial helpful then don't forget to share.