Looking at _commentlist.class.php a bit more closely, it looks to me like in this section

		$this->sql = 'SELECT DISTINCT T_comments.*
									FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';

		if( !empty( $p ) )
		{	// Restrict to comments on selected post
			$this->sql .= " WHERE comment_post_ID = $p AND ";
		}
		elseif( $blog > 1 )
		{	// Restrict to viewable posts/cats on current blog
			$this->sql .= "INNER JOIN T_postcats ON post_ID = postcat_post_ID INNER JOIN T_categories othercats ON postcat_cat_ID = othercats.cat_ID WHERE othercats.cat_blog_ID = $blog AND ";
		}
		else
		{	// This is blog 1, we don't care, we can include all comments:
			$this->sql .= ' WHERE ';
		}

the only place that the

SELECT DISTINCT

is needed in the second clause (

elseif( $blog >1 )

). If this is the case, should the

SELECT DISTINCT

be pushed into that block?

AWESOME FIX!!!! I'm on 0.9.0.10 and I was having the exact same problem with haywire order of comments (that only started happening sometime around sep 7?)... I tried this simple fix here and VOILA! It's all good now!

SO THANK YOU!!!!

This seemed to work for me, as well.

I noticed the problem in our production v1.8 (Summer Beta) version and went back to compare in v0.9.1 (DAWN), which I keep around, for posterity. :| I noticed a similar juxtaposition of comments for the same post. Looked at the DB (all okay there). Removed the "distinct" and blammo ... the correct order. :D

Don't yet know if there is any fallout elsewhere, but all preliminary indications are - "This Rocks Dude!"

Thanks!!

Yes, it worked in my case as well, but now there are problems with my "recent comments". Every post appears 3-5 times

nomad,

Yer right. Same thing here. (Not something I have linked on my front page, but when I typed in the URL Parms ... I noticed the same thing).

Seems like a simple fix would be to put a branch in that function code?

if($disp=='comments') {
old code
} else { 
new code (w/out the distinct) 
}

(untested, of course, because I need all the comments I can get ... even if they're a tad redundant) :p

It may be brutish, but it seems to work for me?

<?php

global $disp;

if($disp=='comments'){
		$this->sql = 'SELECT DISTINCT T_comments.*
									FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID '; 
		} else {
		
		$this->sql = 'SELECT T_comments.*
									FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID '; 
		}
?>

All, of course, in inc/MODEL/comments/_commentlist.class.php file.

Hope this helps.

Does it matter where I place the code?

Sorry, yes ...

Replace the original code:

<?php

$this->sql = 'SELECT DISTINCT T_comments.* 
                                    FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';  


?>

(From which, you MAY have already deleted the "DISTINCT" part).

with the stuff above (repeated here):

<?php

global $disp; 

if($disp=='comments'){ 
        $this->sql = 'SELECT DISTINCT T_comments.* 
                                    FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';  
        } else { 
         
        $this->sql = 'SELECT T_comments.* 
                                    FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';  
        } 
?> 

That should do it for you. :D

Thanks but as I still use 0.91 and the syntax has changed, I've received a syntax error. I've tried adjusting it but without result

My original code that I should replace is:

$this->request = "SELECT $tablecomments.*
FROM (($tablecomments INNER JOIN $tableposts ON comment_post_ID = ID) ";

The error message:

MySQL error!

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN evo_postcats ON ID = postcat_post_ID) INNER JOIN evo_categories other' at line 1(Errno=1064)

Your query:

INNER JOIN evo_postcats ON ID = postc

OKAY ... (I didn't check v0.9.x)

The idea, however, should be the same (i.e., you originally had a problem with comment order, but when you eliminate DISTINCT, your lastcomments repeat ... right?)

So ..

WHATEVER statement you replaced DISTINCT on, works fine for recent comments

The concept is:

global $disp;

if($disp=='comments') {
do the thing WITH DISTINCT in it 
}else{
do the thing WITHOUT DISTINCT 
}

Make sense?

I assume that the v0.9.x "thing" is

$this->request = "SELECT $tablecomments.* 
FROM (($tablecomments INNER JOIN $tableposts ON comment_post_ID = ID) "; 

Thanks, i'd had this problem with comment appearing out of sequence on apparently random occasions. All fixed now, though I suppose I should have just pestered my hosts into upgrading MySQL.

Could someone please post the exact code to modify 0.9.0.10 (in file x replace ... with), the thread got a tad confusing and I don't want to mess stuff up.

I take it there wasn't anything to fix this in 1.8.6 and I have to apply the hack again?

I think it's more a "If-yer-host-is-running-mySQL-version-4.1.21-thing". I just noticed that v1.9.2 has the same problem for me and I had to redo the whole core-modification.

<?php 
		global $disp;						
	  if($disp=='comments') { 
       $this->sql = 'SELECT DISTINCT T_comments.*
								     FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID '; 
    } else { 
       $this->sql = 'SELECT T_comments.*
									   FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID '; 
    }
?>

Hope this helps.

well, I'm stumped. All of a sudden in 1.9.2 I get recent comments out of order again when I already made changes to _commentlist.class.php to fix the problem. Here is my _commentlist.class.php and I thank anyone that would point out exactly what I need to fix. Future bless you's to who responds. Thanks.

BTW I think my host is using PHP 5.

<?php
/**
 * This file implements the CommentList class.
 *
 * This file is part of the b2evolution/evocms project - {@link http://b2evolution.net/}.
 * See also {@link http://sourceforge.net/projects/evocms/}.
 *
 * @copyright (c)2003-2006 by Francois PLANQUE - {@link http://fplanque.net/}.
 * Parts of this file are copyright (c)2004-2005 by Daniel HAHLER - {@link http://thequod.de/contact}.
 *
 * @license http://b2evolution.net/about/license.html GNU General Public License (GPL)
 *
 * {@internal Open Source relicensing agreement:
 * Daniel HAHLER grants Francois PLANQUE the right to license
 * Daniel HAHLER's contributions to this file and the b2evolution project
 * under any OSI approved OSS license (http://www.opensource.org/licenses/).
 * }}
 *
 * @package evocore
 *
 * {@internal Below is a list of authors who have contributed to design/coding of this file: }}
 * @author blueyed: Daniel HAHLER.
 * @author fplanque: Francois PLANQUE
 *
 * @version $Id: _commentlist.class.php,v 1.5.2.2 2006/11/04 19:54:53 fplanque Exp $
 */
if( !defined('EVO_MAIN_INIT') ) die( 'Please, do not access this page directly.' );

/**
 * Includes:
 */
require_once dirname(__FILE__).'/../dataobjects/_dataobjectlist.class.php';

/**
 * CommentList Class
 *
 * @package evocore
 */
class CommentList extends DataObjectList
{
	var $blog;

	/**
	 * Constructor
	 */
	function CommentList(
		$blog = 1,
		$comment_types = "'comment'",
		$show_statuses = array( 'published' ),	// Restrict to these statuses
		$p = '',															// Restrict to specific post
		$author = '',													// Not used yet
		$order = 'DESC',											// ASC or DESC
		$orderby = '',												// list of fields to order by
		$limit = '' 													// # of comments to display on the page
		)
	{
		global $DB;
		global $cache_categories;
		global $pagenow;		// Bleh !

		// Call parent constructor:
		parent::DataObjectList( 'T_comments', 'comment_', 'comment_ID', 'Item', NULL, $limit );

		$this->blog = $blog;


		$this->sql = 'SELECT DISTINCT T_comments.*
									FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';

		if( !empty( $p ) )
		{	// Restrict to comments on selected post
			$this->sql .= " WHERE comment_post_ID = $p AND ";
		}
		elseif( $blog > 1 )
		{	// Restrict to viewable posts/cats on current blog
			$this->sql .= "INNER JOIN T_postcats ON post_ID = postcat_post_ID INNER JOIN T_categories othercats ON postcat_cat_ID = othercats.cat_ID WHERE othercats.cat_blog_ID = $blog AND ";
		}
		else
		{	// This is blog 1, we don't care, we can include all comments:
			$this->sql .= ' WHERE ';
		}

		$this->sql .= "comment_type IN ($comment_types) ";

		/*
		 * ----------------------------------------------------
		 *  Restrict to the statuses we want to show:
		 * ----------------------------------------------------
		 */
		if( ! empty( $show_statuses ) )
		{
			$this->sql .= ' AND comment_status IN (\''.implode( "', '", $show_statuses ).'\')';
		}

		// This one restricts to post statuses, but it doesn't work completely right:
		// TODO: handle status dependencies with post
		$this->sql .= ' AND '.statuses_where_clause();


		// order by stuff
		if( (!empty($order)) && ((strtoupper($order) != 'ASC') && (strtoupper($order) != 'DESC')))
		{
			$order='DESC';
		}

		if(empty($orderby))
		{
			$orderby = 'comment_date '.$order;
		}
		else
		{
			$orderby_array = explode(' ',$orderby);
			$orderby = $orderby_array[0].' '.$order;
			if (count($orderby_array)>1)
			{
				for($i = 1; $i < (count($orderby_array)); $i++)
				{
					$orderby .= ', comment_'.$orderby_array[$i].' '.$order;
				}
			}
		}


		$this->sql .= "ORDER BY $orderby";
		if( !empty( $this->limit ) )
		{
			$this->sql .= ' LIMIT '.$this->limit;
		}

		// echo $this->sql;

		$this->rows = $DB->get_results( $this->sql, ARRAY_A );

		// Prebuild and cache objects:
		if( $this->result_num_rows = $DB->num_rows )
		{	// fplanque>> why this test??

			$i = 0;
			foreach( $this->rows as $row )
			{
				// Prebuild object:
				$this->Obj[$i] = new Comment( $row ); // COPY (function)

				// To avoid potential future waste, cache this object:
				// $this->DataObjectCache->add( $this->Obj[$i] );

				$i++;
			}
		}
	}


	/**
	 * Template function: display message if list is empty
	 *
	 * @param string String to display if list is empty
   * @return true if empty
	 */
	function display_if_empty( $message = '' )
	{
		if( empty($message) )
		{	// Default message:
			$message = T_('No comment yet...');
		}

		return parent::display_if_empty( $message );
	}

}

/*
 nolog */
?>

Read the post right before yours and I *think* your solution is in there.

AFAIK, you need to change

<?php 
$this->sql = 'SELECT DISTINCT T_comments.* 
FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID '; 
?>

to

<?php
$this->sql = 'SELECT  T_comments.* 
FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID '; 
?>

Dunno about php versions (I'm on 5.0.5 and using the hack for 1.9.3 that I mention) without issue.

Give it a whirl. (Might need to do the IF thingie, if you're also showing recent comments).

Hope this helps.

OK, I removed any DISTINCTs so that everything should now be ok, I got confused with all the if with DISTINCT then if without DISTINCT so I don't know why those if codes were there in some examples when all needed was to delete all DISTINCTs (if I got that right)

bless me, bless me, bless me :D

Actually you're six times blessed, I was so happy that I doubled the thrice-blessing :-)

Thanks STK, I'm a little late to the game on this thread, but I just discovered the same problem, saw this thread and using your advice, got the fix.

Bless you again.

Hi, I am on 1.8.6 and have a problem with comment sorting. I assumed it was having our dates in reverse order 16/05 not 05/16, but maybe it is something else.

I just tried the code from Thu Nov 09, 2006 13:04
where I replaced:

$this->sql = 'SELECT DISTINCT T_comments.* 
                                    FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';  

with

global $disp; 

if($disp=='comments'){ 
        $this->sql = 'SELECT DISTINCT T_comments.* 
                                    FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';  
        } else { 
         
        $this->sql = 'SELECT T_comments.* 
                                    FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';  
        } 

and it clanged with a php error.

I omitted the php opening and closing bits because they were not there already, unlike the example given.

I should mention that I only vaguely understand php (well even that is an exaggeration).

I applied the same solution after upgrading to 1.10.2. I had forgotten I needed to do this and expected to be fixed in this version of b2evo. Anyway, the fix works, Thanks!

--

Sorry, I forgot to check the backoffice before posting this. In the backoffice, comments appear duplicated or triplicated. (blog/admin.php?ctrl=browse&show_past=1&show_future=1&tab=comments) but in the "Last Commetns" in the public "face" of the blog they appear as intended, only once,

One thing I noticed just now, after implementing this edit to fix the sort order, is that comments appear multiple times in the admin area and under the "Most Recent Comments" link on the blog itself.... If a comment is made to post which has been crossposted in 2 categories, the comment will appear twice in both lists... if crossposted in 3 categories, it will appear 3 times.. etc...

Anyone else experience this? As soon as I added "DISTINCT" back into the /inc/MODEL/comments/_commentlist.class.php file, the multiple copies of the comments disappeared...

jj.