Adding Up Colums with Specific Data and Display

[cloudsurferuk]

Hi guys,Ok so im slowly learning PHP but have come across a problem. I am trying to use a script to manage pilots flight hours and display their running totals on a list next to their respective ID's.

 

The script does work but for some reason when it gets to a certain level it maxes out at 839:59:59

 

For reference in the SQL database "reports" recieves and stores flight information including "duration" and duration is - TIME NOT NULL Default 00:00:00

 

Any help would be appreciated.

<?php

/* Constants */
@define ("MYSQL_CONNECT_INCLUDE", "connect_db.php");	


/* Database connection */
include(MYSQL_CONNECT_INCLUDE);

/* Select all pilots */
$query = "SELECT * FROM pilots ORDER BY pilot_num ASC";
$result = mysql_query($query);

/* Determine the number of pilots */
$number = mysql_numrows($result);

if ($number > 0) {
/* Print roster header 
print "<table>";
print "<tr>";
print "<td bgcolor=#000080 width=40 height=12 align=left><font face=Arial color=#FFFFFF size=1><b>ID</b></font></td>";
print "<td bgcolor=#000080 width=140 height=12 align=left><font face=Arial color=#FFFFFF size=1><b>NAME</b></font></td>";
print "<td bgcolor=#000080 width=73 height=12 align=left><font face=Arial color=#FFFFFF size=1><b>CITY</b></font></td>";
print "<td bgcolor=#000080 width=73 height=12 align=left><font face=Arial color=#FFFFFF size=1><b>COUNTRY</b></font></td>";
print "<td bgcolor=#000080 width=73 height=12 align=left><font face=Arial color=#FFFFFF size=1><b>FLIGHT TIME</b></font></td>";
print "<td bgcolor=#000080 width=50 height=12 align=left><font face=Arial color=#FFFFFF size=1><b>RANK</b></font></td>";
print "</tr>";

/* Get pilots info */
for ($i=0; $i<$number; $i++) {
     		$num = mysql_result($result,$i,"pilot_num");
     		$name = mysql_result($result,$i, "name");
     		$city = mysql_result($result,$i, "city");
     		$country = mysql_result($result,$i, "country");
     		$status = mysql_result($result,$i, "status");
	$id = mysql_result($result,$i, "pilot_id");

         	/* Calculate flight hours */
	$query_hours = "SELECT sec_to_time(sum(time_to_sec(t2.duration))) AS duration_sum FROM pilots t1, reports t2 WHERE t1.pilot_id=$id AND t1.pilot_id=t2.pilot_id";
    		$result_hours = mysql_query($query_hours);
    	
    		if (mysql_numrows($result_hours) > 0) {
		$time = mysql_result($result_hours,0,"duration_sum");
	}
         	
     		/* Display roster entries */
     		print "<tr>";
     		print "<td width=73 height=12 align=left><font face=Arial size=1 color=#000080>$num</font></td>";
     		print "<td width=73 height=12 align=left><font face=Arial size=1 color=#000080>$name</font></td>";
     		print "<td width=73 height=12 align=left><font face=Arial size=1 color=#000080>$city</font></td>";
     		print "<td width=73 height=12 align=left><font face=Arial size=1 color=#000080>$country</font></td>";
     		print "<td width=73 height=12 align=left><font face=Arial size=1 color=#000080>$time</font></td>";
     		print "<td width=73 height=12 align=left><font face=Arial size=1 color=#000080>$status</font></td>";
     		print "</tr>";
}

print "</table>";
}

/* Close the database connection */
mysql_close();
?>

[Goldeneye]

While I can't pinpoint your problem yet, I'm working on it -- I can give you some advice for better methods.

 

1) use mysql_num_rows (mysql_numrows has been deprecated and is not supported by newer versions of PHP)

 

2) instead of using mysql_result to tediously load the retrieved data into the script, use mysql_fetch_assoc or mysql_fetch_row.

$rows = mysql_fetch_row($result);
// to use the variables, you'd go $row['FIELD_NAME']
// or
list($var1, $var2, $var3, $var4 ...) = mysql_fetch_row($result);
// to use the data, you'd go $var1, etc.

 

 

EDIT: Also, I just remembered, MySQL TIME-fields cannot hold a value greater than 838:59:59 or less than -838:59:59. This means, you have to resort to a different field-type.

[cloudsurferuk]

Ah, so perhaps using an INT field would be better and using time in blocks such as 1hr30 = 1.5? (would that work in INT format?) and what would the sum be then to add that up to a total and display as directed? Sorry if this sounds basic but as stated, I am new to PHP but keen to learn.

[fenway]

You could store minutes.

[cloudsurferuk]

The problem with storing minutes is that it would then make a very complex sum as most reports are 2hrs plus and totals get over 1000hrs very quickly.

 

What I would like to know is if I store data as block time i.e 1hrs30mins would be stored as 1.5 then what sum would I use to give the same totals/display as in the code shown up there ^^ and should I store it as INT or VARCHAR?

 

[fenway]

How is a total over 1000 complicated for a machine?

[cloudsurferuk]

Ok so its probably more complex for me than the machine lol. I am trying to keep this as simple as possible as I am not a PHP or SQL expert.

 

The bottom line is I am trying to display a roster of pilots with a colum of total hours which takes the data from the reports table. When pilots file their reports they are used to doing it either as 2:00hrs or 2.0 hrs What i need to know is the simplest way of doing it.

[fenway]

Here's how you do it -- you present them via a drop-down menu of flying times -- in whatever hour increments are appropriate -- say, 0.5h, 1.0h, 1.5h, etc. -- and then you simply submit the value attribute of the option tag in minutes to your database.  It's that easy.