title: finding next/previous tv show from MySQL database
body: <p>I've become rather stuck with a situation I have. I have a database with fields id, showid, season, episode and some other arbitrary data. I have a php page to display information about episodes of shows which works by having the id as a get variable. What I wish to achieve is to have a link to the next episode and previous episode of this tv program however the shows aren't necessarily in order and so simply incrementing or decrementing the id will not help.
  So I would fetch the episodes and store then in <code>$rows</code> with the following <code>MySQL</code> query:</p>
  
  <p><code>SELECT id FROM episodes WHERE showid = $id ORDER BY season, episode;</code></p>
  
  <p>Now without having to loop through the array (which could be rather large for some shows) is there an easy way for me to find the next and previous episode? I'm sure there is I just can't think of it.</p>
  
  <p><strong>Edit:</strong>
  I have been thinking about this myself and nothing all that helpful has come through yet (although thanks for the help, its much appreciated). What I have thought of is to make a query to see if a higher episode exists of the same season, if not make another query to find the lowest episode of then next lowest season - or something along those lines - and then repeat for previous. What do you think?</p>
  
  <p><strong>Edit 2:</strong>
  I thought I would post my final code for future reference.
  Here I find the id's of the nex and previous shows (if they exist).</p>
  
  <p>Find the next episode:</p>
  
  <pre><code>try {
      // First check for a show of the same season but the next lowest episode number
      $stmt = $dbh-&gt;prepare("SELECT id FROM episodes WHERE showid = :show AND season = :s AND episode &gt; :e ORDER BY episode LIMIT 1;");
      $stmt-&gt;execute($pars);
      $rows = $stmt-&gt;fetchAll();
      if(count($rows) &gt; 0) { // If there is one this is our next id
          $nextid = $rows[0]['id'];
      } else {
          // Otherwise check for an episode of a higher season number
          $stmt = $dbh-&gt;prepare("SELECT id FROM episodes WHERE showid = :show AND season &gt; :s ORDER BY season, episode LIMIT 1;");
          $stmt-&gt;execute($pars);
          $rows = $stmt-&gt;fetchAll();
          if(count($rows) &gt; 0) { // If there is one this is our next id.
              $nextid = $rows[0]['id'];
          } // Otherwise no next id is set and so no next link will be created.
      }
  } catch(PDOException $e) {
      die("Error: " . $e-&gt;getMessage());
  }
  </code></pre>
  
  <p>Find the previous episode:</p>
  
  <pre><code>try {
      // First check for an episode in same season of the next highest episode number.
      $stmt = $dbh-&gt;prepare("SELECT id FROM episodes WHERE showid = :show AND season = :s AND episode &lt; :e ORDER BY episode DESC LIMIT 1;");
      $stmt-&gt;execute($pars);
      $rows = $stmt-&gt;fetchAll();
      if(count($rows) &gt; 0) { // If one is found then this is our prev id.
          $previd = $rows[0]['id'];
      } else {
          // Otherwise check for an episode of a lower season number.
          $stmt = $dbh-&gt;prepare("SELECT id FROM episodes WHERE showid = :show AND season &lt; :s ORDER BY season DESC, episode DESC LIMIT 1;");
          $stmt-&gt;execute($pars);
          $rows = $stmt-&gt;fetchAll();
          if(count($rows) &gt; 0) { // If there is one this is our prev id.
              $previd = $rows[0]['id'];
          } // Otherwise no prev id is set and so no prev link will be created.
      }
  } catch(PDOException $e) {
      die("Error: " . $e-&gt;getMessage());
  }
  </code></pre>
  
  <p>Then I have the following links:</p>
  
  <pre><code>if($previd) echo "&lt;a href=\"/tv/view/" . $previd . "/\"&gt;Previous Episode&lt;/a&gt;";
  if($nextid) echo "&lt;a href=\"/tv/view/" . $nextid . "/\"&gt;Next Episode&lt;/a&gt;";
  </code></pre>