[mythtv-users] Which table?

[Michael Sullivan]

On Sun, 2008-09-21 at 07:37 -0700, Allen Edwards wrote:
> On Sun, Sep 21, 2008 at 7:02 AM, Michael Sullivan
> <michael at espersunited.com> wrote:
> > I'd like to write a PHP script that will access the mythconverg database
> > and query for a list of programs being recorded the current day and
> > email that list to myself and another user.  I can write the script to
> > query the db and send the email, but I can't figure out which table
> > holds that information.  My best bet was the record table, but it seems
> > to only return data for shows that I've marked not to record:
> >
> > For example:
> >
> > mysql> select title, starttime from record where startdate="2008-09-22";
> > +------------------------+-----------+
> > | title                  | starttime |
> > +------------------------+-----------+
> > | The Last Samurai       | 19:00:00  |
> > | The Poseidon Adventure | 03:30:00  |
> > | Paranormal State       | 22:30:00  |
> > | Paranormal State       | 22:00:00  |
> > | Paranormal State       | 21:30:00  |
> > | Paranormal State       | 21:00:00  |
> > | The Poseidon Adventure | 03:30:00  |
> > +------------------------+-----------+
> > 7 rows in set (0.16 sec)
> >
> >
> > In the MythTV interface, the only program listed to record on 9-22 is
> > "Terminator:  The Sarah Connor Chronicles".  However, if I search for
> > that in the record table:
> >
> > mysql> select title, starttime, startdate from record where
> > title="Terminator: The Sarah Connor Chronicles";
> > +-----------------------------------------+-----------+------------+
> > | title                                   | starttime | startdate  |
> > +-----------------------------------------+-----------+------------+
> > | Terminator: The Sarah Connor Chronicles | 19:00:00  | 2008-01-13 |
> > +-----------------------------------------+-----------+------------+
> > 1 row in set (0.00 sec)
> >
> > The only record is from a long time ago.  Those shows up above are
> > listed above in the db are listed in the GUI, but they are manual "Don't
> > record"s I have a lot of space issues, so I go through the listing in
> > the GUI each time I run mythfilldatabase and mark things not to record
> > that I know will be on again soon.  Can anyone help me?
> >
> >
> >
> This sounds like a good script to have.  Please share it when you are done.
> 
> In terms of the data base, I would search for  "%Sarah%" instead of
> the entire title.  Perhaps the entire title didn't show up because of
> some punctuation difference and finding all the shows with just part
> of the title in them would uncover that.
> 
> Allen
> _______________________________________________

A few changes to my script.  The recording today query now sorts by
starttime, and one can see the programs that are marked not to record,
also sorted by starttime.  Now I just need to find the proper query for
a search...

#!/usr/bin/env php

<?
// TODO:  Add display callsign instead of chanid

   //Now for some variables
   $db = "mythconverg";
   $user = "michael";
   $pass = "mythtv";
   $host = "192.168.1.3";
   $today = date("Y-m-d");

   $channel = "";
   $title = "";
   $subtitle = "";
   $description = "";
   $chanid = "";
   $startdate = "";
   $starttime = "";
   $endtime = "";


   print "\t\t\tTV TODAY Report for $today\n\n\n";
   //Let's connect
   $link = mysql_connect ($host, $user, $pass);
   if (!$link) die ("Could not connect to database server.");
   mysql_select_db ($db, $link) or die ("Could not connect to
database");

   //Let's find out what we want to search for.
   $query = "SELECT station, title, subtitle, description, starttime,
endtime FROM record WHERE next_record LIKE '%$today%' ORDER BY starttime
ASC";
   $result = mysql_query($query, $link) or die("Could not retrieve
records".mysql_error());

   print "\t\tPROGRAMS THAT WILL BE RECORDED:\n\n";
   while($a_row = mysql_fetch_array($result, MYSQL_ASSOC))
   {
      $chanid = $a_row['station'];
      $title = $a_row['title'];
      $subtitle = $a_row['subtitle'];
      $description = $a_row['description'];
      $starttime = $a_row['starttime'];
      $endtime = $a_row['endtime'];
      
      
      print "Program Channel:\t$chanid\n";
      print "Program Name:\t\t$title\n";
      print "Program Episode:\t$subtitle\n";
      print "Program Descriptino:\t$description\n";
      print "Program Start Time:\t$starttime\n";
      print "Program End Time:\t$endtime\n\n";
   }
   print "\n\n\t\tPROGRAMS THAT WILL NOT BE RECORDED\n\n";

   $query = "SELECT DISTINCT station, title, subtitle, description,
startdate, starttime, endtime FROM record WHERE startdate LIKE '%
2008-09-22%' and type=8 ORDER BY starttime ASC;";
   $result = mysql_query($query, $link) or die("Could not retrieve
records".mysql_error());

   while($a_row = mysql_fetch_array($result, MYSQL_ASSOC))
   {
      $chanid = $a_row['station'];
      $title = $a_row['title'];
      $subtitle = $a_row['subtitle'];
      $description = $a_row['description'];
      $starttime = $a_row['starttime'];
      $endtime = $a_row['endtime'];
      
      
      print "Program Channel:\t$chanid\n";
      print "Program Name:\t\t$title\n";
      print "Program Episode:\t$subtitle\n";
      print "Program Descriptino:\t$description\n";
      print "Program Start Time:\t$starttime\n";
      print "Program End Time:\t$endtime\n\n";
   }
   
?>