Skip to content

Instantly share code, notes, and snippets.

@kigawas

kigawas/flight_query.php

Last active August 29, 2015 14:01
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save kigawas/b9c9a354c3dea4bdccc1 to your computer and use it in GitHub Desktop.
Save kigawas/b9c9a354c3dea4bdccc1 to your computer and use it in GitHub Desktop.
Download ZIP
sql worthy of 70% score
Raw
flight_query.php
<?php
include "config.php";
$dep_keyword = $_GET['dep_keyword'];
$des_keyword = $_GET['des_keyword'];
$tran_keyword = $_GET['tran_keyword'];
$count = 0;
$order_cate = 'price';
if( $dep_keyword == $des_keyword ){
$dep_keyword = "";
$des_keyword = "";
}
if ($tran_keyword == 0)
$order_cate = 'price';
if ($tran_keyword == 1)
$order_cate = 'a_dep_date';
if ($tran_keyword == 2)
$order_cate = 'c_arr_date';
$query = " (select flight_number as id1, null as id2, null as id3,
departure as a_dep, destination as a_des,
null as b_dep, null as b_des,
null as c_dep, null as c_des,
departure_date as a_dep_date, arrival_date as a_arr_date,
null as b_dep_date, null as b_arr_date,
null as c_dep_date, null as c_arr_date,
TIMEDIFF(
SUBTIME( arrival_date, air_2.timezone ),
SUBTIME( departure_date, air_1.timezone )
) as a_f_time,
null as b_f_time,
null as c_f_time,
TIMEDIFF(
SUBTIME( arrival_date, air_2.timezone ),
SUBTIME( departure_date, air_1.timezone )
) as flight_time,
'0' as trans_time,
TIMEDIFF(
SUBTIME( arrival_date, air_2.timezone ),
SUBTIME( departure_date, air_1.timezone )
) as total_time,
price
from Flight, Airport air_1, Airport air_2
where departure like '$dep_keyword' and destination like '$des_keyword' and
departure = air_1.location and destination = air_2.location and
$tran_keyword >= 0 and
TIMEDIFF(
SUBTIME( arrival_date, air_2.timezone ),
SUBTIME( departure_date, air_1.timezone )
) > '00:00:00'
)
UNION (select A.flight_number as id1, B.flight_number as id2, null as id3,
A.departure as a_dep, A.destination as a_des,
B.departure as b_dep, B.destination as b_des,
null as c_dep, null as c_des,
A.departure_date as a_dep_date, A.arrival_date as a_arr_date,
B.departure_date as b_dep_date, B.arrival_date as b_arr_date,
null as c_dep_date, null as c_arr_date,
TIMEDIFF(
SUBTIME( A.arrival_date, air_2.timezone ),
SUBTIME( A.departure_date, air_1.timezone )
) as a_f_time,
TIMEDIFF(
SUBTIME( B.arrival_date, air_3.timezone ),
SUBTIME( B.departure_date, air_2.timezone )
) as b_f_time,
null as c_f_time,
ADDTIME(
ADDTIME( TIMEDIFF( SUBTIME( B.arrival_date, air_3.timezone ),
SUBTIME( B.departure_date, air_2.timezone ) ),
TIMEDIFF( SUBTIME( A.arrival_date, air_2.timezone ),
SUBTIME( A.departure_date, air_1.timezone ) )
),
'00:00:00'
) as flight_time,
TIMEDIFF( B.departure_date, A.arrival_date ) as trans_time,
ADDTIME(
ADDTIME( TIMEDIFF( SUBTIME( B.arrival_date, air_3.timezone ),
SUBTIME( B.departure_date, air_2.timezone ) ),
TIMEDIFF( SUBTIME( A.arrival_date, air_2.timezone ),
SUBTIME( A.departure_date, air_1.timezone ) )
),
TIMEDIFF( B.departure_date, A.arrival_date )
) as total_time,
( A.price + B.price )*0.9 as price
from Flight A, Flight B, Airport air_1, Airport air_2, Airport air_3
where A.departure like '$dep_keyword' and B.destination like '$des_keyword' and
A.destination = B.departure and
air_1.location = A.departure and air_2.location = A.destination and
air_3.location = B.destination and
$tran_keyword >= 1 and
TIMEDIFF( B.departure_date, A.arrival_date ) >= '02:00:00' and
ADDTIME(
ADDTIME( TIMEDIFF( SUBTIME( B.arrival_date, air_3.timezone ),
SUBTIME( B.departure_date, air_2.timezone ) ),
TIMEDIFF( SUBTIME( A.arrival_date, air_2.timezone ),
SUBTIME( A.departure_date, air_1.timezone ) )
),
TIMEDIFF( B.departure_date, A.arrival_date )
) > '00:00:00'
)
UNION (select A.flight_number as id1, B.flight_number as id2, C.flight_number as id3,
A.departure as a_dep, A.destination as a_des,
B.departure as b_dep, B.destination as b_des,
C.departure as c_dep, C.destination as c_des,
A.departure_date as a_dep_date, A.arrival_date as a_arr_date,
B.departure_date as b_dep_date, B.arrival_date as b_arr_date,
C.departure_date as c_dep_date, C.arrival_date as c_arr_date,
TIMEDIFF(
SUBTIME( A.arrival_date, air_2.timezone ),
SUBTIME( A.departure_date, air_1.timezone )
) as a_f_time,
TIMEDIFF(
SUBTIME( B.arrival_date, air_3.timezone ),
SUBTIME( B.departure_date, air_2.timezone )
) as b_f_time,
TIMEDIFF(
SUBTIME( C.arrival_date, air_4.timezone ),
SUBTIME( C.departure_date, air_3.timezone )
) as c_f_time,
ADDTIME(
ADDTIME(
ADDTIME( TIMEDIFF( SUBTIME( B.arrival_date, air_3.timezone ),
SUBTIME( B.departure_date, air_2.timezone )
),
TIMEDIFF( SUBTIME( A.arrival_date, air_2.timezone ),
SUBTIME( A.departure_date, air_1.timezone )
)
),
TIMEDIFF( SUBTIME( C.arrival_date, air_4.timezone ),
SUBTIME( C.departure_date, air_3.timezone )
)
),
'00:00:00'
) as flight_time,
ADDTIME(
TIMEDIFF( B.departure_date, A.arrival_date ),
TIMEDIFF( C.departure_date, B.arrival_date )
) as trans_time,
ADDTIME(
ADDTIME(
ADDTIME( TIMEDIFF( SUBTIME( B.arrival_date, air_3.timezone ),
SUBTIME( B.departure_date, air_2.timezone )
),
TIMEDIFF( SUBTIME( A.arrival_date, air_2.timezone ),
SUBTIME( A.departure_date, air_1.timezone )
)
),
TIMEDIFF( SUBTIME( C.arrival_date, air_4.timezone ),
SUBTIME( C.departure_date, air_3.timezone )
)
),
ADDTIME( TIMEDIFF( B.departure_date, A.arrival_date ),
TIMEDIFF( C.departure_date, B.arrival_date )
)
) as total_time,
( A.price + B.price + C.price )*0.8 as price
from Flight A, Flight B, Flight C, Airport air_1, Airport air_2, Airport air_3, Airport air_4
where A.departure like '$dep_keyword' and C.destination like '$des_keyword' and
A.destination = B.departure and B.destination = C.departure and
A.departure <> B.destination and
air_1.location = A.departure and air_2.location = A.destination and
air_3.location = B.destination and air_4.location = C.destination and
$tran_keyword >= 2 and
TIMEDIFF( B.departure_date, A.arrival_date ) >= '02:00:00' and
TIMEDIFF( C.departure_date, B.arrival_date ) >= '02:00:00' and
ADDTIME(
ADDTIME(
ADDTIME( TIMEDIFF( SUBTIME( B.arrival_date, air_3.timezone ),
SUBTIME( B.departure_date, air_2.timezone )
),
TIMEDIFF( SUBTIME( A.arrival_date, air_2.timezone ),
SUBTIME( A.departure_date, air_1.timezone )
)
),
TIMEDIFF( SUBTIME( C.arrival_date, air_4.timezone ),
SUBTIME( C.departure_date, air_3.timezone )
)
),
ADDTIME( TIMEDIFF( B.departure_date, A.arrival_date ),
TIMEDIFF( C.departure_date, B.arrival_date )
)
) > '00:00:00'
) order by $order_cate asc, flight_time asc
";
$result = $db->query($query);
foreach ($result as $row) {
$count += 1;
echo '<tr>';
echo '<td>' . "$count" . '</td>';
echo "<td class='col-md-1'><label>" . $row['id1'] . "<br>" . $row['id2'] . "<br>" . $row['id3'] . '</label></td>';
echo "<td class='col-md-1'><label>" . $row['a_dep'] . "<br>" . $row['b_dep'] . "<br>" . $row['c_dep'] . '</label></td>';
echo "<td class='col-md-1'><label>" . $row['a_des'] . "<br>" . $row['b_des'] . "<br>" . $row['c_des'] . '</label></td>';
echo "<td class='col-md-3'><label>" . $row['a_dep_date'] . "<br>" . $row['b_dep_date'] . "<br>" . $row['c_dep_date'] . '</label></td>';
echo "<td class='col-md-3'><label>" . $row['a_arr_date'] . "<br>" . $row['b_arr_date'] . "<br>" . $row['c_arr_date'] . '</label></td>';
echo "<td class='col-md-1'><label>" . $row['a_f_time'] . "<br>" . $row['b_f_time'] . "<br>" . $row['c_f_time'] . '</label></td>';
echo "<td class='col-md-1'><label>" . $row['flight_time'] . "<br>" . '</label></td>';
echo "<td class='col-md-1'><label>" . $row['trans_time'] . "<br>" . '</label></td>';
echo "<td class='col-md-1'><label>" . $row['total_time'] . "<br>" . '</label></td>';
echo "<td class='col-md-1'><label>" . $row['price'] . "<br>" . '</label></td>';
echo '</tr>';
}
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment