Store & Retrieve PHP Arrays In MYSQL Database (Simple Examples)

Welcome to a tutorial on how to store and retrieve PHP arrays in an MYSQL database. So you want to store a PHP array in the MYSQL database? Well, it is possible, but not so straightforward.

Just how does each method work? Let us walk through some examples, read on!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

YOUTUBE TUTORIAL

 

PART 1) PHP DATABASE LIBRARY

<?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) : void {
    $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 fetchCol ($sql, $data=null) {
    $this->exec($sql, $data);
    return $this->stmt->fetchAll(PDO::FETCH_COLUMN);
  }
 
  // (F) FETCH ALL
  function fetchAll ($sql, $data=null) {
    $this->exec($sql, $data);
    return $this->stmt->fetchAll();
  }
}
 
// (G) 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", "");
 
// (H) 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, H) When $_DB = new Database() is created, the constructor will automatically connect to the database. The destructor closes the connection.
• (C To F) Database functions.
  • exec() Helper function to execute an SQL query.
  • fetch() Fetch a single column of data – SELECT `COLUMN` FROM `TABLE` WHERE `ID`=N.
  • fetchCol() Fetch multiple rows on a single column – SELECT `COLUMN` FROM `TABLE`.
  • fetchAll() Fetch multiple rows – SELECT * FROM `TABLE`.
• (G) Remember to change the settings to your own.

 

 

PART 2) JSON ENCODED ARRAY

2A) DUMMY DATABASE TABLE

CREATE TABLE `people` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `colors` JSON 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. Take note, this is JSON datatype.

P.S. For the uninitiated, JSON stands for “Javascript Object Notation”. Literally, just a formatted string to represent arrays and objects. Simple examples – ["One", "Two"] and {"Key":"Value"}

 

2B) JSON ENCODE & STORE IN DATABASE

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

Yep, it’s that easy. We do a json_encode(ARRAY) to turn the array into a string, and insert it into the database “as usual”.

 

 

2C) RETRIEVE FROM DATABASE & JSON DECODE

<?php
// (A) LOAD DATABASE LIBRARY
require "1-database.php";
 
// (B) FETCH & JSON DECODE
$sql = "SELECT `colors` FROM `people` WHERE `id`=?";
$colors = json_decode($_DB->fetch($sql, [1])); // JOB
print_r($colors);
 
$colors = json_decode($_DB->fetch($sql, [2])); // JOE
print_r($colors);
 
$colors = json_decode($_DB->fetch($sql, [3])); // JOY
print_r($colors);

When we fetch the colors from the database, it will be a flat string. To “convert” it back to an array, we simply do a json_decode(ENCODED-STRING).

 

 

PART 3) SEPARATE TABLE

3A) FAVORITE COLORS TABLE

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 a JSON-encoded string is 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

<?php
// (A) LOAD 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) . ";";
echo $sql; print_r($data);
 
// (D) GO!
$_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 loop through the colors array to create the SQL entries one by one.

 

 

3C) RETRIEVING THE ARRAY

<?php
// (A) LOAD DATABASE LIBRARY
require "1-database.php";

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

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

 

3D) SEPARATE TABLES ARE WAY BETTER

<?php
// (A) LOAD DATABASE LIBRARY
require "1-database.php";
 
// (B) POPULAR COLORS
$colors = $_DB->fetchAll("SELECT `color`, COUNT(*) `count`
FROM `colors`
GROUP BY `color`
ORDER BY `count` DESC");
print_r($colors);

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

• With JSON-encode – 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.

 

 

LINKS & REFERENCES

• JSON encode and decode – PHP
• Implode and explode – PHP
• Serialize and unserialize – PHP
• PHP Data Objects – PHP
• What is JSON? – Code Boxx