How to store and retrieve array in MySQL and PHP

This is the most important technique for all Php developers because of Mysql can't store the array values directly.
So we need to convert the PHP Array to string by using the serialize().

This mechanism is very useful in the e-commerce cart functionality. For example, we can store the customer purchase items list and price can be stored in the associative array and convert into string and store in the MySQL.
This way we can reduce the logic of the code get the accurate result also.

MySql Code

CREATE TABLE `students` (
  `id` int(11) NOT NULL,
  `language` varchar(50) NOT NULL,
  `student_names` mediumtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Indexes for table `students`
--
ALTER TABLE `students`
  ADD PRIMARY KEY (`id`);
 
--
-- AUTO_INCREMENT for table `students`
--
ALTER TABLE `students`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

You need to connect the database create the dbconfig.php file

<?php  
$servername = "localhost";  
$username = "root";  
$password = "";  
$dbname = "techiesbadi";  
// Create connection  
$con = new mysqli($servername,$username, $password, $dbname);  
// Check connection  
if ($con->connect_error) {  
die("Connection failed: " . $con->connect_error);  
}  
?>

Now Store the PHP Array in MySql create the store-array.php file

<?php  
include "dbconfig.php";  
$students = array("ramu", "ravi", "gopal", "krishna");  
//Converting array to String  
$student_names=mysqli_real_escape_string($con, serialize($students));  
$sql = "INSERT INTO `students` (`id`, `language`, `student_names`) VALUES (NULL, 'php', '$student_names')";  
$result = $con->query($sql);  
if($result === TRUE){  
     echo "Record inserted successfully";  
}  
?>

Now retrieve the PHP Array from MySQL create the retrieve-array.php file

<?php  
 include "dbconfig.php";  
 $getStudnets = $con->query("SELECT * FROM `students` LIMIT 1");   
 $studentsData = $getStudnets->fetch_object();  
 $language = $studentsData->language;  
 //Converting string to array  
 $students = unserialize($studentsData->student_names);  
 //printing students array  
 print_r($students);  
 echo "<br />";  
 foreach ($students as $student) {  
      echo $student." learning ".$language." language.<br/>";  
 }  
 ?>

Here you can download the Full Source code and check the demo.

Download Demo

* If you like this post please don't forget to subscribe TechiesBadi - programming blog for more useful stuff

Share this

Related Posts