How to Store Array in MySQL with PHP

An array is a special variable which 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 to 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.

How to store Array in MySQL with PHP


Contents

  1. Table structure
  2. Configuration
  3. With serialize() and unserialize()
  4. With implode() and explode()
  5. With Loop
  6. 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 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 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 unserialize() method to convert the serialized value and get back in the Array format.

If you found this tutorial helpful then don't forget to share.

Related Post

Spread the love
  • 3
  •  
  •  
  •  
  •  
  •  

2 Comments

  1. Gracel Robby said:

    This a great post!! I have been looking for this content for so long. This code has saved my time. As always your posts are worth to read. Keep sharing and educating us! 🙂

    December 18, 2018
    Reply
  2. parag MBI said:

    Is it better than storing as JSON?

    January 3, 2019
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *