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
First of all, we will take one PHP array that contains the user data as follows.
1 2 3 4 5 6 | $userArray = array( "name"=>"test", "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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | <?php // database connection $conn = mysqli_connect("localhost", "username", "password", "database") or die($conn); // user array $userArray = array( "name"=>"test", "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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | <?php // 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | <?php // database connection $conn = mysqli_connect("localhost", "username", "password", "database") or die($conn); // user array $userArray = array( "name"=>"test", "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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | <?php // 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..!!
It’s very trouble-free to find out any matter on net as compared to textbooks, as I found this article at this website.