Insert Array into MySQL Database using PHP

Hi! Today let's see how to insert array into mysql database using php. A frequently asked question by budding developers but not without reason. Since databases don't support array data types, there is no direct way to store them in db. But you can convert array as string and insert into mysql. There are two ways you can do it, one is by serializing the array and the other is saving it as json string.

But before going into the process, I must warn you it's a bad idea to store array in database as it is against the normalization theory of relational database and you cannot easily query the data later. Nevertheless, sometimes I used to store array in database for temporary log and you can also do it.

How to Insert Array into MySQL with PHP?

The first method we are going to see is about serializing php array to string. In PHP, we have two methods, serialize() and unserialize(), the first one converts array to string and the latter reverts back the string to array.

To Convert Array to String:

<?php
$array = array("name"=>"johnson", "code"=>"12345", "status"=>"true");
$string = serialize($array);
echo $string;

// output
// a:3:{s:4:"name";s:7:"johnson";s:4:"code";s:5:"12345";s:6:"status";s:4:"true";}
?>

To Convert Serialized String to Array:

<?php
$array = unserialize($string);
echo "<pre>";
print_r($array);

// output
// Array
// (
//     [name] => johnson
//     [code] => 12345
//     [status] => true
// )
?>

Method 1) Serialize Array and Save into MySQL

Now let's see about storing array in mysql with the above method. First we should convert the given array to string with serialize() function and then insert into database. Here's the php code to do it.

<?php
$con = mysqli_connect("localhost", "username", "password", "db_demo");
$user_data = array("name"=>"johnson", "code"=>"12345", "status"=>"true");
$serialized_data = serialize($user_data);

$sql = "insert into user_logs (details, created) value ('{$serialized_data}', NOW())";
mysqli_query($con, $sql);
?>

Retrieve Array Data from MySQL:

<?php
$con = mysqli_connect("localhost", "username", "password", "db_demo");
$sql = "select details from user_logs";
$result = mysqli_query($con, $sql);
while($row = mysqli_fetch_array($result))
{
   $array = unserialize($row["details"]);
   print_r($array);
}
?>

After fetching the serialized string from database, you have to use unserialize() function to turn them back into the original array. Simple as that!

Method 2) Store Array as JSON String in MySQL

The second method is to convert the array into a json string and insert into mysql. I mostly prefer this than the previous one, as json is more portable and compatible with javascript and other languages.

Basically, you have to encode the array as JSON string with json_encode() function and store it in database. Here's how to do it.

<?php
$con = mysqli_connect("localhost", "username", "password", "db_demo");
$data = array("name"=>"johnson", "code"=>"12345", "status"=>"true");
$json = json_encode($data);
$sql = "insert into user_logs (details, created) value ('{$json}', NOW())";
mysqli_query($con, $sql);
?>

Fetch JSON from DB:

<?php
$con = mysqli_connect("localhost", "username", "password", "db_demo");
$result = mysqli_query($con, "select details from user_logs");
while($row = mysqli_fetch_array($result))
{
   $arr = json_decode($row["details"], true);
   var_dump($arr);
}
?>

Here we fetch the json string which we have stored previously in the database and decode it into an array using json_decode() function.

Read:

That explains about inserting array into mysql database in php. Between the two methods, going with json is much more preferred and can be easily read by other languages. Also MySQL5.7.8 and above has native JSON support, so you can save json string directly in the table column. I hope you like this tutorial. If you find this useful, please share it with your friends.