From 0e2436f50a61f90da22e75085280bf9f2087699a Mon Sep 17 00:00:00 2001 From: z0rglub Date: Mon, 30 Aug 2004 22:00:46 +0000 Subject: add rating feature git-svn-id: http://piwigo.org/svn/trunk@507 68402e56-0260-453c-a942-63ccdbb3a9ee --- category.php | 8 +- include/config.inc.php | 3 +- include/constants.php | 1 + include/functions.inc.php | 34 +++++ include/functions_category.inc.php | 38 ++++++ install/dbscheme.txt | 7 + install/phpwebgallery_structure.sql | 13 ++ language/en_UK.iso-8859-1/common.lang.php | 10 +- picture.php | 213 +++++++++++++++++++++--------- template/default/category.tpl | 3 +- template/default/picture.tpl | 10 ++ 11 files changed, 276 insertions(+), 64 deletions(-) diff --git a/category.php b/category.php index 781d3bda1..9ce531e53 100644 --- a/category.php +++ b/category.php @@ -125,7 +125,7 @@ $template->assign_vars(array( 'NB_PICTURE' => count_user_total_images(), 'TITLE' => $template_title, 'USERNAME' => $user['username'], - 'TOP_VISITED'=>$conf['top_number'], + 'TOP_NUMBER'=>$conf['top_number'], 'MENU_CATEGORIES_CONTENT'=>$page['menu'], 'L_CATEGORIES' => $lang['categories'], @@ -138,6 +138,8 @@ $template->assign_vars(array( 'L_SPECIAL_CATEGORIES' => $lang['special_categories'], 'L_MOST_VISITED_HINT' => $lang['most_visited_cat_hint'], 'L_MOST_VISITED' => $lang['most_visited_cat'], + 'L_BEST_RATED_HINT' => $lang['best_rated_cat_hint'], + 'L_BEST_RATED' => $lang['best_rated_cat'], 'L_RECENT_PICS_HINT' => $lang['recent_pics_cat_hint'], 'L_RECENT_PICS' => $lang['recent_pics_cat'], 'L_RECENT_CATS_HINT' => $lang['recent_cats_cat_hint'], @@ -166,6 +168,7 @@ $template->assign_vars(array( 'U_HOME' => add_session_id( PHPWG_ROOT_PATH.'category.php' ), 'U_FAVORITE' => add_session_id( PHPWG_ROOT_PATH.'category.php?cat=fav' ), 'U_MOST_VISITED'=>add_session_id( PHPWG_ROOT_PATH.'category.php?cat=most_visited' ), + 'U_BEST_RATED'=>add_session_id(PHPWG_ROOT_PATH.'category.php?cat=best_rated'), 'U_RECENT_PICS'=>add_session_id( PHPWG_ROOT_PATH.'category.php?cat=recent_pics' ), 'U_RECENT_CATS'=>add_session_id( PHPWG_ROOT_PATH.'category.php?cat=recent_cats' ), 'U_CALENDAR'=>add_session_id( PHPWG_ROOT_PATH.'category.php?cat=calendar' ), @@ -228,7 +231,8 @@ if (isset($page['cat']) and ((is_numeric($page['cat']) and $page['cat_nb_images'] != 0) or $page['cat'] == 'search' or $page['cat'] == 'most_visited' - or $page['cat'] == 'recent_pics')) + or $page['cat'] == 'recent_pics' + or $page['cat'] == 'best_rated')) { include(PHPWG_ROOT_PATH.'include/category_default.inc.php'); } diff --git a/include/config.inc.php b/include/config.inc.php index 269cd123a..f33e53ba3 100644 --- a/include/config.inc.php +++ b/include/config.inc.php @@ -54,7 +54,7 @@ $conf['file_ext'] = array('jpg','JPG','png','PNG','gif','GIF' ,'mpg','zip','avi','mp3','ogg'); // $conf['picture_ext'] must bea subset of $conf['file_ext'] $conf['picture_ext'] = array('jpg','JPG','png','PNG','gif','GIF'); -$conf['top_number'] = 10; +$conf['top_number'] = 10; // used for "best rated" and "most visited" $conf['anti-flood_time'] = 60; // seconds between 2 comments : 0 to disable $conf['max_LOV_categories'] = 50; @@ -90,4 +90,5 @@ $conf['show_exif_fields'] = array('Make', // $conf['show_exif_fields'] = array('CameraMake','CameraModel','DateTime'); $conf['calendar_datefield'] = 'date_creation'; +$conf['rate'] = true; ?> diff --git a/include/constants.php b/include/constants.php index 483965a9e..ccb2ecd34 100644 --- a/include/constants.php +++ b/include/constants.php @@ -58,4 +58,5 @@ define('USER_GROUP_TABLE', $table_prefix.'user_group'); define('USERS_TABLE', $table_prefix.'users'); define('WAITING_TABLE', $table_prefix.'waiting'); define('IMAGE_METADATA_TABLE', $table_prefix.'image_metadata'); +define('RATE_TABLE', $table_prefix.'rate'); ?> diff --git a/include/functions.inc.php b/include/functions.inc.php index c15149e20..df5b39e40 100644 --- a/include/functions.inc.php +++ b/include/functions.inc.php @@ -505,4 +505,38 @@ function redirect( $url ) exit(); } + +/** + * returns $_SERVER['QUERY_STRING'] whitout keys given in parameters + * + * @param array $rejects + * @returns string + */ +function get_query_string_diff($rejects = array()) +{ + $query_string = ''; + + $str = $_SERVER['QUERY_STRING']; + parse_str($str, $vars); + + $is_first = true; + foreach ($vars as $key => $value) + { + if (!in_array($key, $rejects)) + { + if ($is_first) + { + $query_string.= '?'; + $is_first = false; + } + else + { + $query_string.= '&'; + } + $query_string.= $key.'='.$value; + } + } + + return $query_string; +} ?> \ No newline at end of file diff --git a/include/functions_category.inc.php b/include/functions_category.inc.php index e82df1242..9a65ef997 100644 --- a/include/functions_category.inc.php +++ b/include/functions_category.inc.php @@ -724,6 +724,44 @@ SELECT COUNT(DISTINCT(id)) AS nb_total_images $page['where'].= ' AND '.$forbidden; } } + else if ($page['cat'] == 'best_rated') + { + $page['title'] = $conf['top_number'].' '.$lang['best_rated_cat']; + + $page['where'] = ' WHERE average_rate IS NOT NULL'; + + if (isset($forbidden)) + { + $page['where'] = ' AND '.$forbidden; + } + + $conf['order_by'] = ' ORDER BY average_rate DESC, id ASC'; + + // $page['cat_nb_images'] equals $conf['top_number'] unless there + // are less rated items + $query =' +SELECT COUNT(1) AS count + FROM '.IMAGES_TABLE.' + '.$page['where'].' +;'; + $row = mysql_fetch_array(mysql_query($query)); + if ($row['count'] < $conf['top_number']) + { + $page['cat_nb_images'] = $row['count']; + } + else + { + $page['cat_nb_images'] = $conf['top_number']; + } + unset($query); + + + if (isset($page['start']) + and ($page['start']+$user['nb_image_page']>=$conf['top_number'])) + { + $page['nb_image_page'] = $conf['top_number'] - $page['start']; + } + } if (isset($query)) { diff --git a/install/dbscheme.txt b/install/dbscheme.txt index 913abe6e7..6661372f7 100644 --- a/install/dbscheme.txt +++ b/install/dbscheme.txt @@ -8,6 +8,7 @@ table:groups table:history table:image_category table:images +table:rate table:sessions table:sites table:user_access @@ -68,6 +69,10 @@ column:keywords table:images type:varchar column:storage_category_id table:images type:smallint nullable:N length:5 signed:N column:representative_ext table:images type:varchar nullable:N length:4 binary:N column:date_metadata_update table:images type:date nullable:N +column:average_rate table:images type:float nullable:N length:5,2 signed:N +column:user_id table:rate type:smallint nullable:Y length:5 signed:N +column:element_id table:rate type:mediumint nullable:Y length:8 signed:N +column:rate table:rate type:tinyint nullable:Y length:2 signed:N column:id table:sessions type:varchar nullable:Y length:255 binary:Y column:user_id table:sessions type:smallint nullable:Y length:5 signed:N column:expiration table:sessions type:int nullable:Y length:10 signed:N @@ -114,6 +119,8 @@ PK:groups_pk table:groups column:id PK:image_category_pk table:image_category column:image_id PK:image_category_pk table:image_category column:category_id PK:images_pk table:images column:id +PK:rate_pk table:rate column:user_id +PK:rate_pk table:rate column:element_id PK:sessions_pk table:sessions column:id PK:sites_pk table:sites column:id PK:user_access_pk table:user_access column:user_id diff --git a/install/phpwebgallery_structure.sql b/install/phpwebgallery_structure.sql index 06ea1cebf..3a387603b 100644 --- a/install/phpwebgallery_structure.sql +++ b/install/phpwebgallery_structure.sql @@ -137,11 +137,24 @@ CREATE TABLE phpwebgallery_images ( storage_category_id smallint(5) unsigned default NULL, representative_ext varchar(4) default NULL, date_metadata_update date default NULL, + average_rate float(5,2) unsigned default NULL, PRIMARY KEY (id), KEY images_i2 (date_available), KEY images_i1 (storage_category_id) ) TYPE=MyISAM; +-- +-- Table structure for table 'phpwebgallery_rate' +-- + +DROP TABLE IF EXISTS phpwebgallery_rate; +CREATE TABLE phpwebgallery_rate ( + user_id smallint(5) unsigned NOT NULL default '0', + element_id mediumint(8) unsigned NOT NULL default '0', + rate tinyint(2) unsigned NOT NULL default '0', + PRIMARY KEY (user_id,element_id) +) TYPE=MyISAM; + -- -- Table structure for table 'phpwebgallery_sessions' -- diff --git a/language/en_UK.iso-8859-1/common.lang.php b/language/en_UK.iso-8859-1/common.lang.php index c0bdd2b04..45a611710 100644 --- a/language/en_UK.iso-8859-1/common.lang.php +++ b/language/en_UK.iso-8859-1/common.lang.php @@ -231,8 +231,8 @@ $lang['category_representative'] = 'representative'; $lang['special_categories'] = 'specials'; $lang['most_visited_cat_hint'] = 'displays most visited pictures'; $lang['most_visited_cat'] = 'most visited'; -$lang['best_rated_cat_hint'] = 'displays pictures best rated'; $lang['best_rated_cat'] = 'best rated'; +$lang['best_rated_cat_hint'] = 'displays best rated items'; $lang['recent_pics_cat_hint'] = 'Displays most recent pictures'; $lang['recent_pics_cat'] = 'Last pictures'; $lang['recent_cats_cat_hint'] = 'Displays recently updated categories'; @@ -279,4 +279,12 @@ $lang['hello'] = 'Hello'; $lang['picture_show_metadata'] = 'Show file metadata ?'; $lang['picture_hide_metadata'] = 'Hide file metadata'; +$lang['to_rate'] = 'Rate'; +$lang['update_rate'] = 'Update your rating'; +$lang['element_rate'] = 'rate'; +$lang['already_rated'] = 'You\'ve already rated this item'; +$lang['never_rated'] = 'You\'ve never rated this item'; +$lang['no_rate'] = 'no rate'; +$lang['rates'] = 'rates'; +$lang['standard_deviation'] = 'STD'; ?> \ No newline at end of file diff --git a/picture.php b/picture.php index 891510b9f..9e15f5ce3 100644 --- a/picture.php +++ b/picture.php @@ -25,7 +25,8 @@ // | USA. | // +-----------------------------------------------------------------------+ -//----------------------------------------------------------- include +$rate_items = array(0,1,2,3,4,5); +//--------------------------------------------------------------------- include define('PHPWG_ROOT_PATH','./'); include_once( PHPWG_ROOT_PATH.'include/common.inc.php' ); //-------------------------------------------------- access authorization check @@ -191,16 +192,9 @@ foreach (array('prev', 'current', 'next') as $i) $picture[$i]['name'] = str_replace('_', ' ', $file_wo_ext); } - $picture[$i]['url'] = PHPWG_ROOT_PATH.'picture.php?image_id='.$row['id']; - $picture[$i]['url'].= '&cat='.$page['cat']; - if ( $page['cat'] == 'search' ) - { - $picture[$i]['url'].= '&search='.$_GET['search']; - } - if (isset($_GET['show_metadata'])) - { - $picture[$i]['url'].= '&show_metadata=1'; - } + $picture[$i]['url'] = PHPWG_ROOT_PATH.'picture.php'; + $picture[$i]['url'].= get_query_string_diff(array('image_id','add_fav')); + $picture[$i]['url'].= '&image_id='.$row['id']; } $url_home = PHPWG_ROOT_PATH.'category.php?cat='.$page['cat'].'&'; @@ -213,7 +207,41 @@ if ( $page['cat'] == 'search' ) $url_admin = PHPWG_ROOT_PATH.'admin.php?page=picture_modify'; $url_admin.= '&cat_id='.$page['cat']; $url_admin.= '&image_id='.$_GET['image_id']; - +//----------------------------------------------------------- rate registration +if (isset($_GET['rate']) + and $conf['rate'] + and !$user['is_the_guest'] + and in_array($_GET['rate'], $rate_items)) +{ + $query = ' +DELETE + FROM '.RATE_TABLE.' + WHERE user_id = '.$user['id'].' + AND element_id = '.$_GET['image_id'].' +;'; + mysql_query($query); + $query = ' +INSERT INTO '.RATE_TABLE.' + (user_id,element_id,rate) + VALUES + ('.$user['id'].','.$_GET['image_id'].','.$_GET['rate'].') +;'; + mysql_query($query); + + // update of images.average_rate field + $query = ' +SELECT ROUND(AVG(rate),2) AS average_rate + FROM '.RATE_TABLE.' + WHERE element_id = '.$_GET['image_id'].' +;'; + $row = mysql_fetch_array(mysql_query($query)); + $query = ' +UPDATE '.IMAGES_TABLE.' + SET average_rate = '.$row['average_rate'].' + WHERE id = '.$_GET['image_id'].' +;'; + mysql_query($query); +} //--------------------------------------------------------- favorite management if ( isset( $_GET['add_fav'] ) ) { @@ -536,7 +564,7 @@ $template->assign_block_vars('info_line', array( // filesize if (empty($picture['current']['filesize'])) { - if (!$picture[$i]['is_picture']) + if (!$picture['current']['is_picture']) { $filesize = floor(filesize($picture['current']['download'])/1024); } @@ -572,10 +600,43 @@ if ( !empty($picture['current']['keywords'])) )); } // number of visits -$template->assign_block_vars('info_line', array( +$template->assign_block_vars( + 'info_line', + array( 'INFO'=>$lang['visited'], 'VALUE'=>$picture['current']['hit'].' '.$lang['times'] )); +// rate results +if ($conf['rate']) +{ + $query = ' +SELECT COUNT(rate) AS count + , ROUND(AVG(rate),2) AS average + , ROUND(STD(rate),2) AS STD + FROM '.RATE_TABLE.' + WHERE element_id = '.$picture['current']['id'].' +;'; + $row = mysql_fetch_array(mysql_query($query)); + if ($row['count'] == 0) + { + $value = $lang['no_rate']; + } + else + { + $value = $row['average']; + $value.= ' ('; + $value.= $row['count'].' '.$lang['rates']; + $value.= ', '.$lang['standard_deviation'].' : '.$row['STD']; + $value.= ')'; + } + + $template->assign_block_vars( + 'info_line', + array( + 'INFO' => $lang['element_rate'], + 'VALUE' => $value + )); +} //-------------------------------------------------------------------- metadata if ($conf['show_exif'] or $conf['show_iptc']) { @@ -596,26 +657,7 @@ if ($metadata_showable and !isset($_GET['show_metadata'])) if ($metadata_showable and isset($_GET['show_metadata'])) { $url = PHPWG_ROOT_PATH.'picture.php'; - - $str = $_SERVER['QUERY_STRING']; - parse_str($str, $get_vars); - $is_first = true; - foreach ($get_vars as $key => $value) - { - if ($key != 'show_metadata') - { - if ($is_first) - { - $url.= '?'; - $is_first = false; - } - else - { - $url.= '&'; - } - $url.= $key.'='.$value; - } - } + $url.= get_query_string_diff(array('show_metadata','add_fav')); $template->assign_block_vars('hide_metadata', array('URL' => $url)); @@ -708,6 +750,57 @@ if ($metadata_showable and isset($_GET['show_metadata'])) } } } +//------------------------------------------------------------------- rate form +if ($conf['rate']) +{ + $query = ' +SELECT rate + FROM '.RATE_TABLE.' + WHERE user_id = '.$user['id'].' + AND element_id = '.$_GET['image_id'].' +;'; + $result = mysql_query($query); + if (mysql_num_rows($result) > 0) + { + $row = mysql_fetch_array($result); + $sentence = $lang['already_rated']; + $sentence.= ' ('.$row['rate'].'). '; + $sentence.= $lang['update_rate']; + } + else + { + $sentence = $lang['never_rated'].'. '.$lang['to_rate']; + } + $template->assign_block_vars( + 'rate', + array('SENTENCE' => $sentence) + ); + + + foreach ($rate_items as $num => $mark) + { + if ($num > 0) + { + $separator = '|'; + } + else + { + $separator = ''; + } + + $url = PHPWG_ROOT_PATH.'picture.php'; + $url.= get_query_string_diff(array('rate','add_fav')); + $url.= '&rate='.$mark; + + $template->assign_block_vars( + 'rate.rate_option', + array( + 'OPTION' => $mark, + 'URL' => $url, + 'SEPARATOR' => $separator + )); + } +} //------------------------------------------------------- favorite manipulation if ( !$user['is_the_guest'] ) { @@ -719,29 +812,33 @@ if ( !$user['is_the_guest'] ) $row = mysql_fetch_array( $result ); if (!$row['nb_fav']) { - $url = PHPWG_ROOT_PATH.'picture.php?cat='.$page['cat'].'&image_id='.$_GET['image_id']; - $url.='&add_fav=1'; - if ( $page['cat'] == 'search' ) - { - $url.= '&search='.$_GET['search']; - } - $template->assign_block_vars('favorite', array( - 'FAVORITE_IMG' => PHPWG_ROOT_PATH.'template/'.$user['template'].'/theme/favorite.gif', - 'FAVORITE_HINT' =>$lang['add_favorites_hint'], - 'FAVORITE_ALT' =>'[ '.$lang['add_favorites_alt'].' ]', - 'U_FAVORITE'=> add_session_id( $url ) - )); + $url = PHPWG_ROOT_PATH.'picture.php'; + $url.= get_query_string_diff(array('rate','add_fav')); + $url.= '&add_fav=1'; + + $template->assign_block_vars( + 'favorite', + array( + 'FAVORITE_IMG' => PHPWG_ROOT_PATH.'template/'.$user['template'].'/theme/favorite.gif', + 'FAVORITE_HINT' =>$lang['add_favorites_hint'], + 'FAVORITE_ALT' =>'[ '.$lang['add_favorites_alt'].' ]', + 'U_FAVORITE' => $url + )); } else { - $url = PHPWG_ROOT_PATH.'picture.php?cat='.$page['cat'].'&image_id='.$_GET['image_id']; + $url = PHPWG_ROOT_PATH.'picture.php'; + $url.= get_query_string_diff(array('rate','add_fav')); $url.= '&add_fav=0'; - $template->assign_block_vars('favorite', array( - 'FAVORITE_IMG' => PHPWG_ROOT_PATH.'template/'.$user['template'].'/theme/del_favorite.gif', - 'FAVORITE_HINT' =>$lang['del_favorites_hint'], - 'FAVORITE_ALT' =>'[ '.$lang['del_favorites_alt'].' ]', - 'U_FAVORITE'=> add_session_id( $url ) - )); + + $template->assign_block_vars( + 'favorite', + array( + 'FAVORITE_IMG' => PHPWG_ROOT_PATH.'template/'.$user['template'].'/theme/del_favorite.gif', + 'FAVORITE_HINT' =>$lang['del_favorites_hint'], + 'FAVORITE_ALT' =>'[ '.$lang['del_favorites_alt'].' ]', + 'U_FAVORITE'=> $url + )); } } //------------------------------------ admin link for information modifications @@ -761,12 +858,10 @@ if ( $conf['show_comments'] ) $row = mysql_fetch_array( mysql_query( $query ) ); // navigation bar creation - $url = PHPWG_ROOT_PATH.'picture.php?cat='.$page['cat'].'&image_id='.$_GET['image_id']; - if ( $page['cat'] == 'search' ) - { - $url.= '&search='.$_GET['search']; - } - if( !isset( $_GET['start'] ) + $url = PHPWG_ROOT_PATH.'picture.php'; + $url.= get_query_string_diff(array('rate','add_fav')); + + if (!isset( $_GET['start'] ) or !is_numeric( $_GET['start'] ) or ( is_numeric( $_GET['start'] ) and $_GET['start'] < 0 ) ) { diff --git a/template/default/category.tpl b/template/default/category.tpl index 2eb19b642..d718784f5 100644 --- a/template/default/category.tpl +++ b/template/default/category.tpl @@ -18,7 +18,8 @@
  • {L_SPECIAL_CATEGORIES}