Insert an array into a MySQL database using PHP

Today, we will learn simple ways to insert an array into a MySQL database using PHP. This is a very common use case where we have an array that we want to store in the database table.

We can't insert an array into the database directly so we need to convert an array into a string. Here, we’ll explain two ways to convert an array into a string and easily retrieve it from the MySQL database using PHP.

Two ways to insert an array into a MySQL database

1. Convert an array into serialized string
2. Convert an array into JSON string

First of all, we will take one PHP array that contains the user data as follows.

$userArray = array(
	"name"=>"test",
	"email"=>"[email protected]",
	"country"=>"USA",
	"status"=> "true"
);

1. Convert an array into serialized string

We can convert an array into serialized string and convert back from the serialized string to array using `serialize()` and `unserialize()` functions. Now, we will insert the PHP array after converting it into a serialized string using the `serialize()` function.

<!--?php<p-->

// database connection
$conn = mysqli_connect("localhost", "username", "password", "database") or die($conn);

// user array

$userArray = array(
	"name"=>"test",
	"email"=>"[email protected]",
	"country"=>"USA",
	"status"=> "true"
);

// convert to serialized string
$serialized_userdata = serialize($userArray);

// $serialized_userdata will be
// a:4:{s:4:"name";s:4:"test";s:5:"email";s:14:"[email protected]";s:7:"country";s:5:"USA";s:6:"status";s:4:"true";}

// insert into database

$sql = "insert into users (details) value ('$serialized_userdata')";
mysqli_query($conn, $sql);

?>

Fetch data from the database and convert into an array

<!--?php<p-->

// database connection
$conn = mysqli_connect("localhost", "username", "password", "database") or die($conn);

// fetch data from database and convert to array

$sql = "select details from users";
$result = mysqli_query($conn, $sql);

while($data = mysqli_fetch_array($result))
{
	// convert unserialize string to array

   	$array = unserialize($data["details"]);
   	print_r($array);
}

?>

2. Convert an array into JSON string

Using the `json_encode()` function, we can convert an array into JSON string and the `json_decode()` function used for converting JSON to an array. We recommend you to use the json method which is simple and accessible by javascript and other languages.

<!--?php<p-->

// database connection
$conn = mysqli_connect("localhost", "username", "password", "database") or die($conn);

// user array

$userArray = array(
	"name"=>"test",
	"email"=>"[email protected]",
	"country"=>"USA",
	"status"=> "true"
);

// convert to json

$json_userdata = json_encode($userArray);
//  $json_userdata will be {"name":"test","email":"[email protected]","country":"USA","status":"true"}

// insert into database

$sql = "insert into users (details) value ('$json_userdata')";
mysqli_query($conn, $sql);

?>

Fetch data from the database and convert into an array

<!--?php<p-->

// database connection
$conn = mysqli_connect("localhost", "username", "password", "database") or die($conn);

// fetch data from the database

$sql = "select details from users";
$result = mysqli_query($conn, $sql);

while($data = mysqli_fetch_array($result))
{
	// convert JSON to array

  	$array = json_decode($data["details"],true);
   	print_r($array);
}

?>

That’s it for today.
Thank you for reading. Happy Coding..!!