We help IT Professionals succeed at work.

PHP TV Schedule

mattibutt
mattibutt asked
on
Medium Priority
795 Views
Last Modified: 2012-05-06
hi
i want to create TV schedule which will display 48 shows each show will be half an hour i have a table for shows
i have something similar in c sharp which uses stored procedure to create that schedule interface
i dont know how can i acheive this in php
CREATE TABLE `shows` (
  `id` int(10) NOT NULL auto_increment,
  `Name` varchar(45) character set latin1 collate latin1_bin NOT NULL default '',
  `Description` varchar(1000) collate latin1_german2_ci NOT NULL,
  `Genre` varchar(50) collate latin1_german2_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=31 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=31 ;

Open in new window

Comment
Watch Question

Top Expert 2008

Commented:
You have not provided enough information for us to be able to help you. What is your question? "achieve this in php" is meaningless when we don't know what "this" is...

Author

Commented:
i will try to provide details for schedule, the schedule I want to create will need to have following functions
48 shows slots load for each day
Each of the list boxes contains the shows as I have provided the show table structure I have also created the interface which allows me to add/edit delete show
i have attached an image grab which is my dot net application.
now as you asked me the first thing "what is your question"
my first aim is to create the interface similar to picture i have attached which loads show name from the show table
php-schedule-design.bmp

Author

Commented:
i have created the slot table which may be use to store shows id and load in the list box using , still struggling to think in php
CREATE TABLE `schedulex` (
  `schedule_id` int(11) NOT NULL auto_increment,
  `slot_1` int(11) NOT NULL default '0',
  `slot_2` int(11) NOT NULL default '0',
  `slot_3` int(11) NOT NULL default '0',
  `slot_4` int(11) NOT NULL default '0',
  `slot_5` int(11) NOT NULL default '0',
  `slot_6` int(11) NOT NULL default '0',
  `slot_7` int(11) NOT NULL default '0',
  `slot_8` int(11) NOT NULL default '0',
  `slot_9` int(11) NOT NULL default '0',
  `slot_10` int(11) NOT NULL default '0',
  `slot_11` int(11) NOT NULL default '0',
  `slot_12` int(11) NOT NULL default '0',
  `slot_13` int(11) NOT NULL default '0',
  `slot_14` int(11) NOT NULL default '0',
  `slot_15` int(11) NOT NULL default '0',
  `slot_16` int(11) NOT NULL default '0',
  `slot_17` int(11) NOT NULL default '0',
  `slot_18` int(11) NOT NULL default '0',
  `slot_19` int(11) NOT NULL default '0',
  `slot_20` int(11) NOT NULL default '0',
  `slot_21` int(11) NOT NULL default '0',
  `slot_22` int(11) NOT NULL default '0',
  `slot_23` int(11) NOT NULL default '0',
  `slot_24` int(11) NOT NULL default '0',
  `slot_25` int(11) NOT NULL default '0',
  `slot_26` int(11) NOT NULL default '0',
  `slot_27` int(11) NOT NULL default '0',
  `slot_28` int(11) NOT NULL default '0',
  `slot_29` int(11) NOT NULL default '0',
  `slot_30` int(11) NOT NULL default '0',
  `slot_31` int(11) NOT NULL default '0',
  `slot_32` int(11) NOT NULL default '0',
  `slot_33` int(11) NOT NULL default '0',
  `slot_34` int(11) NOT NULL default '0',
  `slot_35` int(11) NOT NULL default '0',
  `slot_36` int(11) NOT NULL default '0',
  `slot_37` int(11) NOT NULL default '0',
  `slot_38` int(11) NOT NULL default '0',
  `slot_39` int(11) NOT NULL default '0',
  `slot_40` int(11) NOT NULL default '0',
  `slot_41` int(11) NOT NULL default '0',
  `slot_42` int(11) NOT NULL default '0',
  `slot_43` int(11) NOT NULL default '0',
  `slot_44` int(11) NOT NULL default '0',
  `slot_45` int(11) NOT NULL default '0',
  `slot_46` int(11) NOT NULL default '0',
  `slot_47` int(11) NOT NULL default '0',
  `slot_48` int(11) NOT NULL default '0',
  PRIMARY KEY  (`schedule_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=2 ;

Open in new window

Top Expert 2008

Commented:
Ok, so you want a three column table with time slots, having one slot for every 30 minutes, starting 04:00 in upper left corner. Each table cell should contain a time in format HH:MM (24 hour clock) and a dropdown containing the 'name' column from the 'show' table in your database. You want this in PHP code, and HTML output.

In what order should the shows be listed?

This should be split in two tasks: getting the shows from the database and making a dropdown of it, and creating the schedule table, repeating the dropdown 48 times.

The below code creates the dropdown. I will post the schedule table code shortly, please tell me if I have missinterpreted your question in any way.
mysql_connect('localhost','user','pass') or die('could not connect to the database server');
mysql_select_db('dbname') or die('could not select database');
$res = mysql_query('select id,name from shows');  # order by ?
if(!$res) die('SQL error: '.mysql_error());       # report error, if any
$dropdown = '<option>-- Please Select --</option>';
while($row = mysql_fetch_assoc($res))
  $dropdown .= '<option value="'.$row['id'].'">'.
               htmlentities($row['name']).'</option>';

Open in new window

Top Expert 2008

Commented:
Sorry, I did not se the schedulex table until now... this is not good db design. It should probably be something like this:

create table shedulex(
  `schedule_id` int(11) NOT NULL,
  `slot` tinyint(11) NOT NULL default '0',
  `show_id` int(11) NOT NULL default '0',
  primary key(`schedule_id`,`slot`)
)

You would have 48 rows for one "schedule". How is this related to your application? Will there be one "schedule" for each user? Or ust one global schedule?

Author

Commented:
its global schedule

Author

Commented:
i think so far you have understood
Top Expert 2008
Commented:
If there is only one saved schedule, the schedule_id column in the schedulex table seems redundant.

I assume each dropdown will have a pre-defined selection from the schedulex table, they should not all display "-- Please Select --" like in the picture above?

Code for producing the schedule html table:
$starttime = 0 - date('Z');     # date('Z') is to adjust for timezone
$endtime = 8*60*60 - date('Z');
 
$res = mysql_query('select * from schedulex');  # load schedule
if(!$res) die('SQL error: '.mysql_error());
$schedules = array();
while($sch = mysql_fetch_assoc($res))
  $schedules[$sch['slot']] = $sch['show_id'];  # store in array
 
function showcell($slot,$time) {
  global $dropdown, $schedules;  # show dropdown & schedulex data
  if(isset($schedules[$slot])) {
    $show_id = $schedules[$slot];
    $val = ' value="'.$show_id.'"';
    $thisdropdown = str_replace($val.'>',$val.' selected="selected">',$dropdown); 
  } else 
    $thisdropdown = $dropdown;
  return date('H:i',$time).' '.
         '<select name="slot_'.$slot.'">'.$thisdropdown.'</select>';
}
 
$slot = 1;  # first slot
echo '<table>';
for($i=$starttime;$i<$endtime;$i+=30*60) { 
  echo '<tr>'.
       '<td>'.showcell($slot,$i+(4*60*60)).'</td>'.
       '<td>'.showcell($slot+16,$i+(12*60*60)).'</td>'.
       '<td>'.showcell($slot+32,$i+(20*60*60)).'</td>'.
       '</tr>';
  $slot += 1;
}
echo '</table>';

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
sorry for getting back to you late thanks for your help  i will try these codes and get back to you

Author

Commented:
thanks buddy it did let me create the basic prototype
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.