Page 1 of 1

Need help for a Pilot LogBook site!

Posted: Fri Feb 11, 2011 12:14 am
by pilotlog
Hi Guys
I hope you can help me with this one, and I will try to explain myself best as I can.
I'm building a Pilot Logbook website. I use PHP and MySQL.
DB name: logbook
Table name: air_flight_log
Columns: DEPARTURE_DATE, BLOCK_TIME_START, BLOCK_TIME_END, TOTAL_BLOCKTIME.

1. I need to calculate the different between BLOCK_TIME_START - BLOCK_TIME_END and display the time on the "TOTAL_BLOCKTIME" as HH:MM.
2. Need to make sure to calculate the right time if I enter 23:00 - 01:00 the day after.
3. I need to do a Record count for the whole "TOTAL_BLOCKTIME" columns as HH:MM.
4. What "Type" shell I use for the BLOCK_TIME_START, BLOCK_TIME_END, TOTAL_BLOCKTIME in my Data Base, a TIME or ENUM?

I really hope you can help me to solve this as simple as possible, because I'm not a developer but I have some idea.

P.S
I use Dreamweaver CS5!

Cheers. Your Pilot :)

Re: Need help for a Pilot LogBook site!

Posted: Fri Feb 11, 2011 8:16 am
by divedj
Having a few ideas how to go about your problem, could you specify a little more what you are doing with this log. Is it only for one pilot or for a group of users. Even if it's only for one pilot we would need to define a primary key. For one pilot the primary could be a combination of start date and block time start. For a group of users there need to be also a identifyer for each pilot.
Next thing to know would be where the table entries are coming from. Are this variables passed on by another script or from a online form.

Mean while have a look at the unix time format which just counts the elapsed time in seconds since 1979 I think. PHP provides numerous functions to convert strings in to unix time and unix time to date and time strings. This makes it very easy to calculate flight time, working hours etc.
You have to decide in which format you would like to save your data in the table. If the priority is viewing the data than save it as string (VARCHAR). If priority is doing calculations save it as unix time and this is a number (INT)
Small example to work with the data:

Code: Select all

//using the function string to time strtotime() to convert a string variable to unix time
$block_time_end = strtotime("now");  //assuming the end time and date is right now
$start = strtotime("d-m-Y H:i:s", $block_time_start) ; //assuming $start is holding the starttime as string
$blocktime = $block_time_end - $start; //calculating the blocktime by substracting endtime from starttime
echo date("H:i:s", $blocktime); //Showing the result in hours, minutes and seconds
Hope that gives you a start to work things out.

Re: Need help for a Pilot LogBook site!

Posted: Fri Feb 11, 2011 8:44 am
by pilotlog
Hi D.

Thanks for you getting back to me!
Yes, I want to make this website for every body (Pilots), for both FAA and EASA.
Ok, this is how my index file looks like. But this is only for displaying the information (I have some data in my DB which I have imported):

<?php require_once('../Connections/logbook.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

mysql_select_db($database_logbook, $logbook);
$query_rsMain = "SELECT * FROM air_flight_log ORDER BY DEPARTURE_DATE ASC";
$rsMain = mysql_query($query_rsMain, $logbook) or die(mysql_error());
$row_rsMain = mysql_fetch_assoc($rsMain);
$totalRows_rsMain = mysql_num_rows($rsMain);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<table border="0">
<tr>
<td>FLIGHT_ID</td>
<td>Date (dd/mm/yy)</td>
<td>DEP (place)</td>
<td>OFF BLOCK (UTC)</td>
<td>ARR (place)</td>
<td>ON BLOCK (UTC)</td>
<td>TYPE AIRCRAFT OR SYNTHETIC TRANINF DEV.</td>
<td>REGISTRATION</td>
<td>NAME OF PILOT IN COMMAND</td>
<td>TOTAL</td>
<td>NIGHT</td>
<td>SINGLE ENGINE VFR</td>
<td>SINGLE ENGINE IFR</td>
<td>MULTI_ENGINE VFR</td>
<td>MULTI_ENGINE IFR</td>
<td>PILOT IN COMMAND</td>
<td>CO-PILOT</td>
<td>MULTI PILOT</td>
<td>FLIGHT INSTRUCTOR</td>
<td>DUAL (transferred from student appendix)</td>
<td>INSTRUCTOR SYNTHETIC TRAINING DEVICE</td>
<td colspan="2">LANDING</td>
<td>REMARKS AND ENDORSEMENTS</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_rsMain['FLIGHT_ID']; ?></td>
<td><?php echo $row_rsMain['DEPARTURE_DATE']; ?></td>
<td><?php echo $row_rsMain['PLACE_OFF']; ?></td>
<td><?php echo $row_rsMain['BLOCK_TIME_START']; ?></td>
<td><?php echo $row_rsMain['PLACE_LANDING']; ?></td>
<td><?php echo $row_rsMain['BLOCK_TIME_END']; ?></td>
<td><?php echo $row_rsMain['AIRCRAFT_TYPE']; ?></td>
<td><?php echo $row_rsMain['AIRCRAFT_CODE']; ?></td>
<td><?php echo $row_rsMain['CREWMEMBER_NAME']; ?></td>
<td><?php echo $row_rsMain['TOTAL_BLOCKTIME']; ?></td>
<td><?php echo $row_rsMain['LANDING_COUNT_NIGHT']; ?></td>
<td><?php echo $row_rsMain['SINGLE_ENGINE_VFR']; ?></td>
<td><?php echo $row_rsMain['SINGLE_ENGINE_IFR']; ?></td>
<td><?php echo $row_rsMain['MULTI_ENGINE_VFR']; ?></td>
<td><?php echo $row_rsMain['MULTI_ENGINE_IFR']; ?></td>
<td><?php echo $row_rsMain['PILOT_IN_COMMAND']; ?></td>
<td><?php echo $row_rsMain['SECOND_PILOT']; ?></td>
<td><?php echo $row_rsMain['MULTI_PILOT']; ?></td>
<td><?php echo $row_rsMain['INSTRUCTOR']; ?></td>
<td><?php echo $row_rsMain['DUAL_FLIGHT_TIME']; ?></td>
<td><?php echo $row_rsMain['SIMULATOR']; ?></td>
<td><?php echo $row_rsMain['LANDING_COUNT_DAY']; ?></td>
<td><?php echo $row_rsMain['LANDING_COUNT_NIGHT']; ?></td>
<td><?php echo $row_rsMain['REMARK']; ?></td>
</tr>

<?php } while ($row_rsMain = mysql_fetch_assoc($rsMain)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($rsMain);
?>

Re: Need help for a Pilot LogBook site!

Posted: Fri Feb 11, 2011 3:31 pm
by litebearer
Just an observation:

Might make arrival and departure table field datetime.
No need of fields that are simple calculations of other fields
An example of the following rough script (http://www.nstoia.com/sat/photostudio/pilot2.php )

Code: Select all

<?PHP
/* connect to db */
include("db.php");
/* decide which pilot */
$pid = 1;
/* create query */
$query = "SELECT *, time_to_sec(TIMEDIFF(arrive, depart)) as trip  FROM flightlog where pid = '$pid' ORDER BY depart";
/* excute query */
$result = mysql_query($query);
/* display the results */
while($row = mysql_fetch_array($result)) {
	$secs = $row['trip'];
	$mins = floor($secs/60);
	$hours = floor($mins/60);
	$mins = $mins - ($hours * 60);
	echo "Departure time: " . date("F d, Y - H:i", strtotime($row['depart']) ). " - Arrival time: " . date("F d, Y - H:i", strtotime($row['arrive']) ) . " - flight-time: " . $hours . " hours " . $mins . " minutes <hr>";
}

Re: Need help for a Pilot LogBook site!

Posted: Sat Feb 12, 2011 9:09 am
by divedj
Doing the calculation inside mysql is a great approach. Didn't think of it.