Please login or register.

Login with username, password and session length
Advanced search  

News:

You need/want an older version of sNews ? Download an older/unsupported version here.

Pages: [1] 2 3

Author Topic: [WIP] Threaded comments  (Read 3221 times)

nukpana

  • Hero Member
  • *****
  • Karma: 71
  • Posts: 663
[WIP] Threaded comments
« on: October 07, 2010, 04:42:11 AM »

SEE THIS THREAD FOR AN UPDATE
http://snewscms.com/forum/index.php?topic=9461.msg65822#msg65822



Tired, so I am dropping this for someone to pick up....

SQL:
Code: [Select]
   CREATE TABLE `comments` (
    `id` int(11) primary key auto_increment,
    `parent` int(8) DEFAULT NULL,
     `articleid` int(11) default '0',
     `name` varchar(50),
     `url` varchar(100) NOT NULL,
     `comment` text,
     `time` datetime NOT NULL default '0000-00-00 00:00:00',
     `approved` varchar(5) NOT NULL default 'True',
     KEY articleid (articleid)
    );

Comment out function comments

New code:
Code: [Select]
function comment( $freeze_status ) {
if (isset($_POST['comment'])) {
add_comment();
} else {
echo '<h3>Comments</h3>';
show_comments('', $freeze_status);
comment_form('', $freeze_status);
comment_pagination();
}
}

show_comments & comment_list
TO DO - add ADMIN links
Code: [Select]
function show_comments($id = '', $freeze_status = '') {
$comments = comment_list($id);
foreach ($comments as $comment) {
$child_class = !empty($comment['parent'])
? ' class="subcat"'
: '';
echo '
<div '.$child_class.'>
<p class="meta">
'.$comment['name'].'
<a
href="'._SITE . $_GET['category'].'#Comment'.$comment['number'].'"
id="'.$comment['number'].'"
name="'.$comment['number'].'"
>
'.$comment['datetime'].'
</a>';
if (!empty($comment['url'])) {
echo '<a href="'.$comment['url'].'">'.$comment['url'].'</a>';
}
echo '
</p>
<p class="comment">'.$comment['comment'].'</p>
<p>Reply:</p>
</div>';
comment_form($comment['id'], $freeze_status);
}
}

function comment_list($id = '') {
static $list;
if (!$list) {
global $_ID, $commentsPage;
$list = array();
$offset = (comment_page() - 1) * s('comment_limit');
$id = !empty($id)
? ' AND parent = '.$id
: '';
$query = 'SELECT
id, parent, articleid, name, url, comment, time, approved
FROM '._PRE.'comments
WHERE articleid = '.$_ID.'
AND approved = "True"
'.$id.'
ORDER BY COALESCE(parent,id), id '.s('comments_order').'
LIMIT '.$offset.', '.s('comment_limit');
$result = mysql_query($query) or die(l('dberror'));
$ordinal = 1;
$date_format = s('date_format');
$edit_link = ' <a href="'._SITE.'?action=';
while ($r = mysql_fetch_assoc($result)) {
$r['datetime'] = date($date_format, strtotime($r['time']));
$r['number'] = $offset + $ordinal;
$ordinal++;
$list[] = $r;
}
}
return $list;
}

comment_pagination
Code: [Select]
function comment_pagination() {
    global $articleSEF, $_ID, $commentsPage;
    if ($_ID) {
         if (strpos($articleSEF, l('paginator')) === 0) {
             $articleSEF = str_replace(
                l('paginator'),
                '',
                $articleSEF
            );
         }
         $comments_order = s('comments_order');
        $commentCount = s('comment_limit');
        $comment_limit = (empty($commentCount) || $commentCount < 1)
           ? 100
            : $commentCount;

        $pageNum = comment_page();
        $offset = ($pageNum - 1) * $comment_limit;
        $totalrows = 'SELECT count(id) AS num FROM '._PRE.'comments'.'
            WHERE articleid = '.$_ID.' AND approved = \'True\';';
        $rowsresult = mysql_query($totalrows);
        $numrows = mysql_fetch_array($rowsresult);
        $numrows = $numrows['num'];
       $maxPage = ceil($numrows / $comment_limit);
        $back_to_page = ceil(($numrows + 1) / $comment_limit);
        if ($maxPage > 1) {
            paginator($pageNum, $maxPage,l('comment_pages'));
        }
    }
}

function comment_page() {
global $commentsPage;
  if (isset($commentsPage)) {
  $num = str_replace(
l('comment_pages'),
'',
$commentsPage
);
  }
  if (
  !isset($num)
|| !is_numeric($num)
|| $num < 1
) {
  $num = 1;
  }
return $num;
}

comment_form.... the threads use this form in the loop - revision may be needed....
Code: [Select]
function comment_form($commentID = '', $freeze_status = '') {
    global $categorySEF, $subcatSEF, $articleSEF, $_ID, $commentsPage;
    if ($freeze_status != 'freezed' && s('freeze_comments') != 'YES') {
        if($_SESSION[_SITE.'comment']['fail'] == true) {
            $name         = $_SESSION[_SITE.'comment']['name'];
            $comment     = $_SESSION[_SITE.'comment']['comment'];
            $url         = $_SESSION[_SITE.'comment']['url'];
            unset($_SESSION[_SITE.'comment']);
        } else {
            $url = $name = $comment = '';
       }

        $art_value = empty($articleSEF)
            ? $subcatSEF
            : $articleSEF;
        ?>
    <div class="commentsbox">
        <h2><?php echo l('addcomment'); ?></h2>
        <p><?php echo l('required'); ?></p>
        <form method="post" action="<?php echo _SITE?>" id="post" accept-charset="<?php echo s('charset'); ?>">
            <p>
                <label for="name">* <?php echo l('name'); ?>:</label><br />
                <input type="text" name="name" id="name" class="text" value="<?php echo $name?>" />
            </p>
            <p>
                <label for="url"><?php echo l('url'); ?>:</label><br />
                <input type="text" name="url" id="url" class="text" value="<?php echo $url?>" />
            </p>
            <p>
                <label for="text">* <?php echo l('comment'); ?>:</label><br />
                <textarea name="text" rows="5" cols="5" id="text"><?php echo $comment?></textarea>
            </p>
            <?php echo mathCaptcha(); ?>
            <p>
                <input type="hidden" name="category" id="category" value="<?php echo $categorySEF?>" />
                <input type="hidden" name="id" id="id" value="<?php echo $_ID?>" />
                <input type="hidden" name="article" id="article" value="<?php echo $art_value?>" />
                <input type="hidden" name="commentspage" id="commentspage" value="<?php echo $back_to_page?>" />
                <input type="hidden" name="ip" id="ip" value="<?php echo $_SERVER['REMOTE_ADDR']; ?>" />
                <input type="hidden" name="time" id="time" value="<?php echo time(); ?>" />
                <input type="hidden" name="parent" id="parent" value="<?php echo $commentID?>" />
                <input type="submit" name="comment" id="comment" class="button" value="<?php echo l('submit'); ?>" />
            </p>
        </form>
    </div>
        <?php
    
} else {
        echo 
'<p>'.l('frozen_comments').'</p>';
    }
}

add_comment - Not sure why, but regular comments work - threaded comments do not.....
Code: [Select]
function add_comment() {
     if (isset($_POST['comment'])) {
        $comment = cleanWords(trim($_POST['text']));
        $comment = strlen($comment) > 4 ? clean(cleanXSS($comment)) : null;
        $name = trim($_POST['name']);
        # XSS Vulnerability Patch - June 14.10 (mdj) - 2 strings
        // strips any non-alphanumeric characters, while still allowing spaces, underscores and hyphens.
        $name = preg_replace('/[^a-zA-Z0-9_\s-]/', '', $name);
        if (empty($name)) { $name = 'Anonymous'; }
        $name = strlen($name) > 1 ? clean(cleanXSS($name)) : null;
        $url = trim($_POST['url']);
        # XSS Vulnerability Patch - June 14.10 (mdj) - 1 string
        $url = preg_replace('/[^a-zA-Z0-9_:\/\.-]/', '', $url); // strips characters not normally allowed in a URL
        $url = (strlen($url) > 8 && strpos($url, '?') === false) ? clean(cleanXSS($url)) : null;
        $post_article_id = (is_numeric($_POST['id']) && $_POST['id'] > 0) ? $_POST['id'] : null;
        $ip = (strlen($_POST['ip']) < 16) ? clean(cleanXSS($_POST['ip'])) : null;


$parent = is_numeric($_POST['parent']) ? $_POST['parent'] : "NULL";


        if (_ADMIN) {
            $doublecheck = 1;
            $ident=1;
        } else {
            $contentCheck = retrieve('id', 'comments', 'comment', $comment);
            $ident = !$contentCheck || (time() - $_SESSION[_SITE.'poster']['time']) > s('comment_repost_timer') ||
                $_SESSION[_SITE.'poster']['ip'] !== $ip ? 1 : 0;
            $doublecheck = $_SESSION[_SITE.'poster']['article'] === "$comment:|:$post_article_id" &&
                (time()-$_SESSION[_SITE.'poster']['time']) < s('comment_repost_timer') ? 0 : 1;
        }
        if ($ip == $_SERVER['REMOTE_ADDR'] && $comment && $name && $post_article_id  &&
             checkMathCaptcha() && $doublecheck == 1 && $ident == 1) {        
                $url = preg_match('/((http)+(s)?:(\/\/)|(www\.))([a-z0-9_\-]+)/', $url) ? $url : '';
                $url = substr($url, 0, 3) == 'www' ? 'http://'.$url : $url;
                $time = date('Y-m-d H:i:s');
                unset($_SESSION[_SITE.'poster']);
                $approved = s('approve_comments') != 'on'|| _ADMIN ? 'True' : '';

$query = 'INSERT INTO '._PRE.'comments'.'(parent,articleid, name, url, comment, time, approved) VALUES'.

"($parent, $post_article_id, '$name', '$url', '$comment', '$time', '$approved')";


                mysql_query($query);
                $_SESSION[_SITE.'poster']['article']="$comment:|:$post_article_id";
                $_SESSION[_SITE.'poster']['time'] = time();
                // this is to set session for checking multiple postings.
                $_SESSION[_SITE.'poster']['ip'] = $ip;
                $commentStatus = s('approve_comments') == 'on'&& !_ADMIN ? l('comment_sent_approve') : l('comment_sent');
                // eMAIL COMMENTS
                if (s('mail_on_comments') == 'on' && !_ADMIN) {
                    if (s('approve_comments') == 'on') {
                        $status = l('approved_text');
                        $subject =l('subject_a');
                    } else {
                        $status = l('not_waiting_approved');
                        $subject =l('subject_b');
                    }
                    $to = s('website_email');
                    $send_array = array(
                        'to'=>$to,
                        'name'=>$name,
                        'comment'=>$comment,
                        'ip'=>$ip,
                        'url'=>$url,
                        'subject'=>$subject,
                        'status'=>$status);
                    send_email($send_array);
                }
                // End of Mail
        } else {
            $commentStatus = l('comment_error');
            $commentReason = l('ce_reasons');
            $fail = true;
            $_SESSION[_SITE.'comment']['name'] = $name;
            $_SESSION[_SITE.'comment']['comment'] = br2nl($comment);
            $_SESSION[_SITE.'comment']['url'] = $url;
            $_SESSION[_SITE.'comment']['fail'] = $fail;
        }
        echo '<h2>'.$commentStatus.'</h2>';
        if (!empty($commentReason)) {
            echo '<p>'.$commentReason.'</p>';
        }
        $postArt = clean(cleanXSS($_POST['article']));
        $postArtID = retrieve('category','articles','id',$post_article_id);
        if ($postArtID == 0) {
            $postCat = '' ;
        } else {
            $postCat = cat_rel($postArtID, 'seftitle').'/';
        }
        if ($fail){
            $back_link = _SITE.$postCat.$postArt;
            echo '<a href="'.$back_link.'/">'.l('back').'</a>';
        } else {
            echo '<meta http-equiv="refresh" content="1; url='._SITE.$postCat.$postArt.'/">';
        }
   }
}

As a proof of concept, it works, but there are bugs and this is totally unfinished.... It is a rush job for now...


EDIT - the form issue is because of math captcha.  The function is not able to handle multiple forms, or the form is not created well enough (I was tired...), or the function needs to be modified (both) to handle this.  I would just remove the captcha .
« Last Edit: October 08, 2010, 06:10:45 AM by nukpana »
Logged

nukpana

  • Hero Member
  • *****
  • Karma: 71
  • Posts: 663
Re: [WIP] Threaded comments
« Reply #1 on: October 07, 2010, 12:39:47 PM »

Fixed some bugs and added some (bad) styling - still needs some things (ADMIN function for one)

STILL HAS BUGS!! - DO NOT USE IN A LIVE ENVIRONMENT (looking at Sven!)

** code removed
« Last Edit: October 08, 2010, 06:09:47 AM by nukpana »
Logged

Sven

  • ULTIMATE member
  • ******
  • Karma: 88
  • Posts: 2029
  • Chasing MY bugs!
    • hiseo.fr - rédacteur Web
Re: [WIP] Threaded comments
« Reply #2 on: October 07, 2010, 02:36:28 PM »

STILL HAS BUGS!! - DO NOT USE IN A LIVE ENVIRONMENT (looking at Sven!)
;D
I swear, I won’t!! ;)

Fred K

  • Still trying to learn stuff
  • ULTIMATE member
  • ******
  • Karma: 130
  • Posts: 2728
    • Personal
Re: [WIP] Threaded comments
« Reply #3 on: October 07, 2010, 07:44:49 PM »

Thanks Jason.
It'll be a while before I can run real tests but I'll give a whirl and see how much it bites...
(my good computer died yesterday and a number of ongoing projects with it so ... first things first.)
Logged

nukpana

  • Hero Member
  • *****
  • Karma: 71
  • Posts: 663
Re: [WIP] Threaded comments
« Reply #4 on: October 08, 2010, 01:46:20 AM »

Ok I think this is done... for now

3 level threaded comments:


http://www.mediafire.com/file/n3q67dcywdwnom6/snews17-cmnt_thrds.zip

Please note, MathCaptcha is NOT ENABLED in comments.


** MAJOR BUG **
Comment deletion functions must be disabled.  Right now there is no way to rebuild a tree if a comment is deleted.  - Only solution is to remove the deletion of comments. As of today still a WIP


Bug fix & addition:
October 9, 2010:
1. In function comment_list(), replace the query with this:
Code: [Select]
$query = '
SELECT
 c.id
, c.parent
, c.articleid
, c.name
, c.url
, c.comment
, c.time
, c.approved
, (
SELECT name
FROM '._PRE.'comments
WHERE id = c.parent
) AS replied_to
, CASE
WHEN (
SELECT lvl1.id
FROM '._PRE.'comments AS lvl1
WHERE isnull(lvl1.parent)
AND lvl1.approved = "True"
AND lvl1.articleid = '.$_ID.'
AND lvl1.id = c.id
) = c.id
THEN "x"
 END as Lvl_1
, CASE
WHEN (
SELECT lvl2.id
FROM '._PRE.'comments AS lvl1
LEFT OUTER JOIN '._PRE.'comments AS lvl2
ON lvl2.parent = lvl1.id
WHERE isnull(lvl1.parent)
AND lvl1.approved = "True"
AND lvl2.approved = "True"
AND lvl1.articleid = '.$_ID.'
AND lvl2.articleid = '.$_ID.'
AND lvl2.id = c.id
) = c.id
THEN "x"
 END as Lvl_2
, CASE
WHEN (
SELECT lvl3.id
FROM '._PRE.'comments AS lvl1
LEFT OUTER JOIN '._PRE.'comments AS lvl2
ON lvl2.parent = lvl1.id
LEFT OUTER JOIN '._PRE.'comments AS lvl3
ON lvl3.parent = lvl2.id
WHERE isnull(lvl1.parent)
AND lvl1.approved = "True"
AND lvl2.approved = "True"
AND lvl3.approved = "True"
AND lvl1.articleid = '.$_ID.'
AND lvl2.articleid = '.$_ID.'
AND lvl3.articleid = '.$_ID.'
AND lvl3.id = c.id
) = c.id
THEN "x"
 END as Lvl_3
, CASE
WHEN isnull(c.parent)
THEN CONCAT(c.id)

WHEN (
SELECT lvl2.parent
FROM '._PRE.'comments AS lvl1
LEFT OUTER JOIN '._PRE.'comments AS lvl2
ON lvl2.parent = lvl1.id
WHERE isnull(lvl1.parent)
AND lvl1.approved = "True"
AND lvl2.approved = "True"
AND lvl1.articleid = '.$_ID.'
AND lvl2.articleid = '.$_ID.'
AND lvl2.id = c.id
) = c.parent
THEN CONCAT(
 c.parent
, "."
, c.id
)

WHEN (
SELECT lvl3.parent
FROM '._PRE.'comments AS lvl1
LEFT OUTER JOIN '._PRE.'comments AS lvl2
ON lvl2.parent = lvl1.id
LEFT OUTER JOIN '._PRE.'comments AS lvl3
ON lvl3.parent = lvl2.id
WHERE isnull(lvl1.parent)
AND lvl1.approved = "True"
AND lvl2.approved = "True"
AND lvl3.approved = "True"
AND lvl1.articleid = '.$_ID.'
AND lvl2.articleid = '.$_ID.'
AND lvl3.articleid = '.$_ID.'
AND lvl3.id = c.id
) = c.parent
THEN CONCAT(
 (SELECT parent
FROM '._PRE.'comments
WHERE id = c.parent
 )
, "."
, c.parent
, "."
, c.id
)
END AS lineage
FROM '._PRE.'comments AS c
WHERE c.articleid = '.$_ID.'
AND c.approved = "True"
ORDER BY lineage, COALESCE( c.parent, c.id ), c.id '.s('comments_order').'
LIMIT '.$offset.', '.s('comment_limit');

2. In function show_comments(), find the first 3 lines and add the balance:
Code: [Select]
<?php

echo !empty($comment['url'])
'<a href="'.$comment['url'].'">'.$comment['name'].'</a>'
$comment['name'];
// UPDATED October 9th
echo !$comment['Lvl_1']
' <em>in reply to '.$comment['replied_to'].'</em>'
'';

?>

« Last Edit: October 22, 2010, 03:14:11 PM by nukpana »
Logged

jesth

  • Jr. Member
  • **
  • Karma: 1
  • Posts: 66
Re: [WIP] Threaded comments
« Reply #5 on: October 08, 2010, 03:36:54 AM »

I just tested it, and I get this:
Quote
There was an error while connecting to the database.
Check your database settings.
Logged

nukpana

  • Hero Member
  • *****
  • Karma: 71
  • Posts: 663
Re: [WIP] Threaded comments
« Reply #6 on: October 08, 2010, 06:09:18 AM »

I just tested it, and I get this:
Quote
There was an error while connecting to the database.
Check your database settings.

Just uploaded a test package for testing.. Let me know if it is ok
Logged

Rui Mendes

  • Development,Testing, Support
  • Hero Member
  • *****
  • Karma: 195
  • Posts: 1009
  • sNews1.7
    • Comunidade Portuguesa
Re: [WIP] Threaded comments
« Reply #7 on: October 08, 2010, 01:26:29 PM »

Big J
How are you Jason? I hope 100%
Good work you did. This is a good addon, I will try on this weekend.
You deserve a big Karma.
Logged
Need a Job on Europe. Linkdin - Facebook / Group

Fred K

  • Still trying to learn stuff
  • ULTIMATE member
  • ******
  • Karma: 130
  • Posts: 2728
    • Personal
Re: [WIP] Threaded comments
« Reply #8 on: October 08, 2010, 06:37:08 PM »

Jason - (using the test package) it works perfectly. Thanks a bunch! Karma deserved and given. ;)
Logged

jesth

  • Jr. Member
  • **
  • Karma: 1
  • Posts: 66
Re: [WIP] Threaded comments
« Reply #9 on: October 08, 2010, 07:13:53 PM »

Tested the package, and it's working nicely, good job.
Logged

nukpana

  • Hero Member
  • *****
  • Karma: 71
  • Posts: 663
Re: [WIP] Threaded comments
« Reply #10 on: October 08, 2010, 07:51:43 PM »

Thanks everyone.  Let me know of any bugs so I can get to them.  This "Mod" is a splitting of the current comments, so i shouldn't expect to see many bugs.  I would like to port this to 1.6 since it was pretty easy to do for 1.7.

I am concerned about the MathCaptcha -it's removal and how that effects spammed comments.  Again, I don't think it is an issue, but you tell me.
Logged

Fred K

  • Still trying to learn stuff
  • ULTIMATE member
  • ******
  • Karma: 130
  • Posts: 2728
    • Personal
Re: [WIP] Threaded comments
« Reply #11 on: October 09, 2010, 04:51:37 AM »

Since I generally use the old "honeypot" safety layer on comments I've implemented that -although I've only tried it in a local setting yet and it was a quickThink™. No telling until I run it in a live environment if it'll work but it's a tried and tested method so I expect it to do well against the average spambot. My way of adding it might need some improvement though since I don't really know what I'm doing, but it works. There are of course other captchas that could be implemented - reCaptcha comes to mind. Reasonably it shouldn't be too hard to do.

Here's what I did, if it's useful for others:
in mod.comment-threads.php, function add_comment(), I added the blue part to the quoted code, and in the comment form, simply add a field with the corresponding id that we hide with CSS.
Quote

      if ($fail){
         $back_link = _SITE.$postCat.$postArt;
         echo '<a href="'.$back_link.'/">'.l('back').'</a>';
      }
      if(!empty($_POST['your-id-here'])) {echo '<p id="warning">Bots are not allowed to comment!</p>
      <meta http-equiv="refresh"  content="2; url='._SITE.'" />';}


I tested this honeypot solution locally and it did what it was supposed to, so...
Of course it won't stop human spambots but that's a different story.

As for the mod itself, the only thing I noticed in testing was that if you get more than one reply to the original post, all second level posts end up before any third level posts, which makes perfect sense, but looks a little odd chronologically. Say visitor X replies to the original comment, then visitor Y replies to X's reply, then visitor Z responds to the original comment, the chronology on the site becomes:
Quote
original comment
-- X's reply to original
-- Z's reply to original
--- Y's reply to X's reply

Instead of the perhaps more logical:
Quote
Original comment
-- X's reply to original
--- Y's reply to X
-- Z's reply to original
etc

That's how the threaded comments at http://html5boilerplate.com works (as an example). I don't have any idea how complicated it might be to fix such an order, so I'm not asking for it - just, maybe, think about it ... ? ;)
« Last Edit: October 09, 2010, 05:03:47 AM by Fred K (agentsmith) »
Logged

nukpana

  • Hero Member
  • *****
  • Karma: 71
  • Posts: 663
Re: [WIP] Threaded comments
« Reply #12 on: October 09, 2010, 07:33:25 AM »

Complicated... and it is a bug actually...

So, change the query in function comment_list() to this:
Code: [Select]
$query = '
SELECT
 c.id
, c.parent
, c.articleid
, c.name
, c.url
, c.comment
, c.time
, c.approved
, CASE
WHEN (
SELECT lvl1.id
FROM '._PRE.'comments AS lvl1
WHERE isnull(lvl1.parent)
AND lvl1.approved = "True"
AND lvl1.articleid = '.$_ID.'
AND lvl1.id = c.id
) = c.id
THEN "x"
 END as Lvl_1
, CASE
WHEN (
SELECT lvl2.id
FROM '._PRE.'comments AS lvl1
LEFT OUTER JOIN '._PRE.'comments AS lvl2
ON lvl2.parent = lvl1.id
WHERE isnull(lvl1.parent)
AND lvl1.approved = "True"
AND lvl2.approved = "True"
AND lvl1.articleid = '.$_ID.'
AND lvl2.articleid = '.$_ID.'
AND lvl2.id = c.id
) = c.id
THEN "x"
 END as Lvl_2
, CASE
WHEN (
SELECT lvl3.id
FROM '._PRE.'comments AS lvl1
LEFT OUTER JOIN '._PRE.'comments AS lvl2
ON lvl2.parent = lvl1.id
LEFT OUTER JOIN '._PRE.'comments AS lvl3
ON lvl3.parent = lvl2.id
WHERE isnull(lvl1.parent)
AND lvl1.approved = "True"
AND lvl2.approved = "True"
AND lvl3.approved = "True"
AND lvl1.articleid = '.$_ID.'
AND lvl2.articleid = '.$_ID.'
AND lvl3.articleid = '.$_ID.'
AND lvl3.id = c.id
) = c.id
THEN "x"
 END as Lvl_3
, CASE
WHEN isnull(c.parent)
THEN CONCAT(c.id)

WHEN (
SELECT lvl2.parent
FROM '._PRE.'comments AS lvl1
LEFT OUTER JOIN '._PRE.'comments AS lvl2
ON lvl2.parent = lvl1.id
WHERE isnull(lvl1.parent)
AND lvl1.approved = "True"
AND lvl2.approved = "True"
AND lvl1.articleid = '.$_ID.'
AND lvl2.articleid = '.$_ID.'
AND lvl2.id = c.id
) = c.parent
THEN CONCAT(
 c.parent
, "."
, c.id
)

WHEN (
SELECT lvl3.parent
FROM '._PRE.'comments AS lvl1
LEFT OUTER JOIN '._PRE.'comments AS lvl2
ON lvl2.parent = lvl1.id
LEFT OUTER JOIN '._PRE.'comments AS lvl3
ON lvl3.parent = lvl2.id
WHERE isnull(lvl1.parent)
AND lvl1.approved = "True"
AND lvl2.approved = "True"
AND lvl3.approved = "True"
AND lvl1.articleid = '.$_ID.'
AND lvl2.articleid = '.$_ID.'
AND lvl3.articleid = '.$_ID.'
AND lvl3.id = c.id
) = c.parent
THEN CONCAT(
 (SELECT parent
FROM '._PRE.'comments
WHERE id = c.parent
 )
, "."
, c.parent
, "."
, c.id
)
END AS lineage
FROM '._PRE.'comments AS c
WHERE c.articleid = '.$_ID.'
AND c.approved = "True"
ORDER BY lineage, COALESCE( c.parent, c.id ), c.id '.s('comments_order').'
LIMIT '.$offset.', '.s('comment_limit');


And if you want the reply to, put this in show_comments
Code: [Select]
echo !empty($comment['url'])
? '<a href="'.$comment['url'].'">'.$comment['name'].'</a>'
: $comment['name'];

// Reply to code
if (!$comment['Lvl_1']) {
echo ' Replied to '.$comment['replied_to'];
}

Add this to the above query (before the first case statement for example)
Code: [Select]
, (
SELECT name
FROM '._PRE.'comments
WHERE id = c.parent
) AS replied_to

I am sick so I am not thinking 100% - sorry if the code is shoddy


So wtf is going on here?  

Well seeing the LINEAGE column, I am taking the id's and parents and putting them in a "Materialized Path" (ex: here, here so we can sort them later.  for example:
Quote
Original comment - ID 1 Parent NULL
-- X's reply to original - ID 2 Parent 1
--- Y's reply to X - - ID 4 Parent 2
-- Z's reply to original - ID 3 Parent 1

We can clearly see the relationship given to each (thanks to Fred!)
The original is the first level - ID 1
The second comment ID 2 and is a REPLY TO ID 1 so: 1.2
The third comment ID 3 and is a REPLY TO ID 1 so: 1.3
The fourth comment ID 4 and is a REPLY TO ID 2 so: 1.2.4

Sort it...
1
1.2
1.2.4
1.3
« Last Edit: October 09, 2010, 03:00:26 PM by nukpana »
Logged

Fred K

  • Still trying to learn stuff
  • ULTIMATE member
  • ******
  • Karma: 130
  • Posts: 2728
    • Personal
Re: [WIP] Threaded comments
« Reply #13 on: October 09, 2010, 02:16:57 PM »

Jason, first of all you shouldn't do things like this when you're ill - focus on getting better instead.
Second of all, your work is first rate even when you're ill. The result is perfect, shoddy code or not. ;)
Third, I vote for convincing Luka this mod should go in the next version of the core.
Thanks and karma given. Get well!
Logged

nukpana

  • Hero Member
  • *****
  • Karma: 71
  • Posts: 663
Re: [WIP] Threaded comments
« Reply #14 on: October 09, 2010, 05:36:23 PM »

Thanks dude.  I am getting rest now...
Logged
Pages: [1] 2 3