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.
To store an array in the database, there are 2 possible alternatives:
- Convert the array into a JSON-encoded string, and store it in the database.
- Create a separate table to store the array of items one by one.
Just how does each method work? Let us walk through some examples, read on!
TABLE OF CONTENTS
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
EXAMPLE CODE DOWNLOAD
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
PHP ARRAY IN MYSQL DATABASE
All right, let us now get into the examples of how to store arrays in the database and retrieve them.
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.
EXTRAS
That’s all for the code, and here are a few small extras that may be useful to you.
LINKS & REFERENCES
- JSON encode and decode – PHP
- Implode and explode – PHP
- Serialize and unserialize – PHP
- PHP Data Objects – PHP
- What is JSON? – Code Boxx
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!