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

INTRODUCTION

ARRAY STORAGE

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. So if you want to store an array into the database, there are 2 possible alternatives:

  • Convert and store the array as a flat string, using json_encode(), implode(), or serialize().
  • Create a separate table to store the array 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.

 

 

 

PREAMBLE

DOWNLOAD & NOTES

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

 

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.

 

QUICK NOTES

  • Download and unzip into a folder.
  • Open 0-database.php, change the database settings to your own.
  • There are two sets of examples.
    • Files starting with 1- demonstrates how to convert an array into a string, and store it into the database.
    • Files starting with 2- demonstrates how to use a “dedicated standalone” table.

If you spot a bug, please feel free to comment below. I try to answer 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.

 

PRELUDE

DATABASE CONNECTION CODE FRAGMENT

Before we start, let’s create a small fragment of code that connects to the database… Because, it is a pain to keep repeating this for all the examples below.

 

CONNECT TO DATABASE

0-database.php
<?php
// SETTINGS
// ! CHANGE THESE TO YOUR OWN !
error_reporting(E_ALL & ~E_NOTICE);
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
 
// CONNECT TO DATABASE
try {
  $pdo = new PDO(
    "mysql:host=" . DB_HOST . ";charset=" . DB_CHARSET . ";dbname=" . DB_NAME,
    DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES => false
    ]
  );
} catch (Exception $ex) {
  print_r($ex);
  die("Error connecting to database");
}

Yep, no mystery to this simple code fragment. All it does is to connect to the database using PDO (PHP Data Object) – For you guys who have not switched over to using PDO yet… It’s time.

P.S. Remember to change the database settings to your own.

 

 

METHOD 1

CONVERT ARRAY TO STRING

All right, let us now get started with the first method – Converting the array to a string before inserting, and converting them back from string to array on selecting.

 

DUMMY DATABASE TABLE

Let us now establish the foundation of this example, we will be creating a simple people table to store their favorite colors.

1a-people.sql
CREATE TABLE `people` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `fav_color` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `people`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`);

ALTER TABLE `people`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
Field Description
id Person ID, primary key, and auto-increment.
name The person’s name, unique.
fav_color The person’s favorite colors.

 

CONVERT TO STRING & STORE IN DATABASE

1b-store.php
<?php
// CONNECT DATABASE
require "0-database.php";

// STORE ARRAY
$sql = "INSERT INTO `people` (`name`, `fav_color`) VALUES (?, ?)";
 
// (A) JSON - JSON_ENCODE
try {
  $stmt = $pdo->prepare($sql);
  $colors = ["Red", "Green", "Blue"];
  $stmt->execute(["John Doe", json_encode($colors)]);
  echo "JSON - OK";
} catch (Exception $ex) {
  print_r($ex);
  echo "JSON - INSERT ERROR";
}
 
// (B) IMPLODE
try {
  $stmt = $pdo->prepare($sql);
  $colors = ["Red", "Magenta", "Orange", "Yellow"];
  $stmt->execute(["Jane Doe", implode(",", $colors)]);
  echo "IMPLODE - OK";
} catch (Exception $ex) {
  print_r($ex);
  echo "IMPLODE - INSERT ERROR";
}
 
// (C) SERIALIZE
try {
  $stmt = $pdo->prepare($sql);
  $colors = ["Red", "Blue", "Cyan", "Lime"];
  $stmt->execute(["Joy Doe", serialize($colors)]);
  echo "SERIALIZE - OK";
} catch (Exception $ex) {
  print_r($ex);
  echo "SERIALIZE - INSERT ERROR";
}

This one should be pretty straightforward, just your 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(ARRAY) Javascript Object Notation.
  • implode(SEPARATOR, ARRAY) This one simply combines all the elements into a flat string, separated with SEPARATOR.
  • serialize(ARRAY) The default PHP mechanism to store and represent arrays, objects, functions in a string.

 

 

RETRIEVE FROM DATABASE & CONVERT TO ARRAY

1c-retrieve.php
<?php
// CONNECT DATABASE
require "0-database.php";
 
// RETRIEVE ARRAY
$sql = "SELECT * FROM `people`";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
 
// (A) JSON - JSON_DENCODE
$colors = json_decode($result[0]['fav_color']);
echo "JSON";
print_r($colors);
 
// (B) EXPLODE
$colors = explode(",", $result[1]['fav_color']);
echo "EXPLODE";
print_r($colors);
 
// (C) UNSERIALIZE
$colors = unserialize($result[2]['fav_color']);
echo "UNSERIALIZE";
print_r($colors);

Pretty much the same old process of connecting to the database, and using SELECT to fetch the entries. But this does the opposite to fetch and “decode” the string back into an array.

  • json_dencode(STRING) To turn a JSON string back into an array (or object).
  • explode(SEPARATOR, STRING) Turns the string into an array, with elements denoted by SEPARATOR.
  • deserialize(STRING) Turns serialized string back into PHP array, object, function.

 

JSON, IMPLODE, SERIALIZE – WHICH IS THE BEST?

Personally, I will recommend using JSON as it is the current de-facto standard in the industry; JSON is used in Javascript, Python, C, C++, C#, Java, and nearly anywhere with the proper JSON parser. Although serialize is also pretty interesting – We can also store objects and functions with it.

Lastly, I will recommend avoiding using implode. Why? Consider the possibilities of having a comma character in the array. For example, $data = ["foo", "bar", "hello, world"]. So when we implode(",", $data), this is going to cause some trouble later; An array with originally 3 items will result in 4 items with explode(",", STRING).

 

 

ENCODED STRINGS ARE STILL BAD

Yes, encoded strings work. But what is so bad about encoded strings? Well, consider the following scenario where we have to find out which is the most popular favorite color. We will have to extract all the encoded strings in the database, decode it, then manually count them one-by-one…

<?php
// CONNECT DATABASE
require "0-database.php";

// GET ALL COLORS
$sql = "SELECT * FROM `people`";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$all = $stmt->fetchAll();

// DATA CRUNCHING
$total = [];
foreach ($all as $a) {
  $colors = json_decode($a['fav_color']);
  foreach ($colors as $c) {
    if ($total[$c]) { $total[$c]++; }
    else { $total[$c] = 1; }
  }
}

When we have thousands of entries, this is just going to be very slow and inefficient. This is why encoded strings are bad, and the smarter way is to create a separate table to store the favorite colors.

 

METHOD 2

USING A 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

2a-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`);
Field Description
id Person ID, partial primary key.
color Color name, partial primary key.

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

2b-store.php
<?php
// CONNECT DATABASE
require "0-database.php";

// (ID: 1) JOHN DOE
$id = 1;
$colors = ["Red", "Green", "Blue"];
$sql = "INSERT INTO `fav_color` (`id`, `color`) VALUES ";
$data = [];
foreach ($colors as $c) {
  $sql .= "(?,?),";
  $data[] = $id;
  $data[] = $c;
}
$sql = substr($sql, 0, -1) . ";";
try {
  $stmt = $pdo->prepare($sql);
  $stmt->execute($data);
  echo "OK" . $id;
} catch (Exception $ex) {
  print_r($ex);
  echo "ERROR" . $id;
}

// (ID: 2) JANE DOE
$id = 2;
$colors = ["Red", "Magenta", "Orange", "Yellow"];
$sql = "INSERT INTO `fav_color` (`id`, `color`) VALUES ";
$data = [];
foreach ($colors as $c) {
  $sql .= "(?,?),";
  $data[] = $id;
  $data[] = $c;
}
$sql = substr($sql, 0, -1) . ";";
try {
  $stmt = $pdo->prepare($sql);
  $stmt->execute($data);
  echo "OK" . $id;
} catch (Exception $ex) {
  print_r($ex);
  echo "ERROR" . $id;
}

// (ID: 3) JOY DOE
$id = 3;
$colors = ["Red", "Blue", "Cyan", "Lime"];
$sql = "INSERT INTO `fav_color` (`id`, `color`) VALUES ";
$data = [];
foreach ($colors as $c) {
  $sql .= "(?,?),";
  $data[] = $id;
  $data[] = $c;
}
$sql = substr($sql, 0, -1) . ";";
try {
  $stmt = $pdo->prepare($sql);
  $stmt->execute($data);
  echo "OK" . $id;
} catch (Exception $ex) {
  print_r($ex);
  echo "ERROR" . $id;
}

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

 

RETRIEVING THE ARRAY

2c-retrieve.php
<?php
// CONNECT DATABASE
require "0-database.php";

// RETRIEVE ARRAY
$sql = "SELECT * FROM `fav_color` WHERE `id`=?";
$stmt = $pdo->prepare($sql);

// (ID 1: JOHN DOE)
$stmt->execute([1]);
$colors = [];
while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
  $colors[] = $row['color'];
}
echo "1 - JOHN DOE";
print_r($colors);
 
// (ID 2: JANE DOE)
$stmt->execute([2]);
$colors = [];
while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
  $colors[] = $row['color'];
}
echo "2 - JANE DOE";
print_r($colors);
 
// (ID 3: JOY DOE)
$stmt->execute([3]);
$colors = [];
while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
  $colors[] = $row['color'];
}
echo "3 - JOY DOE";
print_r($colors);

As to fetching the favorite colors, there is no need for any decoding here – Just pull all the data back out from the database.

 

WHY A SEPARATE TABLE MAKES SENSE

Now with a separate favorite color table, let’s revisit that million-dollar question. Why is this better? One single sentence – We can do data mining and analysis very easily.

SELECT `color`, COUNT(*) `count` 
FROM `fav_color` 
GROUP BY `color` 
ORDER BY `count` DESC

The true value of creating a separate table does not lie in the convenience of coding, but the ease to do the analysis. So always weigh your pro 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

USEFUL BITS & LINKS

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

 

REFERENCES & LINKS

 

EXTRA

VIDEO TUTORIAL

For you guys who want more, here’s the video tutorial, and shameless self-promotion – Subscribe to the Code Boxx YouTube channel for more!

 

YOUTUBE TUTORIAL

 

INFOGRAPHIC CHEAT SHEET

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

 

CLOSING

WHAT’S NEXT?

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!

2 thoughts on “2 Ways to Store and Retrieve Arrays Into Database With PHP MYSQL”

    1. Hi Whit, that is quite an advanced topic that is definitely not covered in this tutorial – Feel free to implement your own filters and checks if that is a concern.

Leave a Comment

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