suggestions for tv programming schedule

[kevinritt]

Hi,

I have a site that is for a local cable station. I upload the schedule in a pdf file each week. Is there a way to set up a way for them to load the programs by day and date? I know I can create a database with days of the week, but it's the hours for each program that's messing me up. If you look here http://saugustv.org/index.php?page=channel8 you'll see how the hours stagger and how they display the schedule. I'm just looking for suggestions/ ideas. Thanks

[kevinritt]

Hi,

I have a site that is for a local cable station. I upload the schedule in a pdf file each week. Is there a way to set up a way for them to load the programs by day and date? I know I can create a database with days of the week, but it's the hours for each program that's messing me up. If you look here http://saugustv.org/index.php?page=channel8 you'll see how the hours stagger and how they display the schedule. I'm just looking for suggestions/ ideas. Thanks

Link fixed

[ignace]

I assume:

- Shows have a start and an end and between shows their is a commercial (shows_start, shows_end as the time a show takes greatly varies plus commercials).

- A schedule shows all shows playing during the week

 

CREATE TABLE shows (
  shows_id INTEGER NOT NULL AUTO_INCREMENT,
  shows_title VARCHAR(32),
  shows_description TEXT,
  shows_date DATE,
  shows_start TIME,
  shows_end TIME,
  PRIMARY KEY (shows_id)
);

 

Query:

SELECT *
FROM shows
WHERE shows_date BETWEEN $monday AND $sunday
ORDER BY shows_date

 

Another possibility is using weeks instead of dates:

CREATE TABLE shows (
  shows_id INTEGER NOT NULL AUTO_INCREMENT,
  shows_title VARCHAR(32),
  shows_description TEXT,
  shows_week TINYINT,
  shows_date DATE,
  shows_start TIME,
  shows_end TIME,
  PRIMARY KEY (shows_id)
);

 

Query:

SELECT *
FROM shows
WHERE shows_week = $week
ORDER BY shows_date

 

PHP:

$week = date('W');

[ignace]

You can extend this further by adding a shows_type which references the primary key in the shows_types table and a shows_rating references the primary key in the shows_ratings table (rating applied by your internal reviewing crew)

 

CREATE TABLE shows_types (
  shows_types_id TINYINT NOT NULL AUTO_INCREMENT, -- increase (to SMALLINT) if you need more then 127 different types
  shows_types_name VARCHAR(32),
  PRIMARY KEY (shows_types_id)
);

CREATE TABLE shows_ratings (
  shows_ratings_shows_id INTEGER NOT NULL,
  shows_ratings_rating TINYINT, -- increase (to SMALLINT) if you need more then 127 stars
  shows_ratings_count SMALLINT, -- should suffice unless you receive more then 64k votes
  UNIQUE KEY (shows_ratings_rating),
  PRIMARY KEY (shows_ratings_shows_id)
);

 

Queries:

SELECT *
FROM shows
LEFT JOIN shows_types ON shows_type = shows_types_id

 

INSERT INTO shows_ratings (shows_ratings_rating, shows_rating_count) VALUES ($rating, 1) ON DUPLICATE KEY shows_rating_count = shows_rating_count + 1

[kevinritt]

Thanks Ignace. Yes, basically the shows are entered weekly by day, date. For example:

Monday Aug 10                  Tuesday Aug 11

8-9:  Some show                                          8-9:  Some show

9-10:30: Another show                                  9-10: Another show

10:30-11:00:  Some show                              10-11:  Some show

 

and so on ...

[ignace]

Thanks Ignace. Yes, basically the shows are entered weekly by day, date. For example:

Monday Aug 10                  Tuesday Aug 11

8-9:  Some show                                          8-9:  Some show

9-10:30: Another show                                  9-10: Another show

10:30-11:00:  Some show                              10-11:  Some show

 

and so on ...

 

I think this query will suffice:

 

SELECT *
FROM shows
WHERE shows_week = $week
ORDER BY shows_date, shows_start

 

Result has this order:

Monday Aug 10, 00:30u Show#1
Monday Aug 10, 1:50u Show#2
Monday Aug 10, 3:30u Show#3
...
Tuesday Aug 11, ...
...

 

 

[ignace]

Go over each and every row and print the new day when shows_date changes so you get something like:

 

Monday Aug 10

00:00-01:20: You should be sleeping

01:30-02:50: Still 'up?

..

 

Tuesday Aug 11

00:00-01:20: Insomnia trouble!

..

 

Add CSS magic to align each day next to each other don't use a table keep it as flexible as possible CSS helps you to style it like you want your visitors to view it.