[PHP] Associate different data

Question

I will try to be as clear as possible because I can't get anybody to help me around,

I am trying to associate some data from a 'videos' table with their respective ID.

Lets say, I have column ID, title, serie, season, episode.

I am getting my data :

    <?
$result = mysql_query("SELECT * FROM videos WHERE serie = '".$row['serie']."' AND season = '".$row['season']."'");
$total_rows = mysql_num_rows($result);
       ?>

(that is in the page where you see the video itself)

So now I can get the number of episodes from a serie and season.

What I'm trying to do is have a link for the next episode, and aa link for the previous one. In the URL I am working with the id, so http://website.com/view/id/'video id here'/

So how can I get the ID of the following and previous episodes of the same season AND serie?

Help will be much appreciated!

The easiest thing I thought of is

<?=$row['id'] + 1?>
<?=$row['id'] - 1?>

But the thing is that it's mixed videos, so it wont work 100%

Answer 1

If you do:

$results = mysql_query($sql);

(where $sql is your mentioned select statement)

Then the $results array will contain all results of that series/season.

So you can access it via:

$previous = $results[$n - 1];
$next = $results[$n + 1];

$previous_id = $previous['id'];
$next_id = $next['id'];

Note that you'll of course need to check that $n - 1 isn't less than zero, and ($n + 1) isn't greater than your $total_rows.

Answer 2

Here is what I tried, I guess it is what you meant,

All that it's doing is put the previous episode, so I get /view/id/3/ (if the episode i am watching is 4)

    <?
                 $result = mysql_query("SELECT * FROM videos WHERE serie = '".$row['serie']."' AND season = '".$row['season']."'");
$total_rows = mysql_num_rows($result);
                 $previous = $results[$n - 1];
                 $next = $results[$n + 1];

                 $previous_id = $previous['id'];
                 $next_id = $next['id'];

                ?>

                <? if($row['episode'] == 1) { ?>
                <td align="center" width="33%"></td>
                <? } else { ?>
                <td align="center" width="33%"><a href="view/id/<?=$previous_id?>/"><img src="images/previous.gif" srcover="images/previoush.gif" alt="Previous Video" border="0" /></a></td><? } ?>

That is only for the previous one,

Sorry I had to post like this, if not it wouldnt have appeared correctly,

(Correct me if I'm wrong with the code)

And thanks for the reply

Answer 3

I think you've taken my example too literally. I'll post a complete example in the morning (I'm just replying from my iPhone at the mo). For some reason it wouldn't let me add a comment to your last reply, so sorry this is as an answer.

[edit] here's a more complete example:

<?php

$result = mysql_query("SELECT * FROM videos WHERE series='$series' AND season = '$season'");

$last_row = null;
$next_row = null;

$current_id = $_GET['id'];

while($row = mysql_fetch_assoc($result)) {
    if($current_id == $row['id']) {
        $next_row = mysql_fetch_assoc($result);
        break;
    }

    $last_row = $row;
}

if($last_row != null) {
    echo "<a href='?id={$last_row['id']}'>Prev</><br/>\n";
}

if($next_row != null) {
    echo "<a href='?id={$next_row['id']}'>Next</><br/>\n";
}

?>

A few things to note:

• This is untested.
• In your initial example, I didn't know where you were getting the $row['serie'] and $row['season'], so in my example, I've just used the variables $series and $season. You'll have to fill these in with what you want the current result set to be filtered by.
• For my example, the URL format uses the GET layout (ie. ?id=).