Designing System for Gate Displays (Final)

Hi there, ready for the next step? Alright! ^^

If you have not read the previous step, click here for the step to create the database for the system. Now, after we knew the flow of the system, create the base for the data, and here we go.

What should we do first? Create the user interface for the ATC or create the gate displays first? Better we create the gate displays first. Look at the code explained below.

[php]
//Create the header of the displays,
//here we placed no title for the displays
print("
<table cellspacing="0" cellpadding="0" border="1">
<tr>
<td width="20px">&nbsp;</td>
<td width="70px">Flight</td>
<td width="130px">Departure</td>
<td width="70px">Time</td>
<td width="130px">Status</td>
</tr>");

//Make a connection between your MySQL and PHP,
//change the ‘MYSQL_DB_SERVER/USER/PASSWORD/DATABASE
//based on your server setting
$connection = mysql_connect(MYSQL_DB_SERVER, MYSQL_DB_USER, MYSQL_DB_PASSWORD);
$database = mysql_select_db("MYSQL_DATABASE", $connection);

//Get the data from the database and execute it
$getdata = "SELECT flight, departure, time, status " .
"FROM flight ORDER BY time ASC, flight ASC, departure ASC LIMIT 0, 10";
$execute = mysql_query($getdata, $connection);

//Take it into a variable and show it
while($result = mysql_fetch_array($execute)) {
$Flight = $result[0];
$Departure = $result[1];
$Time = $result[2];
$Status = $result[3];

print("
<tr>
<td>&nbsp;</td>
<td>$Flight</td>
<td>$Departure</td>
<td>$Time</td>
<td>$Status</td>
</tr>");
}
[/php]

Taadaa, and the Gate Displays ready to use ^^

Now, let’s make the user interface. The code is similarly to the code above, just need to adding some line. Have a look at figure below.

[php]
print("
<table cellspacing="0" cellpadding="0" border="1">
<tr>
<td width="20px">&nbsp;</td>
<td width="70px">Flight</td>
<td width="130px">Departure</td>
<td width="70px">Time</td>
<td width="130px">Status</td>
<td width="100px">&nbsp;</td> //new line added
</tr>");

$connection = mysql_connect(MYSQL_DB_SERVER, MYSQL_DB_USER, MYSQL_DB_PASSWORD);
$database = mysql_select_db("MYSQL_DATABASE", $connection);

$getdata = "SELECT flight, departure, time, status " .
"FROM flight ORDER BY time ASC, flight ASC, departure ASC LIMIT 0, 10";
$execute = mysql_query($getdata, $connection);

while($result = mysql_fetch_array($execute)) {
$Flight = $result[0];
$Departure = $result[1];
$Time = $result[2];
$Status = $result[3];

print("
<tr>
<td>&nbsp;</td>
<td>$Flight</td>
<td>$Departure</td>
<td>$Time</td>
<td>$Status</td>
<td><a href="changestatus.php?flight=$Flight">Change Status</a></td> //new line added
</tr>");
}
[/php]

Now, the view should be like this:

Look at the last line we’ve added, there is a link to ‘changestatus.php’, right? Now, let’s we make a new .php file named ‘changestatus’. We are going to using a variable passing technique called Query String. We will passing the value of the flight number to the ‘changestatus.php’ by using an addition syntax after the URL parameter, ‘?flight=$Flight’. This kind of technique passing the value from the origin file to the file that point by the URL parameter.

[php]
//Save the passing value to the new variable
$Flight = $_GET["flight"];

$connection = mysql_connect(MYSQL_DB_SERVER, MYSQL_DB_USER, MYSQL_DB_PASSWORD);
$database = mysql_select_db("MYSQL_DATABASE", $connection);

//Get the data based on passing value,
//in this case the passing value is $Flight.
//Note that $Flight is not passing value anymore,
//because it has saved in a new variable (see top code)
$getdata = "SELECT flight, status FROM flight WHERE flight = ‘$Flight’";
$execute = mysql_query($getdata, $connection);

//Show it
while($result = mysql_fetch_array($execute)) {
$Flight = $result[0];
$Status = $result[1];

print("$Flight, status: $Status");
}

//Now make the option to change the value, and send it
//pass through the URL parameter again
//Don’t forget to make a new file, named ‘dochangestatus.php’
printf("
Change status:<br>
<a href="dochangestatus.php?flight=$Flight&status=delayed">Delayed</a>&nbsp;|&nbsp;
<a href="dochangestatus.php?flight=$Flight&status=landed">Landed</a>
")
[/php]

Now, we create the ‘dochangestatus.php’ file to execute the change.

[php]
//Save the passing value to the new variable
$Flight = $_GET["flight"];
$Status = $_GET["status"];

$connection = mysql_connect(MYSQL_DB_SERVER, MYSQL_DB_USER, MYSQL_DB_PASSWORD);
$database = mysql_select_db("MYSQL_DATABASE", $connection);

$upddata = "UPDATE flight SET status = ‘$Status’ WHERE flight = ‘$Flight’";
$execute = mysql_query($upddata, $connection);

//If the query failed to execute, then the system
//will show that the operation is failed,
//if succeed, direct to the display gate file
if (empty($execute)) print("Status change failed");
else header("Location: index.php");
[/php]

Yay! Now let’s just take an experiment into its, just take one of the flight and click the ‘change status’ link, and click the ‘delayed’ or ‘landed’, and taadaa!! You’ll get this view:

You can add some line again to delete the flight after the plane landed. But, in reality, they didn’t delete the flight, but they adding a database called ‘flight history’ and store the flight schedule to the database. It’s a kind of OLAP, Online Analytical Data Processing. ^^

Please let me know if there is any question or comment that popping into your mind, or there is any idea that you want me to show up, it will be nice. But I will do it with my best and can’t promise that I can cover it all. Thanks for Micha that have this idea and let me know and I can realized her idea for all of you to get inspire. Thank you for reading.

Designing System for Gate Displays (Part I)

Hi there! Today we’re gonna to create a database for the system I’ve explained before. At the main-figure (Data flow for the system), there are two database, one refer to the airlines, and the other refer to the airport, click here to see the figure. Here, I just require to create one database refer to the airport.

If you do not have any imagine what we are going to make, just open ICCI Web. But we will not use all of variables exist there. The variables we are going to use are: Flight No., From, Time, and Status.

Well, let’s do it!

Firstly, create a new database, named ‘gatedisplays’, or others name you want to make. Or, you can just create the new table for it. Then, create a table named ‘flight’. Finish? Welldone… Then be ready for the next step. What is the next step? Alright, we are gonna create all of the variables we need. The first variable we are going to create is, ‘Flight’, then ‘From’, ‘Time’, and the last, ‘Status’. Look at the figure below.

Here is the query of the MySQL:
[php]
CREATE TABLE gatedisplays.flight (
flight VARCHAR( 7 ) NOT NULL ,
departure VARCHAR( 30 ) NOT NULL ,
time TIME NOT NULL ,
status VARCHAR( 12 ) NOT NULL ,
PRIMARY KEY ( flight )
) ENGINE = INNODB;
[/php]

Now what we have to do is, adding some data to the table to make it useful. Add 10 up to 20 data to the table as you like. Let’s get an example for it.

Ow, I forget to tell you something. As far as the flight schedule not in the range of the Gate Display, it should be an default value to the status, there is ‘unconfirmed’. That’s just my estimate. The ATC will have an authorization to change it.

Well done, in the next step we will have to do make a simple user interface for ATC to change the status while the plane is approaching the airport. And finally, make the Gate Display! ^^ . Thanks for reading.

— to be continued —

Designing System for Gate Displays (Intro Part)

It’s easy but hard, and it’s hard but easy. That’s pretty confusing statement that occurs while I am decided to make a simple system for Gate Display (GD) that usually placed at airport or others transportation’s terminal. It is one of my interest to involved with a company that create a system for airlines/airport.

Data Flow for the System
Gate Displays' Diagram

The database of the airport should be collect the database from airlines, that contain flight schedule to and from the airport. Then, the GD will display the most recent flight at the airport with status that could be change depend on the flight condition, like ‘boarding’, ‘on time’, ‘delayed’, etc. The ATC (Air Traffic Control) will have authorized to change it. Just a simple flow to develop the system here, but it will be harder to develop the real system. But, that is the chance! I will be ready to engaged with. ^^

Keep in touch with Jimzland.com! 🙂

… to be continue – Part I-Make a Database for Airlines’ Flight Schedule…

PHP Tutorial: 10 ways to make a traveler timetable

Hi there, today I wanna share something about PHP. And it’s about, how to joining the redundant header-text become one, like often used in timetable, usually in airlines for the flight or ship schedule. Look at the figure below:
timetable

Firstly, let’s make a database. Note the figure above, there are 6 variables, that is: Departure Place, Arrival Place, Frequency, Departure Time, Arrival Time, and Flight Number. Execute query below on your database (use your phpmyadmin):
[php]
CREATE TABLE IF NOT EXISTS timetbl (
FlightNo varchar(10) NOT NULL,
Departure varchar(20) NOT NULL,
Arrival varchar(20) NOT NULL,
DepTime varchar(5) NOT NULL,
ArrTime varchar(5) NOT NULL,
Frequency varchar(7) NOT NULL,
PRIMARY KEY (FlightNo),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
[/php]

Insert some schedule to the database (look at the figure). Then, here we go! ^^

Get all of your data from ‘timetbl’ table, try this code:
[php]
print("<table cellspacing="0" cellpadding="0" border="1">
<tr>
<td width="20px">&nbsp;</td>
<td width="50px">Frequency</td>
<td width="60px">Dep. Time</td>
<td width="60px">Arr. Time</td>
<td width="100px">Flight No.</td>
</tr>");

$connection = mysql_connect(MYSQL_DB_SERVER, MYSQL_DB_USER, MYSQL_DB_PASSWORD);
$database = mysql_select_db("MYSQL_DATABASE", $connection);

//2. getting data from the ‘timetbl’ table
// Note that it must order by Departure and Arrival,
// in order to make the algorithm more simple.
$getdata = "SELECT FlightNo, Departure, Arrival, DepTime, ArrTime, Frequency " .
"FROM timetbl ORDER BY Departure ASC, Arrival ASC, DepTime ASC";
$execute = mysql_query($getdata, $connection);

//3. create a counter for the data
$count = "0";

//4. move it to temporary variable
while($result = mysql_fetch_array($execute)) {
//5. Add the value of the counter as the data have been added
$count++;

//The sequence of the number 0-5 accordance
//with the getdata query sequence above
$FlightNo = $result[0];
$Departure = $result[1];
$Arrival = $result[2];
$DepTime = $result[3];
$ArrTime = $result[4];
$Frequency = $result[5];

//6. Create a mark to show there is no redundant
// data for departure have been showed before
if($count == ‘1’) {
$dep_temp = $Departure;
$dep_prev = "";
} else {
$dep_prev = $dep_temp;
$dep_temp = $Departure;
}
//7. Print the departure information
if($Departure != $dep_prev) {
$count_dep = "0";
print("<tr><td colspan="5">$Departure</td></tr>");
} else $count_dep++;

//8. Create a mark to show there is no redundant
// data for arrival have been showed before
if($count == ‘1’ || $count_dep == "0") {
$arr_temp = $Arrival;
$arr_prev = "";
} else {
$arr_prev = $arr_temp;
$arr_temp = $Arrival;
}
//9. Print the arrival information
if($Arrival != $arr_temp || $Arrival != $arr_prev) {
print("<tr><td>&nbsp;</td><td colspan="4">$Arrival</td></tr>");
}

//10. Show the supporting information, Frequency, Departure Time,
// Arrival Time, and Flight Number
print("<tr><td>&nbsp;</td><td>$Frequency</td><td>$DepTime</td><td>$ArrTime</td><td>$FlightNo</td></tr>");
}

print("</table>");
[/php]

Don’f forget to change MYSQL_DB_SERVER, MYSQL_DB_USER, MYSQL_DB_PASSWORD, MYSQL_DATABASE with yours configuration, e.g. (sequentially) 127.0.0.1, admin, admin, mydatabase. Or, you can create a new file, named config.php to separate the configuration from your page. You can add css file to the page. ^^

Finally, there is 10 ways to make a better timetable. If you have any questions, do not hesitate to comment on the box provide below or send me an e-mail. Okey? Hopefully this article useful. Cu next time in the next code.