[mythtvnz] TV One & BBC World listings

[Wade Maxfield]

Neil Henwood wrote:
> Hi All
> 
> Tonight was the night that I decided to get rid of my extra channel 
> which only has BBC world on it and only there due to TVNZ only listing 
> 'BBC World' in there program guide, and not the full BBC World listings.
> 
> Now I was originally going to post this as a somewhat complete solution, 
> but I have become unstuck and need some guidance from a MySQL guru that 
> I am sure will be on here.
> 
> Basically what works,  works Ok but is by far not a very elegant 
> solution due to my limited knowledge of SQL. It is by no means in any 
> way ready to actually be used as there are a couple of things that I 
> need help with to actually get it working.
> 
> 1)  I need to know how to loop the whole thing while there are still 
> results from this SQL (select * from program where chanid=1081 and title 
> ='BBC World')
> 2 In steps 4 & 5 I need to know how to update the start and finish time 
> for the BBC Shows so that they fit nicely into the TV One listings 
> rather than just overlapping. The start and end time are located in test 
> table as part of Step 2.
>     I was trying things like joins and the like but I haven't figured it 
> out as yet. Will try again later when my brain is less fried.
> 3)  I need to tidy up how I delete the existing 'BBC World' listing from 
> TV One in Step 6
>     Its NASTY but it works
> 4)  I have no idea as to how step 2 works, and I dont understand why the 
> 'group by chanid' line works in this case, but it does exactly what I 
> want it to do.
> 5)  I need to figure out a more generic way of defining what chanid that 
> it should use, rather than my hard coded values of TVOne=1081 and BBC=1090
> 
> 
> Please note everyone this is still in Alpha stage and has the 
> possibility of really breaking you system to a very large extent. IT 
> still does not run on my system properly. I was originally not going to 
> post the whole script yet, but thought it wise to fully understand what 
> I was doing rather than second guess.
> 
> If someone could give me some pointers to get this (initially steps 4 & 
> 5) sorted I will release it to the wider MythTVNZ users to that they can 
> all have nice populated listings :)
> 
> 
> Thanks
> 
> Neil
> 

I have something hacky that I've been using for a while, it doesn't try 
and tidy up the small gaps at the begining or end. It might have the 
answer to Step 6.


My chanids are 1001 One & 2093 BBC World


REPLACE INTO program
(chanid, starttime, endtime, title, subtitle, description, category, 
category_type, airdate, stars, previouslyshown, title_pronounce, stereo, 
subtitled, hdtv, closecaptioned, partnumber, parttotal, seriesid, 
originalairdate, showtype, colorcode, syndicatedepisodenumber, 
programid, manualid, generic, listingsource, first, last, audioprop, 
subtitletypes, videoprop)
SELECT
1001, new.starttime, new.endtime, new.title, new.subtitle, 
new.description, new.category, new.category_type, new.airdate, 
new.stars, new.previouslyshown, new.title_pronounce, new.stereo, 
new.subtitled, new.hdtv, new.closecaptioned, new.partnumber, 
new.parttotal, new.seriesid, new.originalairdate, new.showtype, 
new.colorcode, new.syndicatedepisodenumber, new.programid, new.manualid, 
new.generic, new.listingsource, new.first, new.last, new.audioprop, 
new.subtitletypes, new.videoprop
FROM program as new JOIN program as old
ON (new.starttime>=old.starttime AND new.endtime<=old.endtime)
WHERE (old.chanid=1001 AND old.title='BBC World') AND new.chanid=2093;

DELETE FROM program WHERE title='BBC World' AND chanid=1001;


  - Wade