[mythtv] [PATCH] Mythweb -- generalized SQL queries, HD search,
page of canned searches
Brad Templeton
brad+mydev at templetons.com
Tue Feb 22 06:03:08 UTC 2005
The following patches for mythweb add the following features:
a) One can preface "hd:" to a search and find only programs broadcast in
HDTV.
b) One can provide a parameter "sql" which is a generalized SQL query for
the "program" table to search.php. This is mostly used below, but
allows users to build complex queries, if they know their SQL, and bookmark
them for future searching.
c) Added a new page of "Searches". This page contains a list of links
which are canned searches, largely generated using the sql search option.
Coders can readily add new searches to the page that might be popular.
Users can use that page, or go there once and bookmark them for future
easy searching. Thus only developers need know SQL.
In theory the "Movies" canned search could be moved to this page.
Sample searches include HDTV non-series (specials and movies), all HDTV,
Science Fiction Movies and "Specials" (similar to the list built into
MythTV)
The patch changes a few files and adds two new ones, which are also
attached
-------------- next part --------------
? Makefile
? handy.php
? themes/Default/handy.php
Index: search.php
===================================================================
RCS file: /var/lib/mythcvs/mythweb/search.php,v
retrieving revision 1.17
diff -u -r1.17 search.php
--- search.php 10 Feb 2005 02:56:22 -0000 1.17
+++ search.php 22 Feb 2005 05:52:04 -0000
@@ -27,8 +27,8 @@
$_SESSION['search']['search_exact'] = _or($_GET['search_exact'], $_POST['search_exact']);
}
// Individual search strings for different fields
- elseif ($_GET['title'] || $_GET['subtitle'] || $_GET['description'] || $_GET['category'] || $_GET['category_type'] || $_GET['originalairdate']
- || $_POST['title'] || $_POST['subtitle'] || $_POST['description'] || $_POST['category'] || $_POST['category_type'] || $_POST['originalairdate'] ) {
+ elseif ($_GET['title'] || $_GET['subtitle'] || $_GET['description'] || $_GET['category'] || $_GET['category_type'] || $_GET['originalairdate'] || $_GET['sql']
+ || $_POST['title'] || $_POST['subtitle'] || $_POST['description'] || $_POST['category'] || $_POST['category_type'] || $_POST['originalairdate'] || $_POST['sql'] ) {
unset($_SESSION['search']);
$_SESSION['search']['title'] = _or($_GET['title'], $_POST['title']);
$_SESSION['search']['subtitle'] = _or($_GET['subtitle'], $_POST['subtitle']);
@@ -37,11 +37,12 @@
$_SESSION['search']['category_type'] = _or($_GET['category_type'], $_POST['category_type']);
$_SESSION['search']['originalairdate'] = _or($_GET['originalairdate'], $_POST['originalairdate']);
$_SESSION['search']['search_exact'] = _or($_GET['search_exact'], $_POST['search_exact']);
+ $_SESSION['search']['sql'] = _or($_GET['sql'], $_POST['sql']);
}
// Start the query out as an array
$query = array();
- $star_query = '';
+ $extra_query = '';
if ($_SESSION['search']['search_exact'])
$compare = ' = ';
else
@@ -58,9 +59,12 @@
if (preg_match( "/1\\/2|\\.5|-/", $stars[1]))
$starcount += 0.125;
// Add this to the query -- convert european decimal to something mysql can understand
- $star_query = ' AND program.stars >= '.str_replace(',', '.', $starcount);
+ $extra_query = ' AND program.stars >= '.str_replace(',', '.', $starcount);
// Remove the stars from the search string so we can continue looking for other things
$search_str = preg_replace('#(\\*+\s*(1/2\b|0?\.5\b|-)?)\s*#', '', $search_str);
+ } elseif( preg_match( "#^hd:#", $search_str ) ) {
+ $extra_query = ' AND hdtv = 1 ';
+ $search_str = preg_replace( "#^hd(?i):\s*#", '', $search_str );
}
// Regex search?
if (preg_match('#^/(.+)/$#', $search_str, $match)) {
@@ -92,6 +96,11 @@
// Individual-field search is an AND search
$joiner = ' AND ';
// Build the query
+ // First allow the raw SQL query in
+ if ($_SESSION['search']['sql']) {
+ // evil kludge, we remove the slashes magic quotes put on!
+ $query[] = stripslashes($_SESSION['search']['sql']);
+ }
if ($_SESSION['search']['title'])
$query[] = "program.title$compare".search_escape($_SESSION['search']['title']);
if (isset($_SESSION['search']['subtitle']))
@@ -117,7 +126,7 @@
# starttime
# endtime
// Perform the query
- $Results =& load_all_program_data(time(), strtotime('+1 month'), NULL, false, '(('.implode($joiner, $query).')'.$star_query.')');
+ $Results =& load_all_program_data(time(), strtotime('+1 month'), NULL, false, '(('.implode($joiner, $query).')'.$extra_query.')');
// Sort the results
if (count($Results))
sort_programs($Results, 'search_sortby');
Index: languages/English.php
===================================================================
RCS file: /var/lib/mythcvs/mythweb/languages/English.php,v
retrieving revision 1.46
diff -u -r1.46 English.php
--- languages/English.php 16 Feb 2005 04:02:40 -0000 1.46
+++ languages/English.php 22 Feb 2005 05:52:05 -0000
@@ -214,6 +214,12 @@
'Key Bindings' => '',
'MythWeb Settings' => '',
'settings: overview' => 'This is the index page for the configuration settings...<p>It\'s incomplete, and will eventually get some nicer formatting. For now, you can choose from the following:',
+// themes/.../handy.php
+ 'handy: overview' => 'This page contains pre-prepared complex searches in the listings.',
+ 'Non-Series HDTV programs' => '',
+ 'All HDTV programs' => '',
+ 'Specials' => '',
+ 'Science Fiction Movies' => '',
// themes/.../settings_channels.php
'Please be warned that by altering this table without knowing what you are doing, you could seriously disrupt mythtv functionality.' => '',
// themes/.../settings_keys.php
@@ -241,6 +247,7 @@
'Favorites' => '',
'Manually Schedule' => '',
'Movies' => '',
+ 'Searches' => '',
'MythMusic on the web.' => '',
'MythVideo on the web.' => '',
'MythWeb Weather.' => '',
Index: themes/Default/theme.php
===================================================================
RCS file: /var/lib/mythcvs/mythweb/themes/Default/theme.php,v
retrieving revision 1.48
diff -u -r1.48 theme.php
--- themes/Default/theme.php 9 Feb 2005 03:35:37 -0000 1.48
+++ themes/Default/theme.php 22 Feb 2005 05:52:05 -0000
@@ -149,6 +149,8 @@
<?/* |
<a href="index.php?mode=favourites"><?php echo t('Favorites') ?></a>*/?>
|
+ <a href="handy.php"><?php echo t('Searches') ?></a>
+ |
<a href="schedule_manually.php"><?php echo t('Manually Schedule') ?></a>
|
<a href="recording_schedules.php"><?php echo t('Recording Schedules') ?></a>
-------------- next part --------------
<?php
/*** ***\
handy.php Last Updated: 2005.01.23 (xris)
An index for handy SQL searches in the listings data
\*** ***/
// Which section are we in?
define('section', 'tv');
// Initialize the script, database, etc.
require_once "includes/init.php";
// Load the class for this page
require_once theme_dir.'handy.php';
// Create an instance of this page from its theme object
$Page = new Theme_handy();
// Display the page
$Page->print_page();
// Exit
exit;
?>
-------------- next part --------------
<?php
/*** ***\
handy.php Last Updated: 2005.02.21 (xris)
main configuration index
\*** ***/
class Theme_handy extends Theme {
function print_page() {
$this->print_header();
?>
<div style="padding: 20px">
<? echo t('handy: overview') ?>
<p><table cellpadding=12>
<tr><td><a href="search.php?sql=hdtv=1 AND category_type != 'series'"><?php echo t('Non-Series HDTV programs') ?></a></td></tr>
<tr><td><a href="search.php?sql=hdtv=1"><?php echo t('All HDTV programs') ?></a>
</td></tr>
<tr><td><a href="search.php?sql=showtype LIKE 'special'"><?php echo t('Specials') ?></a>
</td></tr>
<tr><td><a href="search.php?sql=category_type LIKE 'movie' AND category LIKE 'science fiction'"><?php echo t('Science Fiction Movies') ?></a>
</td></tr>
</table>
</div>
<?php
$this->print_footer();
}
function print_menu_content() {
}
function print_header() {
parent::print_header("MythWeb - Handy Searches");
}
function print_footer() {
parent::print_footer();
}
}
?>
More information about the mythtv-dev
mailing list