0

This is my code, as seen here:
http://pcafansa6.hosticks.com/episode1.php

<?php
/***********************************
 * PhpMyCoder Paginator            *
 * Created By PhpMyCoder           *
 * 2010 PhpMyCoder                 *
 * ------------------------------- *
 * You may use this code as long   *
 * as this notice stays intact and *
 * the proper credit is given to   *
 * the author.                     *
 ***********************************/
?> 
	<head>
    	<title> Pagination Test - Created By PhpMyCoder</title>
        <style type="text/css">
			#nav { font: normal 13px/14px Arial, Helvetica, sans-serif; margin: 2px 0; }
			#nav a { background: #EEE; border: 1px solid #DDD; color: #000080; padding: 1px 7px; text-decoration: none; }
			#nav strong { background: #000080; border: 1px solid #DDD; color: #FFF; font-weight: normal; padding: 1px 7px; }
			#nav span { background: #FFF; border: 1px solid #DDD; color: #999; padding: 1px 7px; }
		</style>
    </head>
    	<?php
			//Require the file that contains the required classes
			include("pmcPagination.php");
				
			//PhpMyCoder Paginator
			$paginator = new pmcPagination(20, "page");
			
			//Connect to the database
			mysql_connect("localhost","root","REDACTED");
			//Select DB
			mysql_select_db("mytvguide");

			//Select only results for today and future
			$result = mysql_query("SELECT * FROM epdata WHERE airdate >= CURTIME() order by expiration ASC;");

			//You can also add reuslts to paginate here
			mysql_data_seek($queryresult,0) ; while($row = mysql_fetch_array($result))
			{
				$paginator->add(new paginationData($row['programme'],
												   $row['channel'],
												   $row['airdate'],
												   $row['expiration'],
												   $row['episode'],
												   $row['setreminder']));
			}
		?>
        <?php
			//Show the paginated results
			$paginator->paginate ();
		?><? include("pca-footer1.php"); ?>
        <?php
			//Show the navigation
			$paginator->navigation();						
		?>

I can paginate, but I'm trying to get data from 2 different databases - namely mytvguide which stores info on what date the programme airs, and episodes which contains a link to a page on the episodes.

Also, what I am trying to do is see if I can get all records which are episodes to be formatted like this:

TV Show showing on Channel1 May 4th - 2:00pm "Celebrity Gossip" Set Reminder

TV Show showing on Channel1 May 4th - 2:30pm Set Reminder

with the episodes not having data being shown as blank, episodes with titles shown as in "quotation marks" like above.

Any help would be much appreciated, it will help my project a lot!

3
Contributors
11
Replies
12
Views
7 Years
Discussion Span
Last Post by whitestream6
0

I don't want to select every record in the episodes table, just selected records, and have them output like this: e.g.:

Police, Camera, Action! showing on Channel2 May 5th - 9:25am "Speed" Set Reminder
Police, Camera, Action! showing on Channel2 May 5th - 9:55am "Nowhere to Hide" Set Reminder
Police, Camera, Action! showing on Channel2 May 5th - 1:25pm "Speed" Set Reminder
Police, Camera, Action! showing on Channel2 May 5th - 1:55pm "What Drives You Mad" Set Reminder
Police, Camera, Action! showing on Channel2 May 5th - 7:00pm "Safety Last" Set Reminder
Police, Camera, Action! showing on Channel2 May 5th - 7:30pm "Motorway Madness" Set Reminder
Police, Camera, Action! showing on Channel2 May 6th - 1:30pm Set Reminder
Police, Camera, Action! showing on Channel2 May 6th - 6:30pm Set Reminder
Police, Camera, Action! showing on Channel2 May 7th - 7:00pm "In The Driving Seat" Set Reminder

Sometimes a record from the table episodes will be repeated multiple times, other times it won't... this is causing my PHP script to have some issues!

I've tried multiple times and not quite got there, but will do hopefully.

0

Please can someone help, this would be much appreciated.
Still tried to resolve it myself, with no luck. ;)

0

Sometimes a record from the table episodes will be repeated multiple times, other times it won't... this is causing my PHP script to have some issues!

Maybe start with the database duplication?
You don't show the code that may cause that.

0

I am currently only extracting from the epdata table but want to extract from multiple tables; just not sure how to get this code to work properly.

0

The quick answer is if the tables are all inside the currently selected database, you just run more queries and create other arrays just like the single query you already have.

0

I am currently only extracting from the epdata table but want to extract from multiple tables; just not sure how to get this code to work properly.

Multiple tables? or Multiple databases? You said tables here but databases in your first post.

If it's just multiple tables, the correct SELECT should fit your needs and not be too difficult.

Show your table designs, a sample of the data and an example of what you are looking to produce once combining this data.

0

It's multiple tables in one database.
This is one problem:

#
//You can also add reuslts to paginate here
#
mysql_data_seek($queryresult,0) ; while($row = mysql_fetch_array($result))
#
{
#
$paginator->add(new paginationData($row['programme'],
#
$row['channel'],
#
$row['airdate'],
#
$row['expiration'],
#
$row['episode'],
#
$row['setreminder']));
#
}
#
?>

Since both tables have a table called "episode" which one will it recognize?

Edited by whitestream6: n/a

0

Easier if you show your tables.

Do you need to see what is in the episode column from both tables? Meaning, they have different data, or are they the same?

If they are the same, just select one of them in you query

select a.programme, a.channel, a.airdate, a.expiration, a.episode, a.setreminder, b.col1, b.col2, b.etc
from epdata a, othertable b
where a.keyfield = b.keyfield

if they are different, alias one or both of them like

select a.programme, a.channel, a.airdate, a.expiration, a.episode 'first_episode', b.episode 'second_episode', a.setreminder, b.col1, b.col2, b.etc
from epdata a, othertable b
where a.keyfield = b.keyfield

where you will then refer to them as

$row['first_episode']
$row['second_episode']
0

This is the SQL code for the episodes table:

-- phpMyAdmin SQL Dump
-- version 2.11.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: 
-- Server version: 5.0.45
-- PHP Version: 5.2.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `mytvguide`
--

-- --------------------------------------------------------

--
-- Table structure for table `episodes`
--

CREATE TABLE IF NOT EXISTS `episodes` (
  `ID` int(11) NOT NULL auto_increment,
  `episode` varchar(255) NOT NULL default '<b>"<a href="/lib/episode/714829">Police Stop!</a>"</b>',
  PRIMARY KEY  (`ID`),
  FULLTEXT KEY `episode` (`episode`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `episodes`
--

INSERT INTO `episodes` (`ID`, `episode`) VALUES
(1, '<b>"<a href="/lib/episode/714829">Round the Bend</a>"</b>'),
(2, '<b>"<a href="/lib/episode/714839">Life in the Fast Lane</a>"</b>'),
(3, '<b>"<a href="/lib/episode/800802">Under the Influence</a>"</b>'),
(4, '<b>"<a href="/lib/episode/714869">Crime Cars</a>"</b>'),
(5, '<b>"<a href="/lib/episode/714868">Bad Influences</a>"</b>'),
(6, '<b>"<a href="/lib/episode/714873">Under Surveillance</a>"</b>'),
(7, '<b>"<a href="/lib/episode/794800">Race</a>"</b>'),
(8, '<b>"<a href="/lib/episode/714867">Death Wish Drivers</a>"</b>');

and for the main table mytvguide:

-- phpMyAdmin SQL Dump
-- version 2.11.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: 
-- Server version: 5.0.45
-- PHP Version: 5.2.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `mytvguide`
--

-- --------------------------------------------------------

--
-- Table structure for table `mytvguide`
--

CREATE TABLE IF NOT EXISTS `mytvguide` (
  `programme` varchar(255) NOT NULL default '<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>',
  `channel` varchar(255) NOT NULL default '<a href="/lib/channel/ITV4"><i>ITV4</i></a>',
  `airdate` datetime NOT NULL,
  `expiration` datetime NOT NULL,
  `episode` varchar(255) NOT NULL default '<b>"<a href="/lib/episode/714829">Round the Bend</a>"</b>',
  `setreminder` varchar(255) NOT NULL default '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `mytvguide`
--

INSERT INTO `pcaepdata14` (`programme`, `channel`, `airdate`, `expiration`, `episode`, `setreminder`) VALUES
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-04-25 16:30:00', '2010-04-25 17:00:00', '<b>"<a href="/lib/episode/714829">Round the Bend</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'),
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-04-26 13:25:00', '2010-04-26 13:55:00', '<b>"<a href="/lib/episode/714829">Round the Bend</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'),
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-04-26 13:55:00', '2010-04-26 14:25:00', '<b>"<a href="/lib/episode/716632">Motorway Manners</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'),
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-04-26 19:30:00', '2010-04-26 20:30:00', '<b>"<a href="/lib/episode/714566">The Off Roaders</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'),
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-04-27 09:25:00', '2010-04-27 09:55:00', '<b>"<a href="/lib/episode/725685">Safety Last</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'),
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-04-27 09:55:00', '2010-04-27 10:25:00', '<b>"<a href="/lib/episode/716621">Tales of the Unexpected</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'),
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-04-27 13:25:00', '2010-04-27 13:55:00', '<b>"<a href="/lib/episode/715323">The Off Roaders</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'),
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-04-27 13:55:00', '2010-04-27 14:25:00', '<b>"<a href="/lib/episode/714662">Caught on Camera</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'),
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-04-27 19:30:00', '2010-04-27 20:00:00', '<b>"<a href="/lib/episode/715629">Rural Roads</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'),
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-05-03 12:25:00', '2010-05-03 12:55:00', '<b>"<a href="/lib/episode/714829">Unroadworthy Vehicles</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'),
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-05-03 18:00:00', '2010-05-03 18:30:00', '<b>"<a href="/lib/episode/714829">Unroadworthy Vehicles</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'),
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-05-03 18:30:00', '2010-05-03 19:00:00', '<b>"<a href="/lib/episode/719883">Crime Cars</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'),
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-05-04 09:25:00', '2010-05-04 09:55:00', '<b>"<a href="/lib/episode/714729">Getting Their Man</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'),
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-05-04 09:55:00', '2010-05-04 10:25:00', '<b>"<a href="/lib/episode/714639">Race</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>'),
('<b><a href=''lib/programmes/policecam1''>Police, Camera, Action!</a></b>', '<a href="/lib/channel/ITV4"><i>ITV4</i></a>', '2010-05-04 18:30:00', '2010-05-04 19:00:00', '<b>"<a href="/lib/episode/715463">Under the Influence</a>"</b>', '<img src=''email-phone-alert.gif'' width=22 height=16> <a href="setreminder.php" alt=''Set a reminder'' target=''_blank''>Set Reminder</a>');

Hope this helps. Apologies for the length of it.

0

I don't see the purpose of having the episodes table. The episodes columns in the 2 tables contain the same thing and there isn't anything else of use in there.

The episodes columns would be a poor choice to join on as well the way they stand.

0

The episodes column in mytvguide table is not meant to be there; it's leftover from an early design. I'm removing it shortly.

I'm trying to get my PHP so that it puts any selected episodes from the episodes table in " " marks.

Thank you for your advice; any advice on making this work is appreciated. That means I'll have to redesign the episodes table.

Edited by whitestream6: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.