Thread

OK, so I made some progress today, but I’m stuck on this:

I’m trying to list all the comments made by the logged in user. I have achieved that—I get the full output of comments when I don’t try joining another table.

But in listing the comments, I want to provide the name of the channel entry that was commented on, as well as a link to that channel entry. So I *think* I need to join the exp_comments table with the exp_weblog_titles table, but correct me if I’m wrong there. I have little SQL experience, so this has been fun…to say the least. Actually, I’m enjoying it, but it’s getting beyond my ability to blindly work through it. Here’s what I have:

{exp:query sql="SELECT entry_id, name, author_id, comment_date, comment FROM exp_comments AS c 
        JOIN exp_weblog_titles AS t 
        ON c.entry_id = t.entry_id
        WHERE author_id = '<?php echo $logged_in_member_id; ?>'" limit="5" paginate="bottom"}    

    <h4>On {comment_date}, you commented on <a href="http://{url_title}">{title}</a>:</h4>
<p> <br />
        {comment}<br />
 <br />
</div></p>

<p>{/exp:query}

Am I on the right track? Currently I’m getting the following error message:

Error Number: 1146

Table ‘cbis_expeng.exp_weblog_titles’ doesn’t exist

Also the limit/pagination seems to be ignored, so I need to figure that out too. Help?

Short with time but here is a copy/paste of my snip that might be of use, just sub the segment_3 with your logic of logged in user:

{exp:query sql="
SELECT exp_comments.author_id, exp_weblog_titles.title, exp_weblog_titles.url_title, 
exp_comments.comment, exp_comments.comment_date, exp_weblog_titles.comment_total, 
exp_weblog_titles.recent_comment_date as comment_recent 
FROM exp_comments 
INNER JOIN exp_weblog_titles 
ON (exp_comments.entry_id = exp_weblog_titles.entry_id) WHERE exp_comments.author_id ='{segment_3}'
"}
  <a href="http://{path=/template_group/template/{url_title}}#allcomments">{title}</a>

  {comment}

  {comment_date format='%m/%d/%y'}

  {comment_total}

  {comment_recent format='%m/%d/%y'}

{/exp:query}

Thank you so much! That got me going in the right direction! Now to get pagination working!

Place your parameters before the sql statement(yours is correct just placed after) and then add the pagination before query tag is closed:

{exp:query limit="5" paginate="bottom" sql=" ...the above…"}

...show comments, links etc….

  {paginate}
   Page {current_page} of {total_pages} pages {pagination_links}
  {/paginate}

{/exp:query}