aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorz0rglub <z0rglub@piwigo.org>2004-05-20 21:22:44 +0000
committerz0rglub <z0rglub@piwigo.org>2004-05-20 21:22:44 +0000
commit8db124da81b8f90d6622ae8086ff57010a996355 (patch)
tree4b0b5eceb204eea75d017a2fd895bd752915b691
parent396d68275d6fe79ffdf07a378d7e46ebe60bcc7b (diff)
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
-rw-r--r--comments.php45
-rw-r--r--include/functions.inc.php6
-rw-r--r--install/dbscheme.txt270
-rw-r--r--install/phpwebgallery_structure.sql2
-rw-r--r--picture.php7
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.= '&amp;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 = '<span style="font-style:italic;">\1</span>';
$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
));