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.