What way can JSON be generated like this?

I am curious, in what way can JSON be generated like this?
https://www.packtpub.com/sites/default/files/Article-Images/2749-04-01.png

Let’s say there’s a database (MySQL, InnoDB), and we have a few tables, and we execute a query, and we pull that information into an array, is there a way to make it structured like the above.

E.g. Passangers is a table, and from the screenshot, we can see that it holds an array with the table contents.

Try this. It may not be what you are looking for, but it’s some what the same as the example you shown in the image.

<?php
$array = [
    'origin' => 'Dehli',
    'destination' => 'Logon',
    'passengers' => [
        [
            'name' => 'Mr. Perry',
            'type' => 'Adult',
            'age' => 28
        ],
        [
            'name' => 'Miss Irene Adler',
            'type' => 'Adult',
            'age' => 25
        ]
    ],
    'travelDate' => '17-Dec-2010'
];

$json = json_encode($array);

The json result should give you something similar to this.

{
   "origin": "Dehli",
   "destination": "Logon",
   "passengers": [
      {
         "name": "Mr. Perry",
         "type": "Adult",
         "age": 28
      },
      {
         "name": "Miss Irene Adler",
         "type": "Adult",
         "age": 25
      }
   ],
   "travelDate": "17-Dec-2010"
}

Then just do a json_decode like this.

$data = json_decode($json);
print_r($data);

And it should give you the final result which looks like this.

1 Like

What about dynamically generating this from the DB result? E.g. manipulating the result before json_encoding and outputting it through echo or print.

Yes, it’s possible. It took me the whole night of attempting to output the entire criteria, but it seems like I finally got it. The problem that I had was trying to get the passengers to output the right way. It kept outputting only 1 passenger when there were multiple passengers.

Here is the snippet I came up with. It’s not the best, but it outputs what you want. Also, assuming you are using PDO.

<?php
define('TYPE', 'mysql'); // The data type. PDO requires this.
define('HOST', '127.0.0.1'); // The database host, can be localhost, localhost.com, or even the IP Address 127.0.0.1
define('USERNAME', 'root'); // The database username
define('PASSWORD', 'root'); // The database password
define('DATABASE', 'test'); // The database

// Create a new connection with PDO
// Define the default fetch mode in the options, we will define FETCH_OBJ because it is what is being shown in the image.
// We also will define an error mode. This will send all our error messages to our error log instead of to the screen.
$options = [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING];
$db = new PDO(TYPE . ':host=' . HOST . ';dbname=' . DATABASE, USERNAME, PASSWORD, $options); // Append our basic database information to the new connection

$sql = "SELECT id, origin, destination, travelDate FROM flight LIMIT 1"; // The SQL query string. Take note of the LIMIT 1. This will come in handy later on.
$prepare = $db->prepare($sql); // Prepare the SQL query string
$prepare->execute(); // Execute the prepared SQL query string

// Check to see if there are any rows yet
if($prepare->rowCount()) {

    // Append a $row variable to the while loop so we can use the specified variables when we want
    while($row = $prepare->fetch(PDO::FETCH_ASSOC)) {

        // Create a new SELECT query string
        $sql = "SELECT name, type, age FROM passengers WHERE flight_id = :flight_id"; // The second SQL query string. Take not this one doesn't have LIMIT 1.
        $prepare = $db->prepare($sql); // Prepare the second SQL query string
        $parameters = [':flight_id' => $row['id']]; // Append the binded parameter to an array along with $row['id']
        $prepare->execute($parameters); // Execute the binded parameter

        // Check to see if there are any rows in the passengers table with the specified flight ID
        if($prepare->rowCount()) {

            $passengers = $prepare->fetchAll(); // Append the $passengers variable to fetchAll().
            // If specified with a default fetch mode of FETCH_OBJ, you'll be seeing stdClass objects instead of associative arrays.

        } else {

            die('No passengers in this flight'); // There are no passengers in this flight so die with a message and ignore everything after this.

        }

        // Append an $array variable to an array.
        // This is where we will be appending our results.
        // Remember when I said to take note of the LIMIT 1 earlier?
        // Well, if you don't use LIMIT 1 on the first SELECT, the results down here will loop multiple times depending on how many passengers you have booked.
        // It will actually output more than 1 final result
        $array = [
            'origin' => $row['origin'], // Append an origin key with the variable $row['origin']
            'destination' => $row['destination'], // Append a destination key with the variable $row['destination']
            'passengers' => $passengers, // Append a passengers key with the variable $passengers, this will most likely be an stdClass object or an arrays
            'travelDate' => $row['travelDate'] // Append a travelDate key with the variable $row['travelDate']
        ];

        $json = json_encode($array); // Append the array variable to the json_encode and then append the json variable to the json_encode
        $data = json_decode($json); // Append the json variable to json_decode and then append the data variable to the json_decode

        print_r($data); // Print the final results

    }

} else {

    print('There are no flights right now'); // There are no flights in your database

}

Here is the SQL data.

NOTE


You might want to change the data type from text to something else. I was just in a hurry because I have no sleep right now.

-- phpMyAdmin SQL Dump
-- version 4.4.13.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 17, 2016 at 08:44 AM
-- Server version: 5.6.27-0ubuntu1
-- PHP Version: 5.6.11-1ubuntu3.1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `flight`
--

CREATE TABLE IF NOT EXISTS `flight` (
  `id` int(11) NOT NULL,
  `origin` text NOT NULL,
  `destination` text NOT NULL,
  `travelDate` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `flight`
--

INSERT INTO `flight` (`id`, `origin`, `destination`, `travelDate`) VALUES
(1, 'Dehli', 'London', '17-Dec-2010');

-- --------------------------------------------------------

--
-- Table structure for table `passengers`
--

CREATE TABLE IF NOT EXISTS `passengers` (
  `id` int(11) NOT NULL,
  `name` text NOT NULL,
  `type` text NOT NULL,
  `age` int(11) NOT NULL,
  `flight_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `passengers`
--

INSERT INTO `passengers` (`id`, `name`, `type`, `age`, `flight_id`) VALUES
(1, 'Mr. Perry', 'Adult', 28, 1),
(2, 'Miss Irene Adler', 'Adult', 25, 1);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `flight`
--
ALTER TABLE `flight`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `passengers`
--
ALTER TABLE `passengers`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `flight`
--
ALTER TABLE `flight`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `passengers`
--
ALTER TABLE `passengers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;

The only reason why we are using LIMIT 1 in this snippet is because if we don’t, it’ll output the same results more than 1 time and that’s not what we want. If you are passing the $_GET variable to this page, you can actually append it to where the LIMIT 1 is. This is only if you want a specific flight and who the passengers are.

Let me know if it’s not what you want. I’m just afraid you might want multiple flights on the same page with their respective passengers. That’s going to take a lot more time to do, but someone can help you out more with that.

1 Like
while($row = $prepare->fetch(PDO::FETCH_ASSOC)) {

        // Create a new SELECT query string
        $sql = "SELECT name, type, age FROM passengers WHERE flight_id = :flight_id"; // The second SQL query string. Take not this one doesn't have LIMIT 1.

Is it good to run a mysql query inside loop? AFAIK, I’ve heard that this is very bad.

Yes and I’m sure there is a better way to do this. I just did it the more complex way.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.