Grouping episodes by season

General PHP Help
#1

Hello,

I request some help, please.

For each series, I would to display episodes, grouping them in a HTML table by season so that a border appears between each season.

https://zupimages.net/viewer.php?id=24/23/uphl.jpg

Here is my code :

<?php
	$titles = getTitle();
	$episodeList = getEpisodes();
	$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 episodeSection col-10">
	<article class="seriesRecord">
	
		<!-- TITLE DISPLAY -->
	  	<h1 class="episodesTitle epListTitle"><mark>EPISODES</mark></h1>
	  	<h1 class="episodesTitle epListTitle"><mark><?php echo $titles['title']; ?><mark></h1>
	  	
	  	<!-- EPISODES SECTION DISPLAY -->
	  	
	  	<table class="episodeTable">
			<?php
				foreach ($episodeList as $episodes) {
			?>
					<tr class="seasonLine">
						<tr>
							<td class="episodeCell episodeNumberCell"><span class="episodes"><?php echo $episodes['epNumber']; ?></span></td>
							<td class="episodeCell episodeSeasonCell"><span class="episodes"><?php echo $episodes['seasonNumber']."-".$episodes['seasonEpNumber']; ?></span></td>
							<td class="episodeCell episodeOrigCell"><span class="episodes"><?php echo $episodes['episodeTitle']; ?></span></td>
							<td class="episodeCell episodeFrenchCell"><span class="episodes"><?php echo $episodes['frenchEpisodeTitle']; ?></span></td>
						</tr>
					</tr>
			<?php } ?>
		  		</table>
  	</article>
</section>
#2

The simplest way of doing this is to index/pivot the data when you fetch it, using the season number as the array index. You can then use two nested foreach loops to produce the output -

foreach($data as $arr)
{
    // output markup for the the start of a season here..
    foreach($arr as $row)
    {
        // output the rows of data for the current season
    {
    // output any markup needed at the end of a season here...
}
#3

So, I tried this :

<table class="episodeTable">
			<?php
				foreach ($episodeList as $episodes) {
			?>
					<tr class="seasonLine">
			<?php
					foreach ($episodes as $row) { ?>
						<tr>
							<td class="episodeCell episodeNumberCell"><span class="episodes"><?php echo $row['sesonId']['epNumber']; ?></span></td>
							<td class="episodeCell episodeSeasonCell"><span class="episodes"><?php echo $row['sesonId']['seasonNumber']."-".$row['seasonEpNumber']; ?></span></td>
							<td class="episodeCell episodeOrigCell"><span class="episodes"><?php echo $row['sesonId']['episodeTitle']; ?></span></td>
							<td class="episodeCell episodeFrenchCell"><span class="episodes"><?php echo $row['sesonId']['frenchEpisodeTitle']; ?></span></td>
						</tr>
			<?php } ?>
					</tr>
			<?php } ?>
		  		</table>

and my query :

function getEpisodes() {
      global $pdo;
      global $seriesId;
      // Getting Episodes List
      $sql = <<<SQL
         SELECT S.seriesId, E.episodeId, E.episodeNumber, E.seasonEpisodeNumber, E.episodeTitle, FE.frenchEpisodeTitle, SN.seasonNumber as seasonNumber FROM EPISODE AS E
         JOIN SERIES AS S ON S.seriesId = E.seriesId
         JOIN FOREIGN_EPISODE AS FE ON FE.episodeId = E.episodeId
         JOIN SEASON AS SN ON SN.seasonId = E.seasonId
         WHERE S.seriesId = :seriesId
         GROUP BY seasonNumber, seasonEpisodeNumber
         ORDER BY seasonNumber, episodeNumber, seasonEpisodeNumber ASC
         SQL;
		 
      $episodesQuery = $pdo->prepare($sql);
      $episodesQuery->bindParam(':seriesId', $seriesId, PDO::PARAM_INT);
      $episodesQuery->execute();
      $rows = $episodesQuery->fetchAll();
      $episode = [];
      foreach ($rows as $row) {
         $episode = [
            'seriesId' => $row['seriesId'],
            'episodeId' => $row['episodeId'],
            'epNumber' => "0".$row['episodeNumber'],
            'seasonNumber' => $row['seasonNumber'],
            'seasonEpNumber' => "0".$row['seasonEpisodeNumber'],
            'episodeTitle' => $row['episodeTitle'],
            'frenchEpisodeTitle' => $row['frenchEpisodeTitle'],
         ];
			
         $episodeList['seriesId'] = $episode;
      }
		
      return $episodeList;
   }

I obtain these two errors

Warning : Trying to access array offset on value of type int in /var/www/seriemaniacs/series/episodes.php on line 32

and this :

Warning : Trying to access array offset on value of type int in /var/www/seriemaniacs/series/episodes.php on line 33

#4

The following should (untested) work -

function getEpisodes()
{
	// don't use global to make your code work. you should pass all inputs to a function as call-time parameters
	global $pdo;
	global $seriesId;
	
	// Getting Episodes List
	  
	// if you are changing the name you reference a column by, just use an alias in the query for the new name
	// aliasing a column to the same name doesn't do anything - SN.seasonNumber as seasonNumber is the same as just SN.seasonNumber
	// the as keyword for an alias is optional
	// if there's only one row of data per seasonNumber/seasonEpisodeNumber the GROUP BY doesn't do anything and can be removed
	// the default ORDER BY direction is ASC, you don't need to specify ASC
	$sql = <<<SQL
		SELECT S.seriesId, E.episodeId, E.episodeNumber epNumber, E.seasonEpisodeNumber seasonEpNumber,
			E.episodeTitle, FE.frenchEpisodeTitle, SN.seasonNumber
		FROM EPISODE AS E
		JOIN SERIES AS S ON S.seriesId = E.seriesId
		JOIN FOREIGN_EPISODE AS FE ON FE.episodeId = E.episodeId
		JOIN SEASON AS SN ON SN.seasonId = E.seasonId
		WHERE S.seriesId = ?
		GROUP BY seasonNumber, seasonEpisodeNumber
		ORDER BY seasonNumber, episodeNumber, seasonEpisodeNumber
SQL;
		 
	$stmt = $pdo->prepare($sql);
	$stmt->execute([ $seriesId ]);
	
	// index/pivot the data, using the season number as the array index
	$data = [];
	// you can directly iterate over the PDOStatement object to fetch the data
	foreach($stmt as $row)
	{
		// unconditionally concatenating a leading zero will result in 010, 011, ... values. you would use str_pad() to give a maximum of two characters
		$row['epNumber'] = str_pad($row['epNumber'], 2, '0',STR_PAD_LEFT);
		$row['seasonEpNumber'] = str_pad($row['seasonEpNumber'], 2, '0',STR_PAD_LEFT);
		$data[$row['seasonNumber']][] = $row;
	}
	$return $data;
}
#5

Thank you very much phdr. It’s working now.

Sponsor our Newsletter | Privacy Policy | Terms of Service