From 8db124da81b8f90d6622ae8086ff57010a996355 Mon Sep 17 00:00:00 2001 From: z0rglub Date: Thu, 20 May 2004 21:22:44 +0000 Subject: field comments.date becomes a datetime MySQL field type (instead of int) git-svn-id: http://piwigo.org/svn/trunk@420 68402e56-0260-453c-a942-63ccdbb3a9ee --- comments.php | 45 +++--- include/functions.inc.php | 6 + install/dbscheme.txt | 270 ++++++++++++++++++------------------ install/phpwebgallery_structure.sql | 2 +- picture.php | 7 +- 5 files changed, 173 insertions(+), 157 deletions(-) diff --git a/comments.php b/comments.php index 1ae42749f..f829e44d7 100644 --- a/comments.php +++ b/comments.php @@ -91,13 +91,16 @@ $template->assign_vars(array( ); foreach ( $conf['last_days'] as $option ) { - $url = $PHP_SELF.'?last_days='.($option - 1); + $url = $_SERVER['PHP_SELF'].'?last_days='.($option - 1); if (defined('IN_ADMIN')) $url.= '&page=comments'; - $template->assign_block_vars('last_day_option', array ( - 'OPTION'=>$option, - 'T_STYLE'=>(( $option == MAX_DAYS + 1 )?'text-decoration:underline;':''), - 'U_OPTION'=>add_session_id( $url ) - )); + $template->assign_block_vars( + 'last_day_option', + array( + 'OPTION'=>$option, + 'T_STYLE'=>(( $option == MAX_DAYS + 1 )?'text-decoration:underline;':''), + 'U_OPTION'=>add_session_id( $url ) + ) + ); } // 1. retrieving picture ids which have comments recently added @@ -109,7 +112,7 @@ $query.= '(ic.category_id) as category_id'; $query.= ' FROM '.COMMENTS_TABLE.' AS c'; $query.= ', '.IMAGE_CATEGORY_TABLE.' AS ic'; $query.= ' WHERE c.image_id = ic.image_id'; -$query.= ' AND date > '.$maxtime; +$query.= ' AND date > FROM_UNIXTIME('.$maxtime.')'; if ( $user['status'] != 'admin' ) { $query.= " AND validated = 'true'"; @@ -173,7 +176,7 @@ while ( $row = mysql_fetch_array( $result ) ) // for each picture, retrieving all comments $query = 'SELECT * FROM '.COMMENTS_TABLE; $query.= ' WHERE image_id = '.$row['image_id']; - $query.= ' AND date > '.$maxtime; + $query.= ' AND date > FROM_UNIXTIME('.$maxtime.')'; if ( $user['status'] != 'admin' ) { $query.= " AND validated = 'true'"; @@ -201,18 +204,20 @@ while ( $row = mysql_fetch_array( $result ) ) $pattern = '/\/([^\s]*)\//'; $replacement = '\1'; $content = preg_replace( $pattern, $replacement, $content ); - $template->assign_block_vars('picture.comment',array( - 'COMMENT_AUTHOR'=>$author, - 'COMMENT_DATE'=>format_date( $subrow['date'], 'unix', true ), - 'COMMENT'=>$content, - )); - if ( $user['status'] == 'admin' ) - { - $template->assign_block_vars('picture.comment.validation', array( - 'ID'=> $subrow['id'], - 'CHECKED'=>($subrow['validated']=='false')?'checked="checked"': '' - )); - } + $template->assign_block_vars( + 'picture.comment',array( + 'COMMENT_AUTHOR'=>$author, + 'COMMENT_DATE'=>format_date( $subrow['date'],'mysql_datetime',true ), + 'COMMENT'=>$content, + )); + if ( $user['status'] == 'admin' ) + { + $template->assign_block_vars( + 'picture.comment.validation', array( + 'ID'=> $subrow['id'], + 'CHECKED'=>($subrow['validated']=='false')?'checked="checked"': '' + )); + } } } //----------------------------------------------------------- html code display diff --git a/include/functions.inc.php b/include/functions.inc.php index 4337cf81a..01f13f78d 100644 --- a/include/functions.inc.php +++ b/include/functions.inc.php @@ -374,6 +374,12 @@ function format_date( $date, $type = 'us', $show_time = false ) case 'unix' : $unixdate = $date; break; + case 'mysql_datetime' : + preg_match( '/^(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})$/', + $date, $matches ); + $unixdate = mktime($matches[4],$matches[5],$matches[6], + $matches[2],$matches[3],$matches[1]); + break; } $formated_date = $lang['day'][date( "w", $unixdate )]; $formated_date.= date( " j ", $unixdate ); diff --git a/install/dbscheme.txt b/install/dbscheme.txt index 3ee5b8ea6..06c757949 100644 --- a/install/dbscheme.txt +++ b/install/dbscheme.txt @@ -1,133 +1,137 @@ -table categories -table comments -table config -table favorites -table group_access -table groups -table history -table image_category -table images -table sessions -table sites -table user_access -table user_category -table user_group -table users -table waiting -column id categories smallint(5) unsigned -column date_last categories date -column nb_images categories mediumint(8) unsigned -column name categories varchar(255) -column id_uppercat categories smallint(5) unsigned -column comment categories text -column dir categories varchar(255) -column rank categories tinyint(3) unsigned -column status categories enum('public','private') -column site_id categories tinyint(4) unsigned -column visible categories enum('true','false') -column uploadable categories enum('true','false') -column representative_picture_id categories mediumint(8) unsigned -column uppercats categories varchar(255) -column id comments int(11) unsigned -column image_id comments mediumint(8) unsigned -column date comments int(11) unsigned -column author comments varchar(255) -column content comments longtext -column validated comments enum('true','false') -column param config varchar(40) -column value config varchar(255) -column comment config varchar(255) -column user_id favorites smallint(5) unsigned -column image_id favorites mediumint(8) unsigned -column group_id group_access smallint(5) unsigned -column cat_id group_access smallint(5) unsigned -column id groups smallint(5) unsigned -column name groups varchar(255) -column date history int(11) -column login history varchar(15) -column IP history varchar(50) -column category history varchar(150) -column file history varchar(50) -column picture history varchar(150) -column image_id image_category mediumint(8) unsigned -column category_id image_category smallint(5) unsigned -column id images mediumint(8) unsigned -column file images varchar(255) -column date_available images date -column date_creation images date -column tn_ext images char(3) -column name images varchar(255) -column comment images text -column author images varchar(255) -column hit images int(10) unsigned -column filesize images mediumint(9) unsigned -column width images smallint(9) unsigned -column height images smallint(9) unsigned -column keywords images varchar(255) -column storage_category_id images smallint(5) unsigned -column id sessions varchar(255) binary -column user_id sessions smallint(5) unsigned -column expiration sessions int(10) unsigned -column ip sessions varchar(255) -column id sites tinyint(4) -column galleries_url sites varchar(255) -column user_id user_access smallint(5) unsigned -column cat_id user_access smallint(5) unsigned -column user_id user_category smallint(5) unsigned -column category_id user_category smallint(5) unsigned -column date_last user_category date -column nb_sub_categories user_category smallint(5) unsigned -column user_id user_group smallint(5) unsigned -column group_id user_group smallint(5) unsigned -column id users smallint(5) unsigned -column username users varchar(20) binary -column password users varchar(255) -column mail_address users varchar(255) -column nb_image_line users tinyint(1) unsigned -column nb_line_page users tinyint(3) unsigned -column status users enum('admin','guest') -column language users varchar(50) -column maxwidth users smallint(6) -column maxheight users smallint(6) -column expand users enum('true','false') -column show_nb_comments users enum('true','false') -column short_period users tinyint(3) unsigned -column long_period users tinyint(3) unsigned -column template users varchar(255) -column forbidden_categories users text -column id waiting int(10) unsigned -column storage_category_id waiting smallint(5) unsigned -column file waiting varchar(255) -column username waiting varchar(255) -column mail_address waiting varchar(255) -column date waiting int(10) unsigned -column tn_ext waiting char(3) -column validated waiting enum('true','false') -column infos waiting text -pk id categories -pk id comments -pk param config -pk user_id favorites -pk image_id favorites -pk group_id group_access -pk cat_id group_access -pk id groups -pk image_id image_category -pk category_id image_category -pk id images -pk id sessions -pk id sites -pk user_id user_access -pk cat_id user_access -pk user_id user_category -pk category_id user_category -pk group_id user_group -pk user_id user_group -pk id users -pk id waiting -index categories_i2 categories id_uppercat -index image_category_i1 image_category image_id -index image_category_i2 image_category category_id -index storage_category_id images storage_category_id -index sites_ui1 sites galleries_url -index users_ui1 users username + +table:categories +table:comments +table:config +table:favorites +table:group_access +table:groups +table:history +table:image_category +table:images +table:sessions +table:sites +table:user_access +table:user_category +table:user_group +table:users +table:waiting + +column:id table:categories type:smallint nullable:Y length:5 signed:N +column:date_last table:categories type:date nullable:N +column:nb_images table:categories type:mediumint nullable:Y length:8 signed:N +column:name table:categories type:varchar nullable:Y length:255 binary:N +column:id_uppercat table:categories type:smallint nullable:N length:5 signed:N +column:comment table:categories type:text nullable:N +column:dir table:categories type:varchar nullable:N length:255 binary:N +column:rank table:categories type:tinyint nullable:N length:3 signed:N +column:status table:categories type:enum('public','private') nullable:Y +column:site_id table:categories type:tinyint nullable:Y length:4 signed:N +column:visible table:categories type:enum('true','false') nullable:Y +column:uploadable table:categories type:enum('true','false') nullable:Y +column:representative_picture_id table:categories type:mediumint nullable:N length:8 signed:N +column:uppercats table:categories type:varchar nullable:Y length:255 binary:N +column:id table:comments type:int nullable:Y length:11 signed:N +column:image_id table:comments type:mediumint nullable:Y length:8 signed:N +column:date table:comments type:datetime nullable:Y +column:author table:comments type:varchar nullable:N length:255 binary:N +column:content table:comments type:longtext nullable:N +column:validated table:comments type:enum('true','false') nullable:Y +column:param table:config type:varchar nullable:Y length:40 binary:N +column:value table:config type:varchar nullable:N length:255 binary:N +column:comment table:config type:varchar nullable:N length:255 binary:N +column:user_id table:favorites type:smallint nullable:Y length:5 signed:N +column:image_id table:favorites type:mediumint nullable:Y length:8 signed:N +column:group_id table:group_access type:smallint nullable:Y length:5 signed:N +column:cat_id table:group_access type:smallint nullable:Y length:5 signed:N +column:id table:groups type:smallint nullable:Y length:5 signed:N +column:name table:groups type:varchar nullable:Y length:255 binary:N +column:date table:history type:int nullable:Y length:11 signed:Y +column:login table:history type:varchar nullable:N length:15 binary:N +column:IP table:history type:varchar nullable:Y length:50 binary:N +column:category table:history type:varchar nullable:N length:150 binary:N +column:file table:history type:varchar nullable:N length:50 binary:N +column:picture table:history type:varchar nullable:N length:150 binary:N +column:image_id table:image_category type:mediumint nullable:Y length:8 signed:N +column:category_id table:image_category type:smallint nullable:Y length:5 signed:N +column:id table:images type:mediumint nullable:Y length:8 signed:N +column:file table:images type:varchar nullable:Y length:255 binary:N +column:date_available table:images type:date nullable:Y +column:date_creation table:images type:date nullable:N +column:tn_ext table:images type:char nullable:Y length:3 binary:N +column:name table:images type:varchar nullable:N length:255 binary:N +column:comment table:images type:text nullable:N +column:author table:images type:varchar nullable:N length:255 binary:N +column:hit table:images type:int nullable:Y length:10 signed:N +column:filesize table:images type:mediumint nullable:N length:9 signed:N +column:width table:images type:smallint nullable:N length:9 signed:N +column:height table:images type:smallint nullable:N length:9 signed:N +column:keywords table:images type:varchar nullable:N length:255 binary:N +column:storage_category_id table:images type:smallint nullable:N length:5 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 +column:ip table:sessions type:varchar nullable:Y length:255 binary:N +column:id table:sites type:tinyint nullable:Y length:4 signed:Y +column:galleries_url table:sites type:varchar nullable:Y length:255 binary:N +column:user_id table:user_access type:smallint nullable:Y length:5 signed:N +column:cat_id table:user_access type:smallint nullable:Y length:5 signed:N +column:user_id table:user_category type:smallint nullable:Y length:5 signed:N +column:category_id table:user_category type:smallint nullable:Y length:5 signed:N +column:date_last table:user_category type:date nullable:N +column:nb_sub_categories table:user_category type:smallint nullable:Y length:5 signed:N +column:user_id table:user_group type:smallint nullable:Y length:5 signed:N +column:group_id table:user_group type:smallint nullable:Y length:5 signed:N +column:id table:users type:smallint nullable:Y length:5 signed:N +column:username table:users type:varchar nullable:Y length:20 binary:Y +column:password table:users type:varchar nullable:Y length:255 binary:N +column:mail_address table:users type:varchar nullable:N length:255 binary:N +column:nb_image_line table:users type:tinyint nullable:Y length:1 signed:N +column:nb_line_page table:users type:tinyint nullable:Y length:3 signed:N +column:status table:users type:enum('admin','guest') nullable:Y +column:language table:users type:varchar nullable:Y length:50 binary:N +column:maxwidth table:users type:smallint nullable:N length:6 signed:Y +column:maxheight table:users type:smallint nullable:N length:6 signed:Y +column:expand table:users type:enum('true','false') nullable:Y +column:show_nb_comments table:users type:enum('true','false') nullable:Y +column:short_period table:users type:tinyint nullable:Y length:3 signed:N +column:long_period table:users type:tinyint nullable:Y length:3 signed:N +column:template table:users type:varchar nullable:Y length:255 binary:N +column:forbidden_categories table:users type:text nullable:N +column:id table:waiting type:int nullable:Y length:10 signed:N +column:storage_category_id table:waiting type:smallint nullable:Y length:5 signed:N +column:file table:waiting type:varchar nullable:Y length:255 binary:N +column:username table:waiting type:varchar nullable:Y length:255 binary:N +column:mail_address table:waiting type:varchar nullable:Y length:255 binary:N +column:date table:waiting type:int nullable:Y length:10 signed:N +column:tn_ext table:waiting type:char nullable:N length:3 binary:N +column:validated table:waiting type:enum('true','false') nullable:Y +column:infos table:waiting type:text nullable:N + +PK:categories_pk table:categories column:id +PK:comments_pk table:comments column:id +PK:config_pk table:config column:param +PK:favorites_pk table:favorites column:user_id +PK:favorites_pk table:favorites column:image_id +PK:group_access_pk table:group_access column:group_id +PK:group_access_pk table:group_access column:cat_id +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:sessions_pk table:sessions column:id +PK:sites_pk table:sites column:id +PK:user_access_pk table:user_access column:user_id +PK:user_access_pk table:user_access column:cat_id +PK:user_category_pk table:user_category column:user_id +PK:user_category_pk table:user_category column:category_id +PK:user_group_pk table:user_group column:group_id +PK:user_group_pk table:user_group column:user_id +PK:users_pk table:users column:id +PK:waiting_pk table:waiting column:id + +index:categories_i2 table:categories column:id_uppercat +index:image_category_i1 table:image_category column:image_id +index:image_category_i2 table:image_category column:category_id +index:storage_category_id table:images column:storage_category_id +index:sites_ui1 table:sites column:galleries_url +index:users_ui1 table:users column:username diff --git a/install/phpwebgallery_structure.sql b/install/phpwebgallery_structure.sql index ba3102af0..a6d28e7be 100644 --- a/install/phpwebgallery_structure.sql +++ b/install/phpwebgallery_structure.sql @@ -36,7 +36,7 @@ DROP TABLE IF EXISTS phpwebgallery_comments; CREATE TABLE phpwebgallery_comments ( id int(11) unsigned NOT NULL auto_increment, image_id mediumint(8) unsigned NOT NULL default '0', - date int(11) unsigned NOT NULL default '0', + date datetime NOT NULL default '0000-00-00 00:00:00', author varchar(255) default NULL, content longtext, validated enum('true','false') NOT NULL default 'false', diff --git a/picture.php b/picture.php index 07b72167e..6f1c7c149 100644 --- a/picture.php +++ b/picture.php @@ -24,6 +24,7 @@ // | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, | // | USA. | // +-----------------------------------------------------------------------+ + //----------------------------------------------------------- include define('PHPWG_ROOT_PATH','./'); include_once( PHPWG_ROOT_PATH.'include/common.inc.php' ); @@ -224,7 +225,7 @@ if ( isset( $_POST['content'] ) && !empty($_POST['content']) ) // anti-flood system $reference_date = time() - $conf['anti-flood_time']; $query = 'SELECT id FROM '.COMMENTS_TABLE; - $query.= ' WHERE date > '.$reference_date; + $query.= ' WHERE date > FROM_UNIXTIME('.$reference_date.')'; $query.= " AND author = '".$author."'"; $query.= ';'; if ( mysql_num_rows( mysql_query( $query ) ) == 0 @@ -233,7 +234,7 @@ if ( isset( $_POST['content'] ) && !empty($_POST['content']) ) $query = 'INSERT INTO '.COMMENTS_TABLE; $query.= ' (author,date,image_id,content,validated) VALUES ('; $query.= "'".$author."'"; - $query.= ','.time().','.$_GET['image_id']; + $query.= ',NOW(),'.$_GET['image_id']; $query.= ",'".htmlspecialchars( $_POST['content'], ENT_QUOTES)."'"; if ( !$conf['comments_validation'] or $user['status'] == 'admin' ) { @@ -577,7 +578,7 @@ if ( $conf['show_comments'] ) $template->assign_block_vars('comments.comment', array( 'COMMENT_AUTHOR'=>empty($row['author'])?$lang['guest']:$row['author'], - 'COMMENT_DATE'=>format_date( $row['date'], 'unix', true ), + 'COMMENT_DATE'=>format_date( $row['date'], 'mysql_datetime', true ), 'COMMENT'=>$content )); -- cgit v1.2.3