[SOLVED] PHP/MySQL Problem...

[hellouthere]

I have a script that basically displays data from 2 seperate mysql tables, it also performs an on-the-fly sum of all records of time (in the format 00:00) for each person in the database, i can assure you that all the table names are correct and the display shows all the information but gives an error for the on-the-fly calculation, the script is adapted from another script i have written but im not sure what ive missed... the script follows...

 

<?php

$hostName = "localhost";
$userName = "root";
$password = "london2012";
$dbName = "flkeeper";

$connect = @mysql_connect($hostName, $userName, $password);

$select = @mysql_select_db($dbName);


$query = "SELECT * FROM pilots";

$result = mysql_query($query);

$number = mysql_numrows($result);

if ($number > 0) {
print "<table width=925px border=1><tr bgcolor=#336699><td><div class=style1 align=center style=color:#ffffff><strong style=font-size:12px>Crew Roster</strong></div></td></tr></table>";
print "<table width=925px border=1><tr bgcolor=#336699><td width=174px><div align=center><strong><span style=color:#ffffff class=style1>Name</span></strong></div></td><td width=121px><div align=center><strong><span style=color:#ffffff class=style1>Pilot ID / <br> Callsign</span></strong></div></td><td width=59px><div align=center><strong><span style=color:#ffffff class=style1>HUB</span></strong></div></td><td width=90px><div align=center><strong><span style=color:#ffffff class=style1>Flight <br> Time</span></strong></div></td><td width=155px><div align=center><strong><span style=color:#ffffff class=style1>Awards</span></strong></div></td><td width=140px><div align=center><strong><span style=color:#ffffff class=style1>Last Flight</span></strong></div></td><td width=152px><div align=center><strong><span style=color:#ffffff class=style1>Status</span></strong></div></td></tr></table>";
print "<table width=925px border=1>";

for ($i=0; $i<$number; $i++) {
     		$name = mysql_result($result,$i, "realname");
	$id = mysql_result($result,$i, "ID");

	$query_hours = "SELECT sec_to_time(sum(time_to_sec(t2.BlockTime))) AS duration_sum FROM pilots t1, liveacars t2 WHERE t1.ID=$id AND t1.ID=t2.IDPilot";
    		$result_hours = mysql_query($query_hours);
    	
    		if (mysql_num_rows($result_hours) > 0) {
		$time = mysql_result($result_hours,0,"duration_sum");
	}

print "<tr>";
print "<td width=174px>$name</td>";
print "<td width=121px>$id</td>";
print "<td width=59px>hub</td>";
print "<td width=90px>$time</td>";
print "<td width=155px>awards</td>";
print "<td width=140px>last flight</td>";
print "<td width=152px>IVAO indicator</td>";
print "</tr>";
}
print "</table>";

print "<table width=925px border=1>";
print "<tr>";
print "<td bgcolor=#336699> </td>";
print "</tr>";
print "</table>";

}

mysql_close();
?>

 

Id be very grateful of a solution or even a push in the right direction, Thanks for your time...

[Barand]

I set up a similar pair of tables

[pre]

PILOT

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

CREATE TABLE `pilot` (

  `pilot_id` int(10) unsigned NOT NULL auto_increment,

  `pilot` varchar(45) NOT NULL,

  PRIMARY KEY  (`pilot_id`)

) ;

 

DATA

1, 'Fred'

2, 'Bill'

3, 'John'

 

 

PILOT_HOURS

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

CREATE TABLE `pilot_hours` (

  `id` int(10) unsigned NOT NULL auto_increment,

  `pilot_id` int(10) unsigned NOT NULL,

  `blocktime` time NOT NULL,

  PRIMARY KEY  (`id`)

) ;

 

DATA

1, 1, 01:30:00

2, 1, 02:00:00

3, 2, 00:30:00

4, 2, 00:45:00

5, 3, 01:20:00

6, 3, 00:50:00

[/pre]

 

Query

SELECT p.pilot, sec_to_time(SUM(time_to_sec(h.blocktime))) as hours
FROM pilot p INNER JOIN pilot_hours h ON p.pilot_id = h.pilot_id
GROUP BY p.pilot

 

OUTPUT

[pre]

Bill    01:15:00

Fred    03:30:00

John    02:10:00

[/pre]

[hellouthere]

thanks, i have tried to adapt it as the names are not exactly the same, still no joy, ill gve the table structure and updated query...

 

SELECT p.ID, sec_to_time(SUM(time_to_sec(h.blocktime))) AS hours
FROM pilots AS p INNER JOIN pirep AS h ON p.ID = h.IDPilot
GROUP BY p.ID

 

table `pirep`

 

IDPilot (callsign in format ABC123)

BlockTime (time format 00:00)

 

table `pilots`

 

IDPilot (callsign in format ABC123)

realname

 

i think this is all you need, thanks for your help, sorry if im missing something very obvious but havent found a problem yet...

 

[hellouthere]

the display table now adds up the total BlockTime for all ID's and displays the identical total on each name in the table...

 

also correction on the tables...

 

table `pilots`

 

ID (callsign in format ABC123)

realname

[Barand]

<?php

$sql = "SELECT p.realname, sec_to_time(SUM(time_to_sec(h.BlockTime))) AS hours
        FROM pilots AS p INNER JOIN pirep AS h ON p.ID = h.IDPilot
        GROUP BY p.realname" ;
$res = mysql_query($sql) or die (mysql_error().'<p>$sql</p>');
while (list($name, $hrs) = mysql_fetch_row($res)) {
    echo "$name, $hrs <br/>";
}

?>

[hellouthere]

ok, that gives a good output, could you please explain the piece of code after the query, i would like to know exactly why it works if i have to manipulate it... Thanks so much for your time...

 

EDIT: i cant see how it will fit into the table display... the display needs to show the names of all the pilots whether they have hours or not...

 

[Barand]

list() takes the elements from an array and puts them into the variables. So

 

list ($a, $b, $c) = array(1,2,3);

 

puts 1 into $a, 2 into $b and 3 into $c

 

mysql_fetch_row($res) returns an array containing the selected field values, for example

 

array (

    0 => 'John Doe',

    1 => 20:35

)

 

list ($name, $hrs) = mysql_fetch_row($res) will put the first element of the array into $name and the second into $hrs.

 

I just find it easier to format output this way rather than using $row[0]  (or $row['realname'] if mysql_fetch_assoc() is used).

[hellouthere]

for ($i=0; $i<$number; $i++) {
	$id = mysql_result($result,$i, "ID");

	$sql = "SELECT p.realname, sec_to_time(SUM(time_to_sec(h.BlockTime))) AS hours
        FROM pilots AS p INNER JOIN pirep AS h ON p.ID = h.IDPilot
        GROUP BY p.realname" ;
$res = mysql_query($sql) or die (mysql_error().'<p>$sql</p>');

   while (list($name, $hrs) = mysql_fetch_row($res)) {
  



print "<tr>";
print "<td width=174px>$name</td>";
print "<td width=121px>$id</td>";
print "<td width=59px>hub</td>";
print "<td width=90px>$hrs</td>";
print "<td width=155px>awards</td>";
print "<td width=140px>last flight</td>";
print "<td width=152px>IVAO indicator</td>";
print "</tr>";
}

 

this code rerturns

 

Dave Giffney UKC001 hub 10:57:00 awards last flight IVAO indicator

Wayne Basson UKC001 hub 01:15:00 awards last flight IVAO indicator

Dave Giffney UKC002 hub 10:57:00 awards last flight IVAO indicator

Wayne Basson UKC002 hub 01:15:00 awards last flight IVAO indicator

Dave Giffney UKC003 hub 10:57:00 awards last flight IVAO indicator

Wayne Basson UKC003 hub 01:15:00 awards last flight IVAO indicator

Dave Giffney UKC010 hub 10:57:00 awards last flight IVAO indicator

Wayne Basson UKC010 hub 01:15:00 awards last flight IVAO indicator

Dave Giffney UKC011 hub 10:57:00 awards last flight IVAO indicator

Wayne Basson UKC011 hub 01:15:00 awards last flight IVAO indicator

Dave Giffney UKC012 hub 10:57:00 awards last flight IVAO indicator

Wayne Basson UKC012 hub 01:15:00 awards last flight IVAO indicator

 

The names abovve are the only people with hours, it needs to cater for people with no hours... im not sure howd id go about this...

 

[Barand]

Use LEFT JOIN instead of INNER JOIN. Them the  pilots are selected even when there is no matching pirep record

[hellouthere]

all is going well, worked so far... thanks for your help... a true legend