Pagination script - getting data from two tables

Question

I have a pagination script, which can be seen here: http://www.automotori6282.tk/phpsandbox/

This is the source code:

http://pastebin.com/raw.php?i=63dCvfxD (PmcPagination.php)

http://pastebin.com/raw.php?i=3k4nqwRB (demo.php, the page with the code in)

and index.php for http://www.automotori6282.tk/phpsandbox/ simply uses PHP's include() function to show demo.php

What I'm trying to do is extract from two tables for the pagination.

This is my current database structure:

---

programme   varchar(255)    
channel     varchar(255)    
episode     varchar(255)
series  varchar(255)            
setreminder     varchar(255)    

However, I have the episodes stored in a separate table, and would like to make a query which extracts from both epdata167 and episodes table (the episodes table only has episode, seriesno, episodeno and description as its fields).

One error I made in my pagination script shows here:

TV Show showing on Channel 1 August 3rd - 12:30pm "" Set Reminder
TV Show showing on Channel 1 August 3rd - 8:30pm "Episode 1" Set Reminder

and it still remains long after the event has happened.

This is how it should work:

 TV Show showing on Channel 1 August 3rd - 12:30pm  
 TV Show showing on Channel 1 August 3rd - 2:45pm Set Reminder
TV Show showing on Channel 1 August 3rd - 8:30pm "Episode 1" Set Reminder

Notice how the Set Reminder field doesn't show when the event happens, and there's no quotation marks if an episode isn't selected.

(The setreminder field is basically a link that a user would click on to send a reminder to them saying "TV show is airing on Channel 1 August 3rd - 8:30pm Episode 1" or "TV show is airing today at 8:30pm Episode 1". However, it would not display for the show that is airing right now, it would display like this:

    TV Show showing on Channel 1 August 3rd - 12:30pm 

)

This is the pagination code causing trouble:

$result = mysql_query("SELECT programme, channel, airdate, expiration, episode, series, setreminder FROM epdata167 where airdate > curdate() GROUP by airdate");

and here is the part of the pagination code I edited to try and use an ifelse statement but wasn't sure what to do:

class paginationData {
private $program;
private $channel;
private $airdate;
private $exiration;
private $episode;
private $series;
private $setReminder;

public function __construct($program, $channel, $airdate, $expiration, $episode, $setReminder)
{
    $this->program = $program;
    $this->channel = $channel;
  $this->airdate = strtotime($airdate);
    $this->expiration = $expiration;
    $this->episode = $episode;
    $this->setReminder = $setReminder;
}

//This function shows the data
public function display()
{
    if(date('Y-m-d') == date('Y-m-d', $this->airdate)) $dateFormat = 'g:ia';
    elseif(date('Y') == date('Y', $this->airdate)) $dateFormat = 'F jS - g:ia';
    else $dateFormat = 'F jS, Y - g:ia';

    echo '<tr>'."\n".
         '  <td><strong>'.$this->program.'</strong></td>'."\n".
         '  <td>showing on '.$this->channel.'</td>'."\n".
         '  <td>'.date($dateFormat, $this->airdate).'</td>'."\n".
         '  <td><b>"'.$this->episode.'"</b><br></td>'. "\n".
         '  <td>'.$this->setReminder.'</td>'."\n".
         '</tr>'."\n";
}

}

Basically, I'm having trouble trying to get data as this in my project:

     TV Show showing on Channel 1 August 3rd - 12:30pm  
 TV Show showing on Channel 1 August 3rd - 2:45pm Set Reminder
TV Show showing on Channel 1 August 3rd - 8:30pm "Episode 1"
                                                  Series 1, episode 1 Set Reminder

(that's if the episode is part of a series with a series number)

Not sure what JOIN to use and where, all help is appreciated trying to get this to work since I've got the pagination working; just trying to get the finer details working!

I'm not asking for people to do it for me, I've been learning myself, and have done my research.

Sorry if this seems a bit long, but I hope I've explained it all.

Thanks.

Answer 1

You need to use a UNION to get this to work.