SQL How to order by differents times-CodePudding

Hi, I have created in the db two rows for one flight, one for the arrival and another for departure. Now Ineed to order by arrival time and departure time in the order.

This is my code but it's not working. In the date that I've selected I have to display the arrivals and departures in the rights order eg:

Flight 1 : arr 10:30 Flight 2 : dep 10:55 Flight 1 : Dep 11:00 Flight 5 : Dep 11:20 And so on....

$sqlArr = "SELECT * FROM flights  WHERE arr_date = '$date' OR dep_date = '$date' ORDER BY 
eta,etd ASC";

$query = mysqli_query($cnx, $sqlArr);

foreach ($query as $key => $v ){
 if ($v['xid_arrival'] == 0) {?>
  <tr>
   <td>ARR</td>
   <td><?=$v['arr_date'];?> </td>
   <td><?=$v['atyp'];?> </td>
   <td><?= $v['reg'];?> </td>
   <td><?=$v['arr_cls'];?> </td>
   <td><?=$v['adep'];?> </td>
   <td><?= date('H:i', strtotime($v['eta']));?> </td>
   <td onclick="openRow(this, <?= $v['id_flt'];?>)"> </td>
  </tr>
  <tr hidden  id="<?= $v['id_flt'];?>">
   <td colspan="6">hidden id <?= $v['id_flt'];?></td>
  </tr>
  <?php } else  { ?>
  <tr>
   <td>DEP</td>
   <td><?=$v['dep_date'];?> </td>
   <td><?=$v['atyp'];?> </td>
   <td><?= $v['reg'];?> </td>
   <td><?=$v['dep_cls'];?> </td>
   <td><?=$v['ades'];?> </td>
   <td><?= date('H:i', strtotime($v['etd']));?> </td>
   <td onclick="openRow(this, <?= $v['xid_arrival'];?>)"> </td>
  </tr>
  <tr hidden id="<?= $v['xid_arrival'];?>">
   <td colspan="6">hidden xid  <?= $v['xid_arrival'];?></td>
  </tr>
<?php   }?>
<?php } ?>

CodePudding user response：

You need to create one column with departure or arrival date depending which is defined in a line and order by that column.

Something like this

SELECT 
 *,
 CASE
   WHEN eta > 0 THEN eta
   WHEN etd > 0 THEN etd
 END as sort_order
FROM flights
WHERE
 arr_date = '$date' OR dep_date = '$date' 
ORDER BY 
 sort_order ASC;

And remember to escape data you get as input to your SQL query to prevent SQL injection vulnerabilities.

And SELECT * is lazy coding. Select only columns you neeed.