[mythtvnz] TV One & BBC World listings

[Michael Dilger]

Neil Henwood wrote:
> 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')
>   

If you are inserting multiple rows, try using two sets of parens around 
the select.   I can't remember if that works.

If not, you can use a MySQL procedure to do the loop.  Here's a sample 
bare-bones MySQL procedure, that can loop around multiple output lines 
from a select.

1a - replace "ALPHA" with the type for chanid, and BETA with the type 
for title
1b - replace gamma,delta,epsilon with the specific field names of the 
select "*" construct, and define
       containers to catch the fetched select results in (fgamma, 
fdelta, fepsilon)
1b - fill in what you want to do
1c - rename the procedure something better.

I didn't read your whole email.  If this is helpful and you want to ask 
more questions, go right ahead.

-Mike


delimiter //
DROP PROCEDURE IF EXISTS procedurename
//
CREATE PROCEDURE procedurename(inchanid ALPHA, intitle BETA)
   # If you want a function instead, add this here:  "RETURNS type"
   MODIFIES SQL DATA
BEGIN

  DECLARE done INT DEFAULT 0;
  DECLARE cur CURSOR FOR SELECT gamma,delta,epsilon FROM program WHERE 
chanid<=>inchanid AND title<=>intitle;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  DECLARE fgamma BLAH;
  DECLARE fdelta BLAH;
  DECLARE fepsilon BLAH;

  OPEN cur;

  mainloop: LOOP
    FETCH cur INTO fgamma, fdelta, fepsilon;
    IF done THEN
      LEAVE mainloop;
    END IF;

    # If you need to move to the next loop iteration:
    # ITERATE mainloop;

    # if you are done
    # LEAVE mainloop;

    # Do something interesting right about here.

  END LOOP mainloop;

  CLOSE cur;

  # If you make this a function instead:
  # RETURN blah

END;
//
delimiter ;