diff options
| -rw-r--r-- | include/constants.php | 1 | ||||
| -rw-r--r-- | include/functions.inc.php | 186 | ||||
| -rw-r--r-- | include/functions_category.inc.php | 152 | ||||
| -rw-r--r-- | install/db/4-database.php | 54 | ||||
| -rw-r--r-- | install/phpwebgallery_structure.sql | 12 | ||||
| -rw-r--r-- | picture.php | 61 | ||||
| -rw-r--r-- | search.php | 129 |
7 files changed, 374 insertions, 221 deletions
diff --git a/include/constants.php b/include/constants.php index bb5a1d926..bf5cfd29c 100644 --- a/include/constants.php +++ b/include/constants.php @@ -60,4 +60,5 @@ define('RATE_TABLE', $prefixeTable.'rate'); define('USER_CACHE_TABLE', $prefixeTable.'user_cache'); define('CADDIE_TABLE', $prefixeTable.'caddie'); define('UPGRADE_TABLE', $prefixeTable.'upgrade'); +define('SEARCH_TABLE', $prefixeTable.'search'); ?> diff --git a/include/functions.inc.php b/include/functions.inc.php index 556c62582..f5710bbdd 100644 --- a/include/functions.inc.php +++ b/include/functions.inc.php @@ -741,8 +741,8 @@ function l10n($key) } /** - * returns the corresponding value from $themeconf if existing. Else, the key is - * returned + * returns the corresponding value from $themeconf if existing. Else, the + * key is returned * * @param string key * @return string @@ -753,4 +753,186 @@ function get_themeconf($key) return $themeconf[$key]; } + +/** + * Prepends and appends a string at each value of the given array. + * + * @param array + * @param string prefix to each array values + * @param string suffix to each array values + */ +function prepend_append_array_items($array, $prepend_str, $append_str) +{ + array_walk( + $array, + create_function('&$s', '$s = "'.$prepend_str.'".$s."'.$append_str.'";') + ); + + return $array; +} + +/** + * returns the SQL clause from a search identifier + * + * Search rules are stored in search table as a serialized array. This array + * need to be transformed into an SQL clause to be used in queries. + * + * @param int search_id + * @return string + */ +function get_sql_search_clause($search_id) +{ + if (!is_numeric($search_id)) + { + die('Search id must be an integer'); + } + + $query = ' +SELECT rules + FROM '.SEARCH_TABLE.' + WHERE id = '.$_GET['search'].' +;'; + list($serialized_rules) = mysql_fetch_row(pwg_query($query)); + + $search = unserialize($serialized_rules); + +// echo '<pre>'; +// print_r($search); +// echo '</pre>'; + + // SQL where clauses are stored in $clauses array during query + // construction + $clauses = array(); + + foreach (array('file','name','comment','keywords','author') as $textfield) + { + if (isset($search['fields'][$textfield])) + { + $local_clauses = array(); + foreach ($search['fields'][$textfield]['words'] as $word) + { + array_push($local_clauses, $textfield." LIKE '%".$word."%'"); + } + + // adds brackets around where clauses + $local_clauses = prepend_append_array_items($local_clauses, '(', ')'); + + array_push( + $clauses, + implode( + ' '.$search['fields'][$textfield]['mode'].' ', + $local_clauses + ) + ); + } + } + + if (isset($search['fields']['allwords'])) + { + $fields = array('file', 'name', 'comment', 'keywords', 'author'); + // in the OR mode, request bust be : + // ((field1 LIKE '%word1%' OR field2 LIKE '%word1%') + // OR (field1 LIKE '%word2%' OR field2 LIKE '%word2%')) + // + // in the AND mode : + // ((field1 LIKE '%word1%' OR field2 LIKE '%word1%') + // AND (field1 LIKE '%word2%' OR field2 LIKE '%word2%')) + $word_clauses = array(); + foreach ($search['fields']['allwords']['words'] as $word) + { + $field_clauses = array(); + foreach ($fields as $field) + { + array_push($field_clauses, $field." LIKE '%".$word."%'"); + } + // adds brackets around where clauses + array_push( + $word_clauses, + implode( + "\n OR ", + $field_clauses + ) + ); + } + + array_walk( + $word_clauses, + create_function('&$s','$s="(".$s.")";') + ); + + array_push( + $clauses, + "\n ". + implode( + "\n ". + $search['fields']['allwords']['mode']. + "\n ", + $word_clauses + ) + ); + } + + foreach (array('date_available', 'date_creation') as $datefield) + { + if (isset($search['fields'][$datefield])) + { + array_push( + $clauses, + $datefield." = '".$search['fields'][$datefield]['date']."'" + ); + } + + foreach (array('after','before') as $suffix) + { + $key = $datefield.'-'.$suffix; + + if (isset($search['fields'][$key])) + { + array_push( + $clauses, + + $datefield. + ($suffix == 'after' ? ' >' : ' <'). + ($search['fields'][$key]['inc'] ? '=' : ''). + " '".$search['fields'][$key]['date']."'" + + ); + } + } + } + + if (isset($search['fields']['cat'])) + { + if ($search['fields']['cat']['sub_inc']) + { + // searching all the categories id of sub-categories + $cat_ids = get_subcat_ids($search['fields']['cat']['words']); + } + else + { + $cat_ids = $search['fields']['cat']['words']; + } + + $local_clause = 'category_id IN ('.implode(',', $cat_ids).')'; + array_push($clauses, $local_clause); + } + + // adds brackets around where clauses + $clauses = prepend_append_array_items($clauses, '(', ')'); + + $where_separator = + implode( + "\n ".$search['mode'].' ', + $clauses + ); + + $search_clause = $where_separator; + + if (isset($forbidden)) + { + $search_clause.= "\n AND ".$forbidden; + } + + return $search_clause; +} ?> diff --git a/include/functions_category.inc.php b/include/functions_category.inc.php index 6b9618db1..c9fee76f0 100644 --- a/include/functions_category.inc.php +++ b/include/functions_category.inc.php @@ -103,7 +103,9 @@ function check_cat_id( $cat ) { $page['cat'] = $cat; } - if ($cat == 'search' and isset($_GET['search'])) + if ($cat == 'search' + and isset($_GET['search']) + and is_numeric($_GET['search'])) { $page['cat'] = $cat; } @@ -381,44 +383,6 @@ function initialize_category( $calling_page = 'category' ) // search result if ( $page['cat'] == 'search' ) { - // analyze search string given in URL (created in search.php) - $tokens = explode('|', $_GET['search']); - - if (isset($tokens[1]) and $tokens[1] == 'AND') - { - $search['mode'] = 'AND'; - } - else - { - $search['mode'] = 'OR'; - } - - $search_tokens = explode('--', $tokens[0]); - foreach ($search_tokens as $search_token) - { - $tokens = explode(':', $search_token); - $field_name = $tokens[0]; - $field_content = $tokens[1]; - - $tokens = explode('~', $tokens[1]); - if (isset($tokens[1])) - { - $search['fields'][$field_name]['mode'] = $tokens[1]; - } - else - { - $search['fields'][$field_name]['mode'] = ''; - } - - $search['fields'][$field_name]['words'] = array(); - $tokens = explode(',', $tokens[0]); - foreach ($tokens as $token) - { - array_push($search['fields'][$field_name]['words'], - htmlentities($token)); - } - } - $page['title'] = $lang['search_result']; if ( $calling_page == 'picture' ) { @@ -426,117 +390,13 @@ function initialize_category( $calling_page = 'category' ) $page['title'].= $_GET['search']."</span>"; } - // SQL where clauses are stored in $clauses array during query - // construction - $clauses = array(); + $page['where'] = 'WHERE '.get_sql_search_clause($_GET['search']); - $textfields = array('file', 'name', 'comment', 'keywords', 'author'); - foreach ($textfields as $textfield) - { - if (isset($search['fields'][$textfield])) - { - $local_clauses = array(); - foreach ($search['fields'][$textfield]['words'] as $word) - { - array_push($local_clauses, $textfield." LIKE '%".$word."%'"); - } - // adds brackets around where clauses - array_walk($local_clauses,create_function('&$s','$s="(".$s.")";')); - array_push($clauses, - implode(' '.$search['fields'][$textfield]['mode'].' ', - $local_clauses)); - } - } - - if (isset($search['fields']['allwords'])) - { - $fields = array('file', 'name', 'comment', 'keywords', 'author'); - // in the OR mode, request bust be : - // ((field1 LIKE '%word1%' OR field2 LIKE '%word1%') - // OR (field1 LIKE '%word2%' OR field2 LIKE '%word2%')) - // - // in the AND mode : - // ((field1 LIKE '%word1%' OR field2 LIKE '%word1%') - // AND (field1 LIKE '%word2%' OR field2 LIKE '%word2%')) - $word_clauses = array(); - foreach ($search['fields']['allwords']['words'] as $word) - { - $field_clauses = array(); - foreach ($fields as $field) - { - array_push($field_clauses, $field." LIKE '%".$word."%'"); - } - // adds brackets around where clauses - array_push($word_clauses, implode(' OR ', $field_clauses)); - } - array_walk($word_clauses, create_function('&$s','$s="(".$s.")";')); - array_push($clauses, - implode(' '.$search['fields']['allwords']['mode'].' ', - $word_clauses)); - } - - $datefields = array('date_available', 'date_creation'); - foreach ($datefields as $datefield) - { - $key = $datefield; - if (isset($search['fields'][$key])) - { - $local_clause = $datefield." = '"; - $local_clause.= str_replace('.', '-', - $search['fields'][$key]['words'][0]); - $local_clause.= "'"; - array_push($clauses, $local_clause); - } - - foreach (array('after','before') as $suffix) - { - $key = $datefield.'-'.$suffix; - if (isset($search['fields'][$key])) - { - $local_clause = $datefield; - if ($suffix == 'after') - { - $local_clause.= ' >'; - } - else - { - $local_clause.= ' <'; - } - if (isset($search['fields'][$key]['mode']) - and $search['fields'][$key]['mode'] == 'inc') - { - $local_clause.= '='; - } - $local_clause.= " '"; - $local_clause.= str_replace('.', '-', - $search['fields'][$key]['words'][0]); - $local_clause.= "'"; - array_push($clauses, $local_clause); - } - } - } - - if (isset($search['fields']['cat'])) + if (isset($forbidden)) { - if ($search['fields']['cat']['mode'] == 'sub_inc') - { - // searching all the categories id of sub-categories - $cat_ids = get_subcat_ids($search['fields']['cat']['words']); - } - else - { - $cat_ids = $search['fields']['cat']['words']; - } - - $local_clause = 'category_id IN ('.implode(',', $cat_ids).')'; - array_push($clauses, $local_clause); + $page['where'].= "\n AND ".$forbidden; } - // adds brackets around where clauses - array_walk($clauses, create_function('&$s', '$s = "(".$s.")";')); - $page['where'] = 'WHERE '.implode(' '.$search['mode'].' ', $clauses); - if ( isset( $forbidden ) ) $page['where'].= ' AND '.$forbidden; - $query = ' SELECT COUNT(DISTINCT(id)) AS nb_total_images FROM '.IMAGES_TABLE.' diff --git a/install/db/4-database.php b/install/db/4-database.php new file mode 100644 index 000000000..c74486a46 --- /dev/null +++ b/install/db/4-database.php @@ -0,0 +1,54 @@ +<?php +// +-----------------------------------------------------------------------+ +// | PhpWebGallery - a PHP based picture gallery | +// | Copyright (C) 2002-2003 Pierrick LE GALL - pierrick@phpwebgallery.net | +// | Copyright (C) 2003-2005 PhpWebGallery Team - http://phpwebgallery.net | +// +-----------------------------------------------------------------------+ +// | branch : BSF (Best So Far) +// | file : $RCSfile$ +// | last update : $Date: 2005-09-21 00:04:57 +0200 (mer, 21 sep 2005) $ +// | last modifier : $Author: plg $ +// | revision : $Revision: 870 $ +// +-----------------------------------------------------------------------+ +// | This program is free software; you can redistribute it and/or modify | +// | it under the terms of the GNU General Public License as published by | +// | the Free Software Foundation | +// | | +// | This program is distributed in the hope that it will be useful, but | +// | WITHOUT ANY WARRANTY; without even the implied warranty of | +// | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU | +// | General Public License for more details. | +// | | +// | You should have received a copy of the GNU General Public License | +// | along with this program; if not, write to the Free Software | +// | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, | +// | USA. | +// +-----------------------------------------------------------------------+ + +if (!defined('PHPWG_ROOT_PATH')) +{ + die('Hacking attempt!'); +} + +$upgrade_description = 'Table #search is required for search engine redesign'; + +// +-----------------------------------------------------------------------+ +// | Upgrade content | +// +-----------------------------------------------------------------------+ + +// column user_id becomes data of type text +$query = ' +CREATE TABLE '.PREFIX_TABLE.'search ( + id int UNSIGNED NOT NULL AUTO_INCREMENT, + last_seen date DEFAULT NULL, + rules text, + PRIMARY KEY (id) +);'; +pwg_query($query); + +echo +"\n" +.'Table '.PREFIX_TABLE.'search created' +."\n" +; +?> diff --git a/install/phpwebgallery_structure.sql b/install/phpwebgallery_structure.sql index 12a27c00d..9e50885f6 100644 --- a/install/phpwebgallery_structure.sql +++ b/install/phpwebgallery_structure.sql @@ -177,6 +177,18 @@ CREATE TABLE `phpwebgallery_rate` ( ) TYPE=MyISAM; -- +-- Table structure for table `phpwebgallery_search` +-- + +DROP TABLE IF EXISTS `phpwebgallery_search`; +CREATE TABLE `phpwebgallery_search` ( + `id` int(10) unsigned NOT NULL auto_increment, + `last_seen` date default NULL, + `rules` text, + PRIMARY KEY (`id`) +) TYPE=MyISAM; + +-- -- Table structure for table `phpwebgallery_sessions` -- diff --git a/picture.php b/picture.php index f34c6b3c3..a9655f712 100644 --- a/picture.php +++ b/picture.php @@ -675,10 +675,14 @@ $infos = array(); if (!empty($picture['current']['author'])) { $infos['INFO_AUTHOR'] = - '<a href="'. - PHPWG_ROOT_PATH.'category.php?cat=search'. - '&search=author:'.$picture['current']['author'] - .'">'.$picture['current']['author'].'</a>'; + // FIXME because of search engine partial rewrite, giving the author + // name threw GET is not supported anymore. This feature should come + // back later, with a better design +// '<a href="'. +// PHPWG_ROOT_PATH.'category.php?cat=search'. +// '&search=author:'.$picture['current']['author'] +// .'">'.$picture['current']['author'].'</a>'; + $picture['current']['author']; } else { @@ -689,10 +693,14 @@ else if (!empty($picture['current']['date_creation'])) { $infos['INFO_CREATION_DATE'] = - '<a href="'. - PHPWG_ROOT_PATH.'category.php?cat=search'. - '&search=date_creation:'.$picture['current']['date_creation'] - .'">'.format_date($picture['current']['date_creation']).'</a>'; + // FIXME because of search engine partial rewrite, giving the author + // name threw GET is not supported anymore. This feature should come + // back later, with a better design (calendar view). +// '<a href="'. +// PHPWG_ROOT_PATH.'category.php?cat=search'. +// '&search=date_creation:'.$picture['current']['date_creation'] +// .'">'.format_date($picture['current']['date_creation']).'</a>'; + format_date($picture['current']['date_creation']); } else { @@ -701,13 +709,18 @@ else // date of availability $infos['INFO_AVAILABILITY_DATE'] = - '<a href="'. - PHPWG_ROOT_PATH.'category.php?cat=search'. - '&search=date_available:'. - substr($picture['current']['date_available'], 0, 10) - .'">'. - format_date($picture['current']['date_available'], 'mysql_datetime'). - '</a>'; +// FIXME because of search engine partial rewrite, giving the author +// name threw GET is not supported anymore. This feature should come +// back later, with a better design (calendar view). +// +// '<a href="'. +// PHPWG_ROOT_PATH.'category.php?cat=search'. +// '&search=date_available:'. +// substr($picture['current']['date_available'], 0, 10) +// .'">'. +// format_date($picture['current']['date_available'], 'mysql_datetime'). +// '</a>'; +format_date($picture['current']['date_available'], 'mysql_datetime'); // size in pixels if ($picture['current']['is_picture']) @@ -751,13 +764,17 @@ $infos['INFO_FILE'] = $picture['current']['file']; if (!empty($picture['current']['keywords'])) { $infos['INFO_KEYWORDS'] = - preg_replace( - '/([^,]+)/', - '<a href="'. - PHPWG_ROOT_PATH.'category.php?cat=search&search=keywords:$1' - .'">$1</a>', - $picture['current']['keywords'] - ); + // FIXME because of search engine partial rewrite, giving the author + // name threw GET is not supported anymore. This feature should come + // back later, with a better design (tag classification). +// preg_replace( +// '/([^,]+)/', +// '<a href="'. +// PHPWG_ROOT_PATH.'category.php?cat=search&search=keywords:$1' +// .'">$1</a>', +// $picture['current']['keywords'] +// ); + $picture['current']['keywords']; } else { diff --git a/search.php b/search.php index b02b52daa..a61526d7b 100644 --- a/search.php +++ b/search.php @@ -37,41 +37,43 @@ if (isset($_POST['submit'])) { if (isset($_POST['search_allwords']) and !preg_match('/^\s*$/', $_POST['search_allwords'])) - { - $local_search = array(); - $search_allwords = $_POST['search_allwords']; + { $drop_char_match = array( '-','^','$',';','#','&','(',')','<','>','`','\'','"','|',',','@','_', '?','%','~','.','[',']','{','}',':','\\','/','=','\'','!','*'); $drop_char_replace = array( ' ',' ',' ',' ',' ',' ',' ',' ',' ',' ','','',' ',' ',' ',' ','',' ', ' ',' ',' ',' ',' ',' ',' ',' ','' ,' ',' ',' ',' ',' '); - $search_allwords = str_replace($drop_char_match, - $drop_char_replace, - $search_allwords); // Split words - $words = preg_split('/\s+/', $search_allwords); - $words = array_unique($words); - $search['fields']['allwords'] = array(); - $search['fields']['allwords']['words'] = $words; - $search['fields']['allwords']['mode'] = $_POST['mode']; + $search['fields']['allwords'] = array( + 'words' => array_unique( + preg_split( + '/\s+/', + str_replace( + $drop_char_match, + $drop_char_replace, + $_POST['search_allwords'] + ) + ) + ), + 'mode' => $_POST['mode'], + ); } if ($_POST['search_author']) { - $search['fields']['author'] = array(); - $search['fields']['author']['words'] = array($_POST['search_author']); + $search['fields']['author'] = array( + 'words' => array($_POST['search_author']), + ); } if (isset($_POST['cat'])) { - $search['fields']['cat'] = array(); - $search['fields']['cat']['words'] = $_POST['cat']; - if ($_POST['subcats-included'] == 1) - { - $search['fields']['cat']['mode'] = 'sub_inc'; - } + $search['fields']['cat'] = array( + 'words' => $_POST['cat'], + 'sub_inc' => ($_POST['subcats-included'] == 1) ? true : false, + ); } // dates @@ -79,46 +81,71 @@ if (isset($_POST['submit'])) if (!empty($_POST['start_year'])) { - $year = $_POST['start_year']; - $month = $_POST['start_month'] != 0 ? $_POST['start_month'] : '01'; - $day = $_POST['start_day'] != 0 ? $_POST['start_day'] : '01'; - $date = $year.'-'.$month.'-'.$day; +// $year = $_POST['start_year']; +// $month = $_POST['start_month'] != 0 ? $_POST['start_month'] : '01'; +// $day = $_POST['start_day'] != 0 ? $_POST['start_day'] : '01'; +// $date = $year.'-'.$month.'-'.$day; - $search['fields'][$type_date.'-after']['words'] = array($date); - $search['fields'][$type_date.'-after']['mode'] = 'inc'; +// $search['fields'][$type_date.'-after']['words'] = array($date); +// $search['fields'][$type_date.'-after']['mode'] = 'inc'; + + $search['fields'][$type_date.'-after'] = array( + 'date' => join( + '-', + array( + $_POST['start_year'], + $_POST['start_month'] != 0 ? $_POST['start_month'] : '01', + $_POST['start_day'] != 0 ? $_POST['start_day'] : '01', + ) + ), + 'inc' => true, + ); } if (!empty($_POST['end_year'])) { - $year = $_POST['end_year']; - $month = $_POST['end_month'] != 0 ? $_POST['end_month'] : '12'; - $day = $_POST['end_day'] != 0 ? $_POST['end_day'] : '31'; - $date = $year.'-'.$month.'-'.$day; +// $year = $_POST['end_year']; +// $month = $_POST['end_month'] != 0 ? $_POST['end_month'] : '12'; +// $day = $_POST['end_day'] != 0 ? $_POST['end_day'] : '31'; +// $date = $year.'-'.$month.'-'.$day; - $search['fields'][$type_date.'-before']['words'] = array($date); - $search['fields'][$type_date.'-before']['mode'] = 'inc'; + $search['fields'][$type_date.'-before'] = array( + 'date' => join( + '-', + array( + $_POST['end_year'], + $_POST['end_month'] != 0 ? $_POST['end_month'] : '12', + $_POST['end_day'] != 0 ? $_POST['end_day'] : '31', + ) + ), + 'inc' => true, + ); } - - // search string (for URL) creation - $search_string = ''; - $tokens = array(); + if (!empty($search)) { - foreach (array_keys($search['fields']) as $field) - { - $token = $field.':'; - $token.= implode(',', $search['fields'][$field]['words']); - if (isset($search['fields'][$field]['mode'])) - { - $token.= '~'.$search['fields'][$field]['mode']; - } - array_push($tokens, $token); - } - $search_string.= implode('--', $tokens); - if (count($tokens) > 1) - { - $search_string.= '|AND'; - } + // default search mode : each clause must be respected + $search['mode'] = 'AND'; + +// echo '<pre>'; +// print_r($_POST); +// echo '</pre>'; + +// echo '<pre>'; +// print_r($search); +// echo '</pre>'; + + // register search rules in database, then they will be available on + // thumbnails page and picture page. + $query =' +INSERT INTO '.SEARCH_TABLE.' + (rules) + VALUES + (\''.serialize($search).'\') +;'; + pwg_query($query); + + $search_id = mysql_insert_id(); } else { @@ -128,7 +155,7 @@ if (isset($_POST['submit'])) //----------------------------------------------------------------- redirection if (isset($_POST['submit']) and count($errors) == 0) { - $url = 'category.php?cat=search&search='.$search_string; + $url = 'category.php?cat=search&search='.$search_id; redirect($url); } //----------------------------------------------------- template initialization |