PHP MySQL grouping and ordering

Question

I have a tv show I created a MySQL database for. I am trying to get my code to group and order correctly the seasons and episodes within their respective season.

Current Code

if (!($result = mysql_query("SELECT season_num, sub_season_num, esp_num, title, descrip FROM season ORDER BY season_num, sub_season_num", $link))) showerror();
$season_one = 1;
$season_two = 2;
echo "Season 1<br>";
while ($row = mysql_fetch_array($result))
{
if(season_num !== $season_one){
echo "Episode {$row["esp_num"]}<br>";
echo "Title {$row["title"]}<br>";
echo "Description {$row["descrip"]}<br>";
}}
echo "Season 2<br>";
while ($row = mysql_fetch_array($result))
{
if(season_num !== $season_two){
echo "Episode {$row["esp_num"]}<br>";
echo "Title {$row["title"]}<br>";
echo "Description {$row["descrip"]}<br>";
}}
?>

Example
Season 1
Eps 1
Eps 2
Eps 3
Season 2
Eps 1
Eps 2
Ect..

Any code help would be greatly appreciated.

Answer 1

You didn't need to hard-code season values like 1 and 2. Just use the nicely sorted repeating values from the rows and detect when the value changes from one row to the next. With such a generic test, as shown below, you can use one loop and avoid repeated code.

Also note that you seemed to be using the wrong operator, !== the not-identical operator to do an equality test.

if (!($result = mysql_query("SELECT season_num, sub_season_num, esp_num, title, descrip FROM season ORDER BY season_num, sub_season_num", $link))) showerror();
$previous_season = 0; // start with nonsense value to force a heading
while ($row = mysql_fetch_array($result))
{
 $season = $row["season_num"]
 // Ignoring subseason in this version.
 // Decide whether to match both season and sub_season and
 // print both in a new header, complicating this `if` statement
 // OR have a separate, similar `if` statement and separate (sub)header
 // for subseasons. In the latter case, be sure to
 // reset previous_subseason to a nonsense value whenever season changes
 // Otherwise, a subseason header will be missing if a season happens to end
 // on the same subseason number (like 1) that the next season begins with.
 if ($season != $previous_season) {
 $echo "Season $season<br>";
 $previous_season = $season;
 }
 echo "Episode {$row["esp_num"]}<br>";
 echo "Title {$row["title"]}<br>";
 echo "Description {$row["descrip"]}<br>";
}

• webmaster alex l: if ($season != $previous_season) { $echo "Season $season<br>"; $previous_season = $season; } flagged a syntax error. Im new to loops and my resin for the sub_season_num is that some episodes have a value of "TBA" but I need something to hold them in the correct temp order within the list
• webmaster alex l: I corrected my naming convention by renamed sub_season_num to temp_eps_num.
• webmaster alex l: I think i got it to work by adding a ; to the end of $season = $row["season_num"]
• webmaster alex l: @5am local everything seems to be working. Im glad, hopefully soon loops and ifs will make more seance to me :) Thank you for your help.

Answer 2

Try This Structure this is really helpful to you....

SELECT 
s.`season_num`,
s.`sub_season_num`,
GROUP_CONCAT(s.`esp_num`)AS esp_num
s.`title`,
s.`descrip`
FROM season s ORDER BY s.`season_num` DESC 
<?php 
$loop=array();
$i=0;
foreach($sql as $data){
 $loop[$i]['season_num']=$data->season_num;
 $esp_number=explode(",",$data->esp_num);
 $countesp_number = ($esp_number);
 for($j=0;$j<=$countesp_number;$j++){
 $loop[$i]['esp_num_'.$j]=$esp[$j];
 }
 $i++;
}
print_r($loop);
?>