2 Ways to Store & Retrieve Arrays In 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 in 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 in 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 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.

 

 

TLDR – QUICK SLIDES

Fullscreen Mode – Click Here

 

TABLE OF CONTENTS

 

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 in 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 examples, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

ARRAY IN DATABASE

All right, let us now get into the examples of how to store arrays in the database and retrieve them.

 

PART 1) PHP 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_CHARSET,
      DB_USER, DB_PASSWORD, [
      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) HELPER - EXECUTE SQL QUERY
  function exec ($sql, $data=null) {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }
 
  // (D) FETCH COLUMN
  function fetch ($sql, $data=null) {
    $this->exec($sql, $data);
    return $this->stmt->fetchColumn();
  }
 
  // (E) FETCH ALL (ON A SINGLE COLUMN)
  function fetchAll ($sql, $data=null) {
    $this->exec($sql, $data);
    return $this->stmt->fetchAll(PDO::FETCH_COLUMN);
  }
}
 
// (F) SETTINGS - CHANGE THESE TO YOUR OWN !
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");
 
// (G) DATABASE OBJECT
$_DB = new Database();

Let us start by creating a database class, so we don’t have to repeat “connect to database” and “execute SQL statement” in the following examples. It may look complicated, but keep calm and look closely:

  • (A, B, G) When $_DB = new Database() is created, the constructor will automatically connect to the database. The destructor closes the connection.
  • (C, D, E) Database functions.
    • exec() Helper function to execute an SQL query.
    • fetch() Fetch a single column of data – SELECT `COLUMN` FROM `TABLE` WHERE `ID`=N.
    • fetchAll() Fetch multiple rows (on a single column) – SELECT `COLUMN` FROM `TABLE`.
  • (F) Remember to change the settings to your own.

 

 

PART 2) CONVERT ARRAY TO STRING

2A) DUMMY DATABASE TABLE

2a-people.sql
CREATE TABLE `people` (
  `id` bigint(20) 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` bigint(20) NOT NULL AUTO_INCREMENT;

For this example, we will work with a dummy table that stores people and their favorite colors.

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

 

2B) CONVERT TO STRING & STORE IN THE DATABASE

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

As in the introduction, one way of storing arrays is to turn them into a flat string. 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.

 

 

2C) RETRIEVE FROM DATABASE & CONVERT TO ARRAY

2c-retrieve-string.php
<?php
// (A) DATABASE LIBRARY & SELECT SQL
require "1-database.php";
$sql = "SELECT `colors` FROM `people` WHERE `id`=?";
 
// (B) JSON DECODE
$colors = json_decode($_DB->fetch($sql, [1]));
print_r($colors);
 
// (C) UNSERIALIZE
$colors = unserialize($_DB->fetch($sql, [2]));
print_r($colors);
 
// (D) EXPLODE
$colors = explode(",", $_DB->fetch($sql, [3]));
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 a PHP array, object, or function.
  • explode() Breaks the string into an array, with the separator you specified.

 

2D) 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. But the truth is, I won’t bother with this encoding-decoding most of the time. See the next example below.

 

 

PART 3) SEPARATE TABLE

3A) FAVORITE COLORS TABLE

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

Using an encoded string is all cool, but it has some shortcomings. In this example, we create a “standalone favorite colors” table. For those who are lost – Every color will be a single entry in this table.

 

3B) STORING DATA

3b-store-separate.php
<?php
// (A) DATABASE LIBRARY
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) {
  array_push($data, $id, $c);
  $sql .= "(?,?),";
}}
$sql = substr($sql, 0, -1) . ";";
 
// (D) GO!
echo $sql; print_r($data);
$_DB->exec($sql, $data);
echo "OK";

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.

 

 

3C) RETRIEVING THE ARRAY

3c-retrieve-separate.php
<?php
// (A) DATABASE & SELECT SQL
require "1-database.php";
$sql = "SELECT `color` FROM `colors` WHERE `id`=?";

// (B) FETCH
$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);

Retrieving the data from the database is no sweat at all.

 

3D) SEPARATE TABLES ARE WAY BETTER

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

  • With data string – Extract all records, decode all the strings, loop through the arrays, sum the colors together, then do an array sort.
  • With separate table – SELECT `color`, COUNT(*) `count` FROM `colors` GROUP BY `color` ORDER BY `count` DESC.

Yep. The beauty of creating a separate table is in the power 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.

 

EXTRA BITS & LINKS

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

 

LINKS & REFERENCES

 

YOUTUBE TUTORIAL

 

INFOGRAPHIC CHEAT SHEET

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 *