Statistiques d’une Tournée
[insert_php]
require (“Gestion_LOHAD.php”);
$cas = $_GET[‘cas’];
$id_tournee = $_GET[‘id_tournee’];
$cas = $conn->real_escape_string($_GET[‘cas’]);
$id_tournee = $conn->real_escape_string($_GET[‘id_tournee’]);
//echo “
$id_tournee
“;
//récupérer la tb_tournée
$tour = (“SELECT tb_tournee.id_tournee, tb_tournee.tournee, tb_tournee.image_tour FROM tb_tournee WHERE id_tournee = $id_tournee”);
$res_tour = $conn->query($tour);
$re_tour = mysqli_fetch_array($res_tour);
$tournee = $re_tour[‘tournee’];
//nb de concert
$count_conc = (“SELECT COUNT(*) AS nbr_conc FROM tb_concert, tb_leg WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee”);
$res_count_conc = $conn->query($count_conc);
$rep_count_conc = mysqli_fetch_array($res_count_conc);
$nbr_conc = $rep_count_conc[‘nbr_conc’];
//nb de pays
$count_pays =(“SELECT COUNT( DISTINCT tb_ville.id_pays ) AS nbr_pays
FROM tb_concert, tb_leg, tb_salle, tb_ville
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee AND tb_concert.id_salle = tb_salle.id_salle
AND tb_salle.id_ville = tb_ville.id_ville”);
$res_count_pays = $conn->query($count_pays);
$rep_count_pays = mysqli_fetch_array($res_count_pays);
$nbr_pays = $rep_count_pays[‘nbr_pays’];
//nb de salle
$count_salle =(“SELECT COUNT( DISTINCT tb_concert.id_salle ) AS nbr_salle
FROM tb_concert, tb_leg
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee”);
$res_count_salle = $conn->query($count_salle);
$rep_count_salle = mysqli_fetch_array($res_count_salle);
$nbr_salle = $rep_count_salle[‘nbr_salle’];
//nb de morceaux joués
$count_morco = (“SELECT COUNT(*) AS nbr_morco
FROM tb_concert, tb_leg, tb_unite
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee AND tb_concert.id_concert = tb_unite.id_concert”);
$res_count_morco = $conn->query($count_morco);
$rep_count_morco = mysqli_fetch_array($res_count_morco);
$nbr_morco = $rep_count_morco[‘nbr_morco’];
//nb de chansons différentes
$count_songs = (“SELECT COUNT( DISTINCT tb_unite.id_chanson ) AS nbr_songs
FROM tb_concert, tb_leg, tb_unite
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee AND tb_concert.id_concert = tb_unite.id_concert”);
$res_count_songs = $conn->query($count_songs);
$rep_count_songs = mysqli_fetch_array($res_count_songs);
$nbr_songs = $rep_count_songs[‘nbr_songs’];
//nb moyen de chanson par concert
$moy_songs = (“SELECT AVG( tb_concert.nbr_chanson ) AS moy_songs
FROM tb_concert, tb_leg
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee”);
$res_moy_songs = $conn->query($moy_songs);
$rep_moy_songs = mysqli_fetch_array($res_moy_songs);
$moy_songs = $rep_moy_songs[‘moy_songs’];
$moy_songs = number_format($moy_songs, 2, ‘,’, ‘ ‘);
$ratio = (($rep_count_songs[‘nbr_songs’] – $rep_moy_songs[‘moy_songs’])/$rep_count_conc[‘nbr_conc’]) ;
$ratio = number_format($ratio, 2, ‘,’, ‘ ‘);
//nb de chanson maxi
$max_songs = (“SELECT MAX( tb_concert.nbr_chanson ) AS max_songs
FROM tb_concert, tb_leg
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee”);
$res_max_songs = $conn->query($max_songs);
$rep_max_songs = mysqli_fetch_array($res_max_songs);
$max_songs = $rep_max_songs[‘max_songs’];
//nb de chanson mini
$min_songs = (“SELECT MIN( tb_concert.nbr_chanson ) AS min_songs
FROM tb_concert, tb_leg
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee”);
$res_min_songs = $conn->query($min_songs);
$rep_min_songs = mysqli_fetch_array($res_min_songs);
$min_songs = $rep_min_songs[‘min_songs’];
//les chansons jouées:
$songs = (“SELECT DISTINCT ( tb_chanson.titre ), tb_chanson.id_chanson, tb_album.image_album
FROM tb_concert, tb_leg, tb_unite, tb_chanson, tb_album
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee
AND tb_leg.numero > 0
AND tb_concert.id_concert = tb_unite.id_concert AND tb_unite.id_chanson = tb_chanson.id_chanson
AND tb_chanson.id_album = tb_album.id_album ORDER BY titre ASC”);
//nb de chansons différentes hors répétitions
$count_songs_rep = (“SELECT COUNT( DISTINCT tb_unite.id_chanson ) AS nbr_songs
FROM tb_concert, tb_leg, tb_unite
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee AND tb_concert.id_concert = tb_unite.id_concert AND tb_leg.numero>0”);
$res_count_songs_rep = $conn->query($count_songs_rep);
$rep_count_songs_rep = mysqli_fetch_array($res_count_songs_rep);
//nb de morceaux joués hors répértitions
$count_morco_rep = (“SELECT COUNT(*) AS nbr_morco
FROM tb_concert, tb_leg, tb_unite
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee AND tb_concert.id_concert = tb_unite.id_concert AND tb_leg.numero>0”);
$res_count_morco_rep = $conn->query($count_morco_rep);
$rep_count_morco_rep = mysqli_fetch_array($res_count_morco_rep);
//nb de concert hors répétitions
$count_conc_rep = (“SELECT COUNT(*) AS nbr_conc FROM tb_concert, tb_leg WHERE tb_concert.id_leg = tb_leg.id_leg
AND tb_leg.id_tournee = $id_tournee AND tb_leg.numero>0”);
$res_count_conc_rep = $conn->query($count_conc_rep);
$rep_count_conc_rep = mysqli_fetch_array($res_count_conc_rep);
//les ALBUMS
$albums = (“SELECT DISTINCT ( tb_album.album ), tb_album.id_album, tb_album.image_album, tb_album.date_paru
FROM tb_concert, tb_leg, tb_unite, tb_chanson, tb_album
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee
AND tb_leg.numero > 0
AND tb_concert.id_concert = tb_unite.id_concert AND tb_unite.id_chanson = tb_chanson.id_chanson
AND tb_chanson.id_album = tb_album.id_album ORDER BY tb_album.date_paru ASC”) ;
echo “
$tournee
“;
echo “
Nb de concerts (NC) |
Nb de pays | Nb de salles | Nb de morceaux joués | Nb de chansons différentes (N1) |
Nb moy de chansons par concert (Nmoy) |
Nb maxi de chansons par concert | Nb min de chansons par concert | Ratio (N1-Nmoy)/NC |
|
---|---|---|---|---|---|---|---|---|---|
Total | $nbr_conc | $nbr_pays | $nbr_salle | $nbr_morco | $nbr_songs | $moy_songs | $max_songs | $min_songs | $ratio |
$leg | $nbr_conc | $nbr_pays | $nbr_salle | $nbr_morco | $nbr_songs | $moy_songs | $max_songs | $min_songs | $ratio1 |
“;
mysqli_close($conn);
[/insert_php]