Je travaille sur un site web rassemblant des Séries TV.
J'aimerais maintenant, pour chaque fiche série, afficher une page qui liste les épisodes par saison.
Je viens demander un peu d'aide.
Le site se présente ainsi.
En bas de page, un lien qui affiche les épisodes.
Il se présente ainsi :
SAISON 1
|-----------------|--------------------|----------------------|-------------|
| N° Episode | Saison x Eps | Titre Original | Titre Fr |
| -----------------|-------------------|----------------------|-------------|
| 1 | 1 x 01 | Title 1 | Titre 1 |
| 2 | 1 x 02 | Title 2 | Titre 2 |
| 3 | 1 x 03 | Title 3 | Titre 3 |<a href="accueil.php?page=Series_Episodes&episodes=<?php echo $titles['seriesId']; ?>">EPISODES</a>Voici le code de la fiche série :
<?php
$seriesPoster = getPoster();
$titles = getTitle();
$prequel = getSpinOff();
$companyNames = getCompany();
$producerNames = getProducer();
$execProducerNames = getExecProducer();
$directorNames = getDirector();
$creators = getCreator();
$origCreators = getOrigCreator();
$castList = getCast();
$firstAiredInfos = getFirstAired();
$seriesIdTest = getPageOnError();
if((isset($_GET['series'])) && ($_GET['series'] > 0) && ($_GET['series'] == $seriesIdTest)) { // If string is not empty then first test if string match the selection
$seriesIdTest = $_GET['series'];
} else { // If the test fails, return to greating page
header("Location: accueil.php");
ob_clean();
die();
}
?>
<section class="series accueilContainer col-10">
<figure class="poster">
<?php if(isset($seriesPoster)) { ?>
<img class="seriesImage" src="<?php echo '/series/images/'.$seriesPoster; ?>">
<?php } else { ?>
<img class="seriesImage" src="/series/images/no_image_available.png">
<?php } ?>
</figure>
<article class="seriesRecord">
<!-- TITLE SECTION DISPLAY -->
<h1 class="seriesTitle"><?php echo $titles['title']; ?></h1>
<!-- If the Series is a Spin-Off display the prequel -->
<?php if ($prequel <> 0) { ?>
<span id="spinOff">Spin-Off de <?php echo $prequel; ?></span>
<?php } ?>
<!-- PRODUCTION SECTION DISPLAY -->
<?php if($companyNames <> 0) { ?>
<div class="castAndCrew">
<h2>PRODUCTION</h2>
<dl>
<?php foreach ($companyNames as $companyName) { ?>
<dd><?php echo $companyName['companyName'] ?></dd>
<?php } ?>
</dl>
</div>
<?php } ?>
<!-- PRODUCER SECTION DISPLAY -->
<?php if($producerNames <> 0) { ?>
<div class="castAndCrew">
<h2>PRODUCTEUR</h2>
<dl>
<?php foreach ($producerNames as $producerName) { ?>
<dd><?php echo $producerName['producerName'] ?></dd>
<?php } ?>
</dl>
</div>
<?php } ?>
<!-- EXECUTIVE PRODUCER SECTION DISPLAY -->
<?php if($execProducerNames <> 0) { ?>
<div class="castAndCrew">
<h2>PRODUCTEUR EXECUTIF</h2>
<dl>
<?php foreach ($execProducerNames as $execProducerName) { ?>
<dd><?php echo $execProducerName['execProducerName'] ?></dd>
<?php } ?>
</dl>
</div>
<?php } ?>
<!-- DIRECTOR SECTION DISPLAY -->
<?php if($directorNames <> 0) { ?>
<div class="castAndCrew">
<h2>REALISATEUR</h2>
<dl>
<?php foreach ($directorNames as $directorName) { ?>
<dd><?php echo $directorName['directorName'] ?></dd>
<?php } ?>
</dl>
</div>
<?php } ?>
<!-- CREATED BY SECTION DISPLAY -->
<?php if($creators <> 0) { ?>
<div class="castAndCrew">
<h2>CREATION</h2>
<dl>
<?php foreach ($creators as $creator) { ?>
<dd class="castAndCrew"><?php echo $creator['creatorName'] ?></dd>
<?php } ?>
</dl>
</div>
<?php }
if($origCreators <> 0) { ?>
<div class="castAndCrew">
<dl>
<?php foreach ($origCreators as $origCreator) {
echo $origCreator['origCreator'];
}
?>
</dl>
</div>
<?php } ?>
<!-- CAST SECTION DISPLAY -->
<div class="castAndCrew">
<h2>DISTRIBUTION</h2>
<?php if ($castList[0]['period'] == '-') { ?>
<dl>
<?php foreach ($castList as $cast) {
$castCrew[$cast['charName']][] = $cast['personName'];
}
foreach ($castCrew as $char => $actor) {
?>
<dd><?php echo implode(' -> ', $actor), " : {$char}" ?></dd>
<?php } ?>
</dl>
<?php } else {
foreach ($castList as $cast) {
$castCrew[$cast['period']][$cast['charName']][] = $cast['personName'];
}
foreach ($castCrew as $castPeriod => $chars) {
?>
<dl>
<dt><?php echo $castPeriod ?></dt>
<?php foreach ($chars as $char => $actor) { ?>
<dd><?php echo implode(' -> ', $actor), " : {$char}" ?></dd>
<?php } ?>
</dl>
<?php }
}
?>
</div>
<!-- FIRST AIRED SECTION DISPLAY -->
<div class="castAndCrew">
<h2>PREMIERE DIFFUSION</h2>
<dl>
<?php
foreach ($firstAiredInfos as $firstAired) {
if($firstAired['countryName'] <> 'France') {
?>
<dd class="castAndCrew"><?php echo $firstAired['countryName'].' : '.$firstAired['channelName'].' - '.$firstAired['releaseYear'] ?></dd>
<dd class="castAndCrew">France : <?php echo $firstAired['frenchChannelName'].' - '.$firstAired['frenchReleaseYear'] ?></dd>
<?php } else { ?>
<dd class="castAndCrew">France : <?php echo $firstAired['channelName'].' - '.$firstAired['releaseYear'] ?></dd>
<?php }
}
?>
</dl>
</div>
<!-- RETURN LINK -->
<div class="retour">
<span><?php echo "<a href=\"javascript:history.go(-1)\">" ?>RETOUR</a></span>
</div>
<!-- EPISODES LINK -->
<div class="retour">
<span><a href="accueil.php?page=Series_Episodes&episodes=<?php echo $titles['seriesId']; ?>">EPISODES</a></span>
</div>
</article>
</section>
<?php
echo ob_get_clean();
?>
<?php
$seasonNumber = getSeasonNumber();
$episodelist = getEpisodes();
$seriesIdTest = getPageOnError();
if((isset($_GET['episodes'])) && ($_GET['episodes'] > 0) && ($_GET['episodes'] == $seriesIdTest)) { // If string is not empty then first test if string match the selection
$seriesIdTest = $_GET['episodes'];
} else { // If the test fails, return to greating page
header("Location: accueil.php");
ob_clean();
die();
}
?>
<!-- EPISODES SECTION DISPLAY -->
<div class="castAndCrew">
<h2>EPISODES</h2>
<?php foreach ($episodeList as $episodes) {
$episodeSeason[$episode['season']][$episode['episodeName']];
}
foreach ($episodeList as $seasonEpisode => $eps) {
?>
<dl>
<dt><?php echo $seasonEpisode ?></dt>
<?php foreach ($eps as $episode => $seasonEps) { ?>
<dd><?php echo $seasonEps ?></dd>
<?php } ?>
</dl>
<?php }
?>
</div>
<?php
if(isset($_GET['series'])) {
$seriesId = $_GET['series'];
}
if(isset($_GET['episodes'])) {
$seriesId = $_GET['episodes'];
}
if(isset($_GET['letter'])) {
$letter = $_GET['letter'];
}
function getPageOnError() {
global $pdo;
global $seriesId;
$sql = <<<SQL
SELECT * FROM SERIES
WHERE seriesId = :seriesId
SQL;
$idQuery = $pdo->prepare($sql);
$idQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$idQuery->execute();
$row = $idQuery->fetch();
$seriesIdTest = $row['seriesId'];
return $seriesIdTest;
}
function getLetter() {
global $pdo;
global $letter;
return $letter;
}
function getSeriesList() {
global $pdo;
global $letter;
// Getting the series list from first letter
$sql = <<<SQL
SELECT titleSort, seriesTitle, seriesId, releaseYear FROM SORTLIST
WHERE titleSort RLIKE '^[$letter]'
ORDER BY titleSort, releaseYear
SQL;
$query = $pdo->prepare($sql);
$query->execute();
$seriesList = [];
while (($row = $query->fetch())) {
$seriesList = [
'seriesTitle' => $row['seriesTitle'],
'releaseYear' => $row['releaseYear'],
'seriesId' => $row['seriesId'],
];
$seriesLists[] = $seriesList;
}
return $seriesLists;
}
function getPoster() {
global $pdo;
global $seriesId;
// Getting the series poster, if any
$sql = <<<SQL
SELECT seriesTitle, seriesPoster FROM SERIES
WHERE seriesId = :seriesId
SQL;
$imageQuery = $pdo->prepare($sql);
$imageQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$imageQuery->execute();
$row = $imageQuery->fetch();
$seriesPoster = $row['seriesPoster'];
return $seriesPoster;
}
function getTitle() {
global $pdo;
global $seriesId;
// Getting the series title
$sql = <<<SQL
SELECT S.seriesId AS seriesId, SL.seriesTitle AS frenchTitle, S.seriesTitle AS seriesTitle FROM SERIESLIST AS SL
LEFT JOIN SERIES AS S ON S.seriesId = SL.seriesId
WHERE SL.seriesId = :seriesId
SQL;
$titleQuery = $pdo->prepare($sql);
$titleQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$titleQuery->execute();
$row = $titleQuery->fetch();
if ($row['frenchTitle'] == $row['seriesTitle']) { // Test if The original an french titles are the same
$title = mb_strtoupper($row['frenchTitle']); // If they are the same display only one uppercase title
} else { // If they're different, display the french title uppercase and the original one lowercase
$title = mb_strtoupper($row['frenchTitle'])." (".$row['seriesTitle'].")";
$limit = "40";
if(strlen($title) <= $limit) { // Test String Lenght
$title = mb_strtoupper($row['frenchTitle'])." (".$row['seriesTitle'].")"; // If the lenght is smaller then limit
} else { // If lenght is bigger, display the title on 2 lines
$title = mb_strtoupper($row['frenchTitle'])."<br>(".$row['seriesTitle'].")";
}
}
$titles = [
'pageTitle' => $row['frenchTitle'],
'title' => $title,
'seriesId' => $row['seriesId'],
];
return $titles;
}
function getSpinOff() {
// Getting the Spin-Off if any
global $pdo;
if(isset($_GET['series'])) {
$seriesId = $_GET['series'];
}
$sql = <<<SQL
SELECT S.seriesId FROM SERIES AS S
JOIN SPINOFF AS SO ON SO.seriesId_spinOff = S.seriesId
WHERE S.seriesId = :seriesId AND SO.seriesId_spinOff = S.seriesId
SQL;
$isSpinOffQuery = $pdo->prepare($sql);
$isSpinOffQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$isSpinOffQuery->execute();
$row = $isSpinOffQuery->fetch();
if (isset($row['seriesId'] )) { // Test if the series is a Spin-Off
// Getting the prequel from Query
$sql = <<<SQL
SELECT PL.seriesTitle AS prequel, S.seriesTitle AS spinOff FROM SPINOFF AS SO
JOIN SERIESLIST AS PL ON PL.seriesId = SO.seriesId_prequel
JOIN SERIESLIST AS S ON S.seriesId = SO.seriesId_spinOff
WHERE S.seriesId = :seriesId
SQL;
$spinOffQuery = $pdo->prepare($sql);
$spinOffQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$spinOffQuery->execute();
$row = $spinOffQuery->fetch();
$prequel = $row['prequel'];
} else {
$prequel = 0;
}
return $prequel;
}
function getCompany() {
global $pdo;
global $seriesId;
// Get the Company Name if any
$sql = <<<SQL
SELECT companyName AS companyName FROM COMPANY AS C
LEFT JOIN PRODUCTION AS P ON P.companyId = C.companyId
LEFT JOIN SERIES AS S ON S.seriesId = P.seriesId
WHERE S.seriesId = :seriesId
SQL;
$companyQuery = $pdo->prepare($sql);
$companyQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$companyQuery->execute();
$rows = $companyQuery->fetchAll();
if(isset($rows[0]['companyName'])) {
$companyName = [];
foreach ($rows as $row) {
$companyName = [
'companyName' => $row['companyName'],
];
$companyNames[] = $companyName;
}
} else {
$companyNames = 0;
}
return $companyNames;
}
function getProducer() {
global $pdo;
global $seriesId;
// Getting the Producer Name if any
$sql = <<<SQL
SELECT personName AS personName FROM PERSON AS P
LEFT JOIN CREW AS C ON C.personId = P.personId
LEFT JOIN ROLE AS R ON R.roleId = C.roleId
LEFT JOIN SERIES AS S ON S.seriesId = C.seriesId
WHERE S.seriesId = :seriesId
AND R.roleId = 1
SQL;
$producerQuery = $pdo->prepare($sql);
$producerQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$producerQuery->execute();
$rows = $producerQuery->fetchAll();
if(isset($rows[0]['personName'])) {
$producerName = [];
foreach ($rows as $row) {
$producerName = [
'producerName' => $row['personName'],
];
$producerNames[] = $producerName;
}
} else {
$producerNames = 0;
}
return $producerNames;
}
function getExecProducer() {
global $pdo;
global $seriesId;
// Getting the Executive Producer Name if any
$sql = <<<SQL
SELECT personName AS personName FROM PERSON AS P
LEFT JOIN CREW AS C ON C.personId = P.personId
LEFT JOIN ROLE AS R ON R.roleId = C.roleId
LEFT JOIN SERIES AS S ON S.seriesId = C.seriesId
WHERE S.seriesId = :seriesId
AND R.roleId = 2
SQL;
$execProducerQuery = $pdo->prepare($sql);
$execProducerQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$execProducerQuery->execute();
$rows = $execProducerQuery->fetchAll();
if(isset($rows[0]['personName'])) {
$execProducerName = [];
foreach ($rows as $row) {
$execProducerName = [
'execProducerName' => $row['personName'],
];
$execProducerNames[] = $execProducerName;
}
} else {
$execProducerNames = 0;
}
return $execProducerNames;
}
function getDirector() {
global $pdo;
global $seriesId;
// Getting the Director Name if any
$sql = <<<SQL
SELECT personName AS personName FROM PERSON AS P
LEFT JOIN CREW AS C ON C.personId = P.personId
LEFT JOIN ROLE AS R ON R.roleId = C.roleId
LEFT JOIN SERIES AS S ON S.seriesId = C.seriesId
WHERE S.seriesId = :seriesId
AND R.roleId = 3
SQL;
$directorQuery = $pdo->prepare($sql);
$directorQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$directorQuery->execute();
$rows = $directorQuery->fetchAll();
if(isset($rows[0]['personName'])) {
$directorName = [];
foreach ($rows as $row) {
$directorName = [
'directorName' => $row['personName'],
];
$directorNames[] = $directorName;
}
} else {
$directorNames = 0;
}
return $directorNames;
}
function getCreator() {
global $pdo;
global $seriesId;
// Getting the Creator Name if any
$crSql = <<<SQL
SELECT personName AS personName FROM PERSON AS P
LEFT JOIN CREW AS C ON C.personId = P.personId
LEFT JOIN ROLE AS R ON R.roleId = C.roleId
LEFT JOIN SERIES AS S ON S.seriesId = C.seriesId
WHERE S.seriesId = :seriesId AND R.roleId = 5
SQL;
$creatorQuery = $pdo->prepare($crSql);
$creatorQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$creatorQuery->execute();
$rows = $creatorQuery->fetchAll();
if(isset($rows[0]['personName'])) {
$creator = [];
foreach ($rows as $row) {
$creator = [
'creatorName' => $row['personName'],
];
$creators[] = $creator;
}
} else {
$creators = 0;
}
return $creators;
}
function getOrigCreator() {
global $pdo;
global $seriesId;
// Getting the Original Creator Name if any
$ocSql = <<<SQL
SELECT originalText AS origText FROM ORIG_CREATION AS OC
LEFT JOIN SERIES AS S ON S.originalId = OC.originalId
WHERE S.seriesId = :seriesId
SQL;
$origCreatorQuery = $pdo->prepare($ocSql);
$origCreatorQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$origCreatorQuery->execute();
$rows = $origCreatorQuery->fetchAll();
if(isset($rows[0]['origText'])) {
$origCreator = [];
foreach ($rows as $row) {
$origCreator = [
'origCreator' => $row['origText'],
];
$origCreators[] = $origCreator;
}
} else {
$origCreators = 0;
}
return $origCreators;
}
function getCast() {
global $pdo;
global $seriesId;
// Getting Cast List
$sql = <<<SQL
SELECT S.seriesId, CP.characterId AS charId, P.personId, seriesTitle, characterName AS charName, personName, appearanceOrder AS appOrder, period, periodNumber AS periodNum FROM CASTING AS C
JOIN SERIES AS S ON S.seriesId = C.seriesId
JOIN PERSON AS P ON P.personId = C.personId
JOIN CHAR_PLAYED AS CP ON CP.characterId = C.characterId
WHERE S.seriesId = :seriesId
ORDER BY periodNum, appOrder
SQL;
$castQuery = $pdo->prepare($sql);
$castQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$castQuery->execute();
$rows = $castQuery->fetchAll();
$cast = [];
foreach ($rows as $row) {
$cast = [
'period' => $row['period'],
'charName' => $row['charName'],
'personName' => $row['personName'],
];
$castList[] = $cast;
}
return $castList;
}
function getFirstAired() {
global $pdo;
global $seriesId;
// Getting the series language
$sql = <<<SQL
SELECT S.seriesId FROM SERIES AS S
JOIN FRENCH_SERIES AS F ON F.seriesId = S.seriesId
WHERE S.seriesId = :seriesId AND F.seriesId = S.seriesId
SQL;
$isFrenchQuery = $pdo->prepare($sql);
$isFrenchQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$isFrenchQuery->execute();
$row = $isFrenchQuery->fetch();
// Gettings first aired channel and year
$sql = <<<SQL
SELECT countryName, releaseYear, frenchReleaseYear, channelName, frenchChannelName FROM FIRSTAIRED
WHERE seriesId = :seriesId
SQL;
$airedQuery = $pdo->prepare($sql);
$airedQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$airedQuery->execute();
if (isset($row['seriesId'] )) { // Test if it's a french series
$firstAired = [];
$row = $airedQuery->fetch();
$firstAired = [
'countryName' => 'France',
'channelName' => $row['channelName'],
'releaseYear' => $row['releaseYear'],
];
$firstAiredInfos[] = $firstAired;
return $firstAiredInfos;
} else {
$firstAired = [];
$row = $airedQuery->fetch();
$firstAired = [
'countryName' => $row['countryName'],
'channelName' => $row['channelName'],
'releaseYear' => $row['releaseYear'],
'frenchChannelName' => $row['frenchChannelName'],
'frenchReleaseYear' => $row['frenchReleaseYear'],
];
$firstAiredInfos[] = $firstAired;
return $firstAiredInfos;
}
}
function getSeasonNumber() {
global $pdo;
global $seriesId;
// Getting Season Number
$sql = <<<SQL
SELECT S.seriesId, SN.seasonId, SN.seasonNumber, E.seasonId FROM SEASON AS SN
JOIN EPISODE AS E ON E.seriesId = S.seriesId
JOIN SERIES AS S ON S.seriesId = E.seriesId
WHERE S.seriesId = :seriesId
ORDER BY seasonId
SQL;
$seasonQuery = $pdo->prepare($sql);
$seasonQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$seasonQuery->execute();
$rows = $seasonQuery->fetchAll();
$season = [];
foreach ($rows as $row) {
$season = [
'seasonNumber' => $row['seasonNumber'],
];
$seasonList[] = $season;
}
return $seasonList;
}
function getEpisodes() {
global $pdo;
global $seriesId;
// Getting Episodes List
$sql = <<<SQL
SELECT S.seriesId, E.episodeId AS epId, E.episodeNumber, E.seasonEpisodeNumber, SN.seasonId AS seasonId, SN.seasonNumber as SeasonNumber FROM EPISODE AS E
JOIN SERIES AS S ON S.seriesId = E.seriesId
JOIN SEASON AS SN ON SN.seasonId = E.seasonId
WHERE S.seriesId = :seriesId
ORDER BY episodeNumber, seasonEpisodeNumber
SQL;
$episodesQuery = $pdo->prepare($sql);
$episodesQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
$episodesQuery->execute();
$rows = $episodesQuery->fetchAll();
$episode = [];
foreach ($rows as $row) {
$episode = [
'epNumber' => $row['episodeNumber'],
'seasonEpNumber' => $row['seasonEpisodeNumber'],
'episodeTitle' => $row['episodeTitle'],
];
$episodeList[] = $episode;
}
return $episodeList;
}
?>