2 Ways to Store and 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:

  1. Convert and store the array as a flat string, using json_encode(), implode(), or serialize().
  2. 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.

 

 

REAL QUICK SLIDES

 

TABLE OF CONTENTS

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

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example 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

  • 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.

 

 

CODE FRAGMENT – DATABASE CONNECTION

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
// (A) SETTINGS - CHANGE THESE TO YOUR OWN !
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
 
// (B) 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
    ]
  );
} catch (Exception $ex) { exit($ex->getMessage()); }

Yep, no mystery to this simple code fragment. All it does is 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) 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.

 

1A) DUMMY DATABASE TABLE

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;

First, we create a simple “people” table to store their favorite colors.

 

1B) CONVERT TO STRING & STORE IN DATABASE

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

// (B) STORE ARRAY
$sql = "INSERT INTO `people` (`name`, `fav_color`) VALUES (?, ?)";
 
// (B1) 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) { exit($ex->getMessage()); }
 
// (B2) IMPLODE
try {
  $stmt = $pdo->prepare($sql);
  $colors = ["Red", "Magenta", "Orange", "Yellow"];
  $stmt->execute(["Jane Doe", implode(",", $colors)]);
  echo "IMPLODE OK";
} catch (Exception $ex) { exit($ex->getMessage()); }
 
// (B3) SERIALIZE
try {
  $stmt = $pdo->prepare($sql);
  $colors = ["Red", "Blue", "Cyan", "Lime"];
  $stmt->execute(["Joy Doe", serialize($colors)]);
  echo "SERIALIZE OK";
} catch (Exception $ex) { exit($ex->getMessage()); }

This one should be pretty 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(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.

 

 

1C) RETRIEVE FROM DATABASE & CONVERT TO ARRAY

1c-retrieve.php
<?php
// (A) CONNECT TO DATABASE
require "0-database.php";
 
// (B) RETRIEVE ARRAY
$sql = "SELECT * FROM `people`";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
 
// (B1) JSON - JSON_DENCODE
$colors = json_decode($result[0]['fav_color']);
echo "JSON";
print_r($colors);
 
// (B2) EXPLODE
$colors = explode(",", $result[1]['fav_color']);
echo "EXPLODE";
print_r($colors);
 
// (B3) 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.

 

1D) ENCODED STRINGS ARE BAD FOR DATA MINING

1d-bad.php
<?php
// (A) CONNECT DATABASE
require "0-database.php";

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

// (C) 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; }
  }
}

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

When we have thousands of entries, this is 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.

 

 

JSON, IMPLODE, SERIALIZE – WHICH IS THE BEST?

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 recommend avoiding using implode() altogether.

 

METHOD 2) 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.

 

2A) 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`);

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

 

2B) STORING DATA

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

// (B) DUMMY DATA (USER ID => FAV COLORS)
$colors = [
  "1" => ["Red", "Green", "Blue"],
  "2" => ["Red", "Magenta", "Orange", "Yellow"],
  "3" => ["Red", "Blue", "Cyan", "Lime"]
];
 
// (C) STORE ARRAY
foreach ($colors as $id=>$col) {
  // (C1) FORMULATE SQL & DATA
  $sql = "INSERT INTO `fav_color` (`id`, `color`) VALUES ";
  $data = [];
  foreach ($col as $c) {
     $sql .= "(?,?),";
     $data[] = $id;
     $data[] = $c;
  }
  $sql = substr($sql, 0, -1) . ";";
 
  // (C2) INSERT
  try {
     $stmt = $pdo->prepare($sql);
     $stmt->execute($data);
     echo "OK - $id<br>";
  } catch (Exception $ex) { exit($ex->getMessage()); }
}

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 SQL entries one-by-one.

 

 

2C) RETRIEVING THE ARRAY

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

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

// (B1) ID 1 - JOHN DOE
$stmt->execute([1]);
$colors = $stmt->fetchAll();
echo "1 - JOHN DOE";
print_r($colors);
 
// (B2) ID 2 - JANE DOE
$stmt->execute([2]);
$colors = $stmt->fetchAll();
echo "2 - JANE DOE";
print_r($colors);
 
// (B3) ID 3 - JOY DOE
$stmt->execute([3]);
$colors = $stmt->fetchAll();
echo "3 - JOY DOE";
print_r($colors);
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.

 

2D) 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.

2d-popular-color.sql
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.

 

USEFUL BITS & LINKS

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

 

REFERENCES & LINKS

 

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!

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 *