2 Ways to Store & Retrieve Arrays Into Database With PHP MYSQL

Welcome to a tutorial on how to store and retrieve arrays into the database using PHP and MySQL. So you have an array in PHP and want to store it into the database? Well, bad news. MySQL only takes in flat strings, numbers, and timestamps. It does not accept arrays or objects.

To store an array into the database, there are 2 possible alternatives:

  • Convert and store the array as a flat string, using json_encode(), serialize(), or implode().
  • Create a separate table to store the array of items one by one.

But just how does each work? How do we store and retrieve encoded strings? Let us walk through some examples, read on to find out!

ⓘ I have included a zip file with all the example code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes Database Class Array To String
Separate Table Useful Bits & Links The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Open 1-database.php, change the database settings to your own.
  • There are two sets of examples.
    • Files starting with 2- demonstrates how to convert an array into a string, and store it into the database.
    • Files starting with 3- demonstrates how to use a “dedicated standalone” table.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

PART 1) DATABASE CLASS

Let us start with creating a database class, so we don’t have to repeat “connect to database” and “execute SQL statement” in the following examples.

 

PHP PDO DATABASE LIBRARY

1-database.php
<?php
class DB {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  function __construct () {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHAR,
      DB_USER, DB_PASS, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  }
 
  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt!==null) { $this->stmt = null; }
    if ($this->pdo!==null) { $this->pdo = null; }
  }
 
  // (C) EXECUTE SQL
  function exec ($sql, $data=null) {
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($data);
      return true;
    } catch (Exception $ex) {
      $this->error = $ex->getMessage();
      return false;
    }
  }
 
  // (D) FETCH (SINGLE ROW)
  function fetch ($sql, $data=null) {
    if ($this->exec($sql, $data) === false) { return false; }
    return $this->stmt->fetch();
  }
 
  // (E) FETCH ALL (SINGLE COLUMN)
  function fetchAll ($sql, $data=null) {
    if ($this->exec($sql, $data) === false) { return false; }
    return $this->stmt->fetchAll(PDO::FETCH_COLUMN);
  }
}
 
// (F) SETTINGS - CHANGE THESE TO YOUR OWN !
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHAR", "utf8");
define("DB_USER", "root");
define("DB_PASS", "");
 
// (G) DATABASE OBJECT
$DB = new DB();

This may look complicated, but keep calm and look closely:

  • (A & B) When $DB = new DB() is created, the constructor will automatically connect to the database. The destructor closes the connection.
  • (C To E) Database functions.
    • exec() Executes an SQL statement.
    • fetch() Fetch a single row of data – SELECT * FROM `TABLE` WHERE `ID`=N.
    • fetchAll() Fetch multiple rows (but single column) – SELECT `COLUMN` FROM `TABLE`.
  • (F & G) Self-explanatory. DOH.

Remember to change the database settings to your own.

 

 

PART 2) ARRAY TO STRING

With the database library out of the way, let us now get into the first method – Converting an array into a string, then converting them back from string to array.

 

DUMMY PEOPLE TABLE

2a-people.sql
CREATE TABLE `people` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `colors` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `people`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`);
 
ALTER TABLE `people`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Yep, we will be using this dummy table for example. All it does is store people and their favorite colors.

  • id Person ID, primary key.
  • name Person’s name.
  • colors Their favorite colors.

 

CONVERT TO STRING & STORE IN DATABASE

2b-store-string.php
<?php
// (A) DATABASE & INSERT SQL
require "1-database.php";
$sql = "INSERT INTO `people` (`name`, `colors`) VALUES (?, ?)";
 
// (B) JSON ENCODE
$person = "Job";
$colors = json_encode(["Red", "Green", "Blue"]);
echo $DB->exec($sql, [$person, $colors]) ? "JSON OK" : $DB->error ;
 
// (C) SERIALIZE
$person = "Joe";
$colors = serialize(["Red"]);
echo $DB->exec($sql, [$person, $colors]) ? "SERIALIZE OK" : $DB->error ;
 
// (D) IMPLODE
$person = "Joy";
$colors = implode(",", ["Red", "Green"]);
echo $DB->exec($sql, [$person, $colors]) ? "IMPLODE OK" : $DB->error ;

These should be straightforward, just the usual SQL INSERT statement. But take note of the 3 different methods that we can use to convert an array to a string.

  • json_encode() Javascript Object Notation.
  • serialize() The default PHP mechanism for storing and representing arrays, objects, functions as a string.
  • implode() This one simply combines all the elements into a flat string, separated with your specified separator.

 

 

RETRIEVE FROM DATABASE & CONVERT TO ARRAY

2c-retrieve-string.php
<?php
// (A) DATABASE & SELECT SQL
require "1-database.php";
$sql = "SELECT * FROM `people` WHERE `name`=?";
 
// (B) JSON DECODE
$colors = $DB->fetch($sql, ["Job"]);
$colors = json_decode($colors["colors"]);
print_r($colors);
 
// (C) UNSERIALIZE
$colors = $DB->fetch($sql, ["Joe"]);
$colors = unserialize($colors["colors"]);
print_r($colors);
 
// (D) EXPLODE
$colors = $DB->fetch($sql, ["Joy"]);
$colors = explode(",", $colors["colors"]);
print_r($colors);

Yep, we are just doing the good old SELECT query to get the string from the database, then converting it back to an array.

  • json_dencode() To turn a JSON string back into an array (or object).
  • deserialize() Turns a serialized string back into PHP array, object, function.
  • explode() Breaks the string into an array, with the separator you specified.

 

JSON, IMPLODE, SERIALIZE – WHICH IS BETTER?

  • I will recommend using JSON as it is the de-facto industry standard – JSON is adopted in Javascript, Python, C, C++, C#, Java, and more… It will work so long as there is a proper JSON parser.
  • If you are working with “strictly PHP only”, serialize() is also a pretty interesting choice – We can also store objects and functions with it.
  • Lastly, I will not recommend using implode().

 

 

PART 3) SEPARATE TABLE

Using an encoded string is all cool, but it has some shortcomings. Let us walk through the second solution in this section, by creating another table to store the favorite colors exclusively.

 

FAVORITE COLORS TABLE

3a-colors.sql
CREATE TABLE `fav_color` (
  `id` int(11) NOT NULL,
  `color` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
ALTER TABLE `fav_color`
  ADD PRIMARY KEY (`id`,`color`);

Now, this is a “standalone favorite colors” table. For those who are somehow lost – Every color will be a single entry in this table.

 

STORING DATA

3b-store-separate.php
<?php
// (A) DATABASE
require "1-database.php";

// (B) COLORS (USER ID => COLORS)
$colors = [
  "1" => ["Red", "Green", "Blue"],
  "2" => ["Red", "Magenta", "Orange", "Yellow"],
  "3" => ["Red", "Blue", "Cyan", "Lime"]
];
 
// (C) SQL INSERT & DATA
$sql = "INSERT INTO `colors` (`id`, `color`) VALUES ";
$data = [];
foreach ($colors as $id=>$col) { foreach ($col as $c) {
  $data[] = $id;
  $data[] = $c;
  $sql .= "(?,?),";
}}
$sql = substr($sql, 0, -1) . ";";
 
// (D) GO!
echo $sql;
print_r($data);
echo $DB->exec($sql, $data) ? "OK" : $DB->error ;

As you see, storing data with this second solution will be a little more tedious. But all we do is actually just loop through the colors array to create the SQL entries one by one.

 

 

RETRIEVING THE ARRAY

3c-retrieve-separate.php
<?php
// (A) DATABASE & SELECT SQL
require "1-database.php";

// (B) FETCH
$sql = "SELECT `color` FROM `colors` WHERE `id`=?";
$colors = $DB->fetchAll($sql, [1]); // JOB
print_r($colors);
$colors = $DB->fetchAll($sql, [2]); // JON
print_r($colors);
$colors = $DB->fetchAll($sql, [3]); // JOY
print_r($colors);

Nah. No sweat.

 

USEFUL BITS & LINKS

That’s all for the code, and here are a few small extras that may be useful to you.

 

SEPARATE TABLES ARE JUST BETTER

Why? I will give you 2 words – Data mining. To find out which is the most and least popular color:

  • Data string – Extract all records, decode all the strings, then sum the colors together.
  • Separate table – SELECT `color`, COUNT(*) `count` FROM `colors` GROUP BY `color` ORDER BY `count` DESC.

Yep. The beauty of creating a separate table does not lie in the convenience of coding, but the ease to do analysis. So always weigh your pros and cons carefully, that laziness to cut a few lines of code comes at the cost of losing the ability to do proper data mining.

 

LINKS & REFERENCES

 

YOUTUBE TUTORIAL

 

INFOGRAPHIC CHEAT SHEET

How To Store & Retrieve Arrays In MySQL PHP (click to enlarge)

 

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped to solve the array problem, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

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