[RESOLVED] 2 problems?

[homer09001]

1) the following query:

SELECT * FROM sa_pireps WHERE PilotID=0002 ORDER BY DateTime ASC

works fine in phpmyadmin and retrieves the 2 rows in the table, yet it doesnt work in my code?

2) i store records in my database regardin time, it is stored as minutes, how can i get it to convert back to hours and minutes? ive tried using /60 after the variable but it makes:

2:75 hours out of what should be 2:45

[homer09001]

solved the minutes to hours problem found a code snippet on the net.

[NogDog]

Pretty tough to tell why it does not work in your PHP script without seeing your PHP code. For debugging, I'd suggest you try something like this:

$query = "SELECT blah blah blah";
$result = mysql_query($query) or die("Query failed: ($query): " . mysql_error());
// start debug:
if(mysql_num_rows($result) == 0)
{
   echo "<p style='color:red'>No rows returned by query ($query)</p>\n";
}
// end debug

This way if things don't work, you can see the query as it was exactly sent to MySQL. Once things are working, you can comment out the debug code for the production version.

[homer09001]

echo '<br><table cellspacing="1" bordercolor=#000000 cellpadding="2" border="2">';
echo "<tr><td>Departure Time</td><td>Arrival Time</td><td>Departing From</td><td>Departing To</td><td>Aircraft Reg</td><td>Duration Of Flight (Hours)</td><td>Flight Number</td></tr>";
$a = "SELECT * FROM $table3 WHERE PilotID=$pid ORDER BY DateTime ASC";
echo $a;
$b = mysql_query($a)or die(mysql_error());
while ($row21 = mysql_fetch_assoc($b)){

$id = $row21['ID'];
$totime = $row21['StartTime'];
$LndTime = $row21['Landime'];
$from = $row21['From'];
$to = $row21['To'];
$acrft = $row21['Aircraft'];
$Hours = $row21['TotalHours'];
$fltno = $row21['flightno'];
$dati = $row21['DateTime'];

$Hours = m2h($Hours);
$query2 = "SELECT AirportName FROM $table5 WHERE icao='$from'";
$result2 = mysql_query($query2)or die(mysql_error());
while ($row2 = mysql_fetch_assoc($result2)){
    $apname = $row2["AirportName"];

$query3 = "SELECT AirportName FROM $table5 WHERE icao='$to'";
$result3 = mysql_query($query3)or die(mysql_error());
while ($row3 = mysql_fetch_assoc($result3)){
    $apname2 = $row3["AirportName"];


echo "<tr><td>$totime</td><td>$LndTime</td><td>$apname</td><td>$apname2</td><td>$acrft</td><td>$Hours</td><td>$fltno</td></tr>";


}}}





// Set up the table, put little bits of HTML after the ? > if you want if you want.
echo "</table>";
echo "<br>Click <a href='index.php?page=roster'>Here</a> To Return To Roster";

thats the entire code sorry

there ive echoed the query and run the output in phpmyadmin and it works there are now 4rows in the database, ive done num_rows on the table with the query and it returns 4 rows?

[Weedpacket]

Nested loops with queries inside them. Nasty. If a PilotID returned eight records with different $from and $to values, there will be 1 + 8 + 8*8 = 73 database queries (of which 56 will be duplicates).

Something like

Select
	t1.StartTime as departure,  -- meaning that you'll have $row['departure']
	t1.LandTime as arrival,
	t2.AirportName as origin,
	t3.AirportName as destination,
	t1.Aircraft as aircraft,
	t1.TotalHours as hours,
	t1.flightno as flightno
From
	$table3 t1,
	$table5 t2,
	$table5 t3,
where
	t2.icao=t1.From
	and t3.icao=t1.To
	and t1.PilotID=$pid
Order by t1.DateTime ASC

And you get the work done with one query, one result set, and one loop.

[homer09001]

i get your point weedpacket but the code you've posted is confusing me? i don't understand how i get that into a working query and get it into variables?

[homer09001]

ahh hang on a sec does it go like so:

echo '<br><table cellspacing="1" bordercolor=#000000 cellpadding="2" border="2">';
echo "<tr><td>Departure Time</td><td>Arrival Time</td><td>Departing From</td><td>Departing To</td><td>Aircraft Reg</td><td>Duration Of Flight (Hours)</td><td>Flight Number</td></tr>";
$a = "
Select
	t1.ID as id,
	t1.StartTime as departure,
	t1.LandTime as arrival,
	t2.AirportName as origin,
	t3.AirportName as destination,
	t1.Aircraft as aircraft,
	t1.TotalHours as hours,
	t1.flightno as flightno,
	t1.datetime as DateTime
From
	$table3 t1,
	$table5 t2,
	$table5 t3,
Where
	t2.icao=t1.Frm
	and t3.icao=t1.Too
	and t1.PilotID=$pid
Order by t1.DateTime ASC
";
//echo $a;
$b = mysql_query($a)or die(mysql_error());
while ($row = mysql_fetch_assoc($b)){

$id = $row['ID'];
$totime = $row['departure'];
$LndTime = $row['arrival'];
$from = $row['origin'];
$to = $row['destination'];
$acrft = $row['aircraft'];
$Hours = $row['hours'];
$fltno = $row['flightno'];
$dati = $row['datetime'];

$Hours = m2h($Hours);



echo "<tr><td>$totime</td><td>$LndTime</td><td>$apname</td><td>$apname2</td><td>$acrft</td><td>$Hours</td><td>$fltno</td></tr>";


}

because if that hows you use it i get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Where t2.icao=t1.Frm and t3.icao=t1.Too and t1.PilotID=0002 Order by t1.DateTi' at line 15

[homer09001]

ahh Problem solved:

echo '<br><table cellspacing="1" bordercolor=#000000 cellpadding="2" border="2">';
echo "<tr><td>Departure Time</td><td>Arrival Time</td><td>Departing From</td><td>Departing To</td><td>Aircraft Reg</td><td>Duration Of Flight (Hours)</td><td>Flight Number</td></tr>";
$a = "
Select
	t1.ID as id,
	t1.StartTime as departure,
	t1.Landime as arrival,
	t2.AirportName as origin,
	t3.AirportName as destination,
	t1.Aircraft as aircraft,
	t1.TotalHours as hours,
	t1.flightno as flightno,
	t1.datetime as DateTime,
	t1.Frm as frm,
	t1.Too as too
From
	$table3 t1,
	$table5 t2,
	$table5 t3
Where
	t2.icao=t1.Frm
	and t3.icao=t1.Too
	and t1.PilotID=$pid
Order by t1.DateTime ASC
";
//echo $a;
$b = mysql_query($a)or die(mysql_error());
while ($row = mysql_fetch_assoc($b)){

$id = $row['ID'];
$totime = $row['departure'];
$LndTime = $row['arrival'];
$from = $row['origin'];
$to = $row['destination'];
$acrft = $row['aircraft'];
$Hours = $row['hours'];
$fltno = $row['flightno'];
$dati = $row['datetime'];

$Hours = m2h($Hours);



echo "<tr><td>$totime</td><td>$LndTime</td><td>$to</td><td>$from</td><td>$acrft</td><td>$Hours</td><td>$fltno</td></tr>";


}

[homer09001]

Thank you weedpacket that query does make a noticable difference in the loading speed of the pages