[SOLVED] Help for 'where' clause

[desibhupi]

Hello friends,

 

I m making a TV Schedule Script. I have a case that a program is repeating on the same day. I m using the following code but it is showing that record only once. please help

 

$sql = "select program.*, repetitive_program.* from program, repetitive_program where ";
$sql .= "(program.weekday like '%$day%' and program.repeat='w') or ";
$sql .= "(program.weekday like '%$day%' and program.repeat='a' and program.alt_repeat='$week') or ";
$sql .= "(program.repeat='n' and program.special_date='$date')or ";
$sql .= "(repetitive_program.pid = program.pid and repetitive_program.r_timing != program.timing) ";
$sql .= " order by program.timing, repetitive_program.r_timing";

 

below is the schema for these two tables.

 

-- 
-- Table structure for table `program`
-- 

CREATE TABLE `program` (
  `pid` int(9) NOT NULL auto_increment,
  `cid` int(6) NOT NULL,
  `title` varchar(255) collate latin1_general_ci NOT NULL,
  `description` text collate latin1_general_ci NOT NULL,
  `image` varchar(255) collate latin1_general_ci NOT NULL,
  `clip` varchar(255) collate latin1_general_ci NOT NULL,
  `cast` text collate latin1_general_ci NOT NULL,
  `director` text collate latin1_general_ci NOT NULL,
  `producer` text collate latin1_general_ci NOT NULL,
  `weekday` varchar(255) collate latin1_general_ci NOT NULL,
  `timing` time default NULL,
  `alt_repeat` enum('e','o') collate latin1_general_ci default NULL,
  `repeat` enum('w','a','n') collate latin1_general_ci NOT NULL default 'w',
  `special_date` date default NULL,
  `completed` enum('y','n') collate latin1_general_ci NOT NULL default 'y',
  PRIMARY KEY  (`pid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=21 ;

-- 
-- Dumping data for table `program`
-- 

INSERT INTO `program` VALUES (10, 3, 'Comedy Show 2', 'fdf', '', '', '', '', '', 'Monday,Wednesday,Saturday', '00:30:00', NULL, 'w', NULL, 'y');
INSERT INTO `program` VALUES (11, 5, 'Jap Mann Satnam', 'Shabad Kirtan', '', '', '', '', '', 'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday', '04:00:00', NULL, 'w', NULL, 'y');
INSERT INTO `program` VALUES (12, 12, 'Games Competitions', 'Punjabi Khedaan', '', '', '', '', '', 'Monday', '16:30:00', 'o', 'a', '2007-05-27', 'y');
INSERT INTO `program` VALUES (16, 3, 'Movie Masala', 'bollywood', '', '', '', '', '', 'Tuesday', '22:30:00', '', 'w', '0000-00-00', 'y');
INSERT INTO `program` VALUES (15, 12, 'News show', 'compete', '', '', '', '', '', 'Monday', '12:30:00', 'e', 'a', '2007-05-20', 'y');
INSERT INTO `program` VALUES (17, 5, 'Talk Show', 'sdfdsvcxvdd', '', '', '', '', '', 'Sunday', '13:00:00', 'e', 'a', '2007-05-20', 'y');
INSERT INTO `program` VALUES (19, 2, 'Serialzz', '', '', '', '', '', '', '', '13:30:00', '', 'n', '2007-05-28', 'y');
INSERT INTO `program` VALUES (20, 10, 'India Rupee', 'kk', '', '', '', '', '', '', '22:30:00', '', 'n', '2007-05-28', 'y');

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

-- 
-- Table structure for table `repetitive_program`
-- 

CREATE TABLE `repetitive_program` (
  `rid` int(4) unsigned NOT NULL auto_increment,
  `pid` int(4) NOT NULL,
  `weekday` varchar(225) collate latin1_general_ci NOT NULL,
  `r_timing` time NOT NULL,
  PRIMARY KEY  (`rid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `repetitive_program`
-- 

INSERT INTO `repetitive_program` VALUES (1, 10, 'Monday,Wednesday,Saturday', '19:06:00');

[bubblegum.anarchy]

$sql = "select program.*, repetitive_program.* from program, repetitive_program where ";

$sql .= "(program.weekday like '%$day%' and program.repeat='w') or ";

$sql .= "(program.weekday like '%$day%' and program.repeat='a' and program.alt_repeat='$week') or ";

$sql .= "(program.repeat='n' and program.special_date='$date')or ";

$sql .= "(repetitive_program.pid = program.pid and repetitive_program.r_timing != program.timing) ";

$sql .= " order by program.timing, repetitive_program.r_timing";

 

What is the highlighted part of the query supposed to be doing?

[desibhupi]

$sql = "select program.*, repetitive_program.* from program, repetitive_program where ";

$sql .= "(program.weekday like '%$day%' and program.repeat='w') or ";

$sql .= "(program.weekday like '%$day%' and program.repeat='a' and program.alt_repeat='$week') or ";

$sql .= "(program.repeat='n' and program.special_date='$date')or ";

$sql .= "(repetitive_program.pid = program.pid and repetitive_program.r_timing != program.timing) ";

$sql .= " order by program.timing, repetitive_program.r_timing";

 

What is the highlighted part of the query supposed to be doing?

 

I want to show the Program Name that also exists (PID) in 'repetitive_program' but there is difference between their timings.

[bubblegum.anarchy]

Then change the last OR to an AND, and the parenthesis are not required..

[desibhupi]

I found the other way

 

Thanks for your support