1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
|
$search = $con->prepare("SELECT tb01.IdCompagnie AS 'IDComp',
tb01.NomCompagnie AS 'Nom Compagnie Départ',
tb02.IdVoyage AS 'Numero',
tb09.IdVille AS 'ID1',
tb09.NomVille AS 'Ville de Départ',
tb05.NomGare AS 'Gare de Départ',
tb03.HeureDepart AS 'Heure de Départ',
tb10.IdVille AS 'ID2',
tb10.NomVille AS 'Ville d''Arrivée',
tb06.NomGare AS 'Gare d''Arrivée',
tb04.HeureArrivee AS 'Heure d''Arrivée',
HOUR(tb04.HeureArrivee-tb03.HeureDepart) AS 'Durée',
tb02.NbrePlaces AS 'Nombre de Places',
tb02.Montant AS 'Prix du Trajet'
FROM compagnie AS tb01
INNER JOIN voyage AS tb02
ON tb01.IdCompagnie = tb02.IdCompagnie
INNER JOIN depart AS tb03
ON tb03.IdDepart = tb02.IdDepart
INNER JOIN arrivee AS tb04
ON tb04.IdArrivee = tb02.IdArrivee
INNER JOIN gare AS tb05
ON tb05.IdGare = tb03.IdGare
INNER JOIN gare AS tb06
ON tb06.IdGare = tb04.IdGare
INNER JOIN commune AS tb07
ON tb07.IdCommune = tb05.IdCommune
INNER JOIN commune AS tb08
ON tb08.IdCommune = tb06.IdCommune
INNER JOIN ville AS tb09
ON tb09.IdVille = tb07.IdVille
INNER JOIN ville AS tb10
ON tb10.IdVille = tb08.IdVille
WHERE (tb01.IdCompagnie = ?) OR (tb09.IdVille= ? AND tb10.IdVille = ?) OR (tb01.IdCompagnie = ? AND tb09.IdVille= ? AND tb10.IdVille = ?)
AND NOT EXISTS (SELECT 1
FROM exception as tb51
WHERE tb51.IdVoyage = tb02.IdVoyage
AND tb51.NumJour = WEEKDAY(COALESCE(".$date2.",NOW()))
)
"); |
Partager