Very Simple PHP MYSQL JSON Examples (A Beginner’s Guide)

Welcome to a beginner’s tutorial and examples of JSON in PHP and MYSQL. So you have probably heard of this “JSON” thing somewhere and wondering what it is for?

  • JSON stands for Javascript Object Notation.
  • In simple terms, we use JSON to convert an array into a string and vice-versa.
    • JSON encode – Turn an array into a string.
    • JSON decode – Turn a JSON encoded string back into an array.
  • The usage of JSON is very wide.
    • To exchange an array between Javascript and PHP.
    • To exchange an array between server-to-server calls.
    • Store an array as a JSON encoded string in the database.

That covers the basics, but let us walk through more examples in this guide – Read on!

ⓘ I have included a zip file with all the example code at the end of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes PHP JSON Useful Bits & Links
The End

 

DOWNLOAD & NOTES

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

 

QUICK NOTES

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

 

 

PHP JSON EXAMPLES

All right, let us now get into the examples of using JSON in Javascript, PHP, and MySQL.

 

EXAMPLE 1) PHP JSON ENCODE-DECODE

1-simple-json.php
<?php
// (A) AN ARRAY
$person = [
  "name" => "Job Doe",
  "email" => "job@doe.com"
];
 
// (B) JSON ENCODE - ARRAY TO STRING
$person = json_encode($person);
echo $person; // STRING
 
// (C) JSON DECODE - STRING TO ARRAY
$person = json_decode($person, 1);
print_r($person); // ARRAY
  • json_encode() Turns an array into a string.
  • json_decode() Turns a JSON encoded string into an array.

 

EXAMPLE 2) JAVASCRIPT JSON ENCODE-DECODE

2-simple-json.html
// (A) AN ARRAY
var person = {
  name : "Jon Doe",
  email : "jon@doe.com"
};
 
// (B) JSON ENCODE - ARRAY TO STRING
person = JSON.stringify(person);
console.log(person); // STRING
 
// (C) JSON DECODE - STRING TO ARRAY
person = JSON.parse(person);
console.log(person); // ARRAY (OBJECT)
  • JSON.stringify() Turns an array into a string.
  • JSON.parse() Turns a JSON encoded string into an array.

 

 

EXAMPLE 3) SEND ARRAY FROM JAVASCRIPT TO PHP

3A) JAVASCRIPT – ENCODE ARRAY TO STRING

3a-send.html
// (A) JSON ENCODE ARRAY TO STRING
var person = JSON.stringify({
  name : "Jon Doe",
  email : "jon@doe.com"
});
 
// (B) APPEND JSON ENCODED PERSON TO DATA
var data = new FormData();
data.append("person", person);
 
// (C) SEND DATA TO SERVER
fetch("3b-receive.php", { "method":"POST", body:data })
.then(res => res.text())
.then((txt) => { console.log(txt); });

What’s the big deal with JSON? With the default POST or GET, it is impossible to directly send an array or object across. This is where we use JSON.stringify(person) to turn it into a string, then send it over to the server.

 

3B) PHP – DECODE STRING TO ARRAY

3b-receive.php
<?php
// (A) $_POST["PERSON"] IS A STRING
echo $_POST["person"];
 
// (B) JSON DECODE TO GET BACK AS ARRAY
$person = json_decode($_POST["person"], true);
print_r($person);

This should be pretty self-explanatory. On the server-side, we simply use json_decode($_POST["person"]) to turn the string back into an array.

 

 

EXAMPLE 4) SEND ARRAY FROM SERVER-TO-SERVER

4-send.php
<?php
// (A) JSON ENCODE - ARRAY TO STRING
$person = json_encode([
  "name" => "Jane Doe",
  "email" => "jane@doe.com"
]);

// (B) SEND TO "OTHER SERVER"
$url = "http://localhost/3b-receive.php"; // CHANGE URL TO YOUR OWN
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, ["person" => $person]);
$result = curl_exec($ch);
curl_close($ch);
print_r($result);

Captain Obvious to the rescue, we can also use JSON in a server-to-server call.

  1. There is a $person array.
  2. It is impossible to directly send this to another server, so we json_encode($person) to turn it into a string first.
  3. Finally, use CURL to send it out.

 

EXAMPLE 5) STORE JSON WITH MYSQL

5A) DUMMY DATABASE TABLE

3a-dummy.sql
CREATE TABLE `dummy` (
  `id` int(11) NOT NULL,
  `name` text NOT NULL,
  `fav` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `dummy`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `dummy`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

For this last example, we will be working with JSON and MySQL – Start by creating this dummy table. Only 3 fields here :

  • id Primary key, just a running number.
  • name Name of person.
  • fav An array of the person’s favorite stuff.

 

 

5B) INSERT-RETRIEVE JSON FROM DATABASE

5b-database.php
<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");
$pdo = new PDO(
  "mysql:host=". DB_HOST .";charset=". DB_CHARSET .";dbname=". DB_NAME,
  DB_USER, DB_PASSWORD
);

// (B) SAVE
$stmt = $pdo->prepare("REPLACE INTO `dummy` (`id`, `name`, `fav`) VALUES (?,?,?)");
$id = 1;
$name = "Joy Doe";
$fav = json_encode(["Red", "Green", "Blue"]); // JSON ENCODE TO A STRING
echo $stmt->execute([$id, $name, $fav]) ? "OK" : "ERROR";

// (C) FETCH
$stmt = $pdo->prepare("SELECT * FROM `dummy` WHERE `id`=?");
$stmt->execute([$id]);
$result = $stmt->fetch();
$fav = json_decode($result["fav"]); // JSON DECODE TO GET BACK ARRAY
print_r($fav);

Remember that json_encode() produces a string? Yes, we can JSON encode an array, then store the string into the database as-it-is.

 

USEFUL BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

LINKS & REFERENCES

 

INFOGRAPHIC CHEATSHEET

Javascript PHP JSON Example (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 you to better understand JSON, and if you have anything to share 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 *