Upcoming flights

[captaintyson]

Hello all!

I'm writing a code for a freeware program that allows a "Virtual Airline" to store their routes. And when a virtual pilot logs in, he can view the next 10 flights starting from the current time he is at. The problem is, that when I use the code below... It's not finding the upcoming flights. It worked this morning when the time format was "0600" not "2100"

 

query:

$cur = date("Hi");
$query_GetFlights = "SELECT * FROM `flights` WHERE `dep` = '$currentloc' AND `deptime` > '$cur' ORDER by `deptime` ASC LIMIT 10";
$GetFlights = mysql_query($query_GetFlights, $cnct) or die(mysql_error());
$row_GetFlights = mysql_fetch_assoc($GetFlights);
$totalRows_GetFlights = mysql_num_rows($GetFlights);

 

spitting it out:

<?php do {
        $dah = $row_GetFlights['deptime'];
        
        
        if($dah>$cur) {

            
        ?>
        
        <table width="100%" border="0" cellspacing="0" cellpadding="2">
  <tr>
    <td width="20%"><div align="center"><?php echo $airlineicao; echo $row_GetFlights['flight']; ?></div></td>
    <td width="30%"><div align="center"><?php echo $row_GetFlights['dep']; ?></div></td>
    <td width="10%"><div align="center"><?php echo $row_GetFlights['deptime']; ?></div></td>
    <td width="30%"><div align="center"><?php echo $row_GetFlights['arr']; ?></div></td>
    <td width="10%"><div align="center"><?php echo $row_GetFlights['arrtime']; ?></div></td>
    <td width="20%"><div align="center"><a href="<?php echo $airlineurl; ?>/book/flight/<?php echo $row_GetFlights['id']; ?>"><img src="<?php echo $airlineurl; ?>/book.png" width="100" height="20" /></a></div></td>
  </tr>
</table>
        <?php } } while ($row_GetFlights = mysql_fetch_assoc($GetFlights)); 
        ?>

 

Anyone have any feedback? I didnt go to bed last night coding this system. Been awake for 29 hours.

Please help! Im going insane.

[Pikachu2000]

What happens if you echo the query to the screen, and paste it into phpMyAdmin (if you use it) or the MySQL command line?

[captaintyson]

What happens if you echo the query to the screen, and paste it into phpMyAdmin (if you use it) or the MySQL command line?

Can you rephrase that?

[Pikachu2000]

You should echo the query string to the screen so you can make sure it has the values it should have like so:

 

$query_GetFlights = "SELECT * FROM `flights` WHERE `dep` = '$currentloc' AND `deptime` > '$cur' ORDER by `deptime` ASC LIMIT 10";
echo $query_GetFlights;  // <<< ----------  ADD THIS LINE
$GetFlights = mysql_query($query_GetFlights, $cnct) or die(mysql_error());

 

Then copy its output from the screen into phpMyAdmin, or the MySQL command prompt, or whatever database management system you use and see if the query succeeds or fails, and what data is actually returned. These are pretty important debugging methods.

[Pikachu2000]

I should also ask what, if any errors you're getting, and specifically what about the script doesn't work as you expect it should?

[captaintyson]

You should echo the query string to the screen so you can make sure it has the values it should have like so:

 

$query_GetFlights = "SELECT * FROM `flights` WHERE `dep` = '$currentloc' AND `deptime` > '$cur' ORDER by `deptime` ASC LIMIT 10";
echo $query_GetFlights;  // <<< ----------  ADD THIS LINE
$GetFlights = mysql_query($query_GetFlights, $cnct) or die(mysql_error());

 

Then copy its output from the screen into phpMyAdmin, or the MySQL command prompt, or whatever database management system you use and see if the query succeeds or fails, and what data is actually returned. These are pretty important debugging methods.

Oh I see. Hmm, I don't see whats wrong with the code!

 

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0001 sec )
SELECT *
FROM `flights`
WHERE `dep` = 'Austin, TX (KAUS)'
AND `deptime` > '2136'
ORDER BY `deptime` ASC
LIMIT 10

[Pikachu2000]

What it the data type of the `deptime` database field?

[captaintyson]

What it the data type of the `deptime` database field?

[Pikachu2000]

I see why the query didn't return anything. There are no flights leaving KAUS after 2136, if those are the only records . . . (Must be Southwest, no?)

 

BUT one major caveat here, with the deptime field not being an actual date/timestmp, you'll have a hard time selecting flights for the next day, week, month, etc. You should really consider making that field a DATETIME field. That would allow you to use the date and time manipulation functions built in to MySQL.

[captaintyson]

I see why the query didn't return anything. There are no flights leaving KAUS after 2136, if those are the only records . . . (Must be Southwest, no?)

 

BUT one major caveat here, with the deptime field not being an actual date/timestmp, you'll have a hard time selecting flights for the next day, week, month, etc. You should really consider making that field a DATETIME field. That would allow you to use the date and time manipulation functions built in to MySQL.

Ok I see now.

Thanks!!! Lack of sleep