The MU forums have moved to WordPress.org

Recent comments from all sites SQL (45 posts)

  1. leisegang
    Member
    Posted 13 years ago #

    I have this SQL and it is working. but it only picks out the comments of the main blog. can anyone point me in the right direction?

    <?php
    global $wpdb, $wpmuBaseTablePrefix;
    $blogs = $wpdb->get_col("SELECT <code>blog_id</code> FROM $wpdb->blogs
    	WHERE <code>public</code> = '1' AND <code>archived</code> = '0' AND <code>last_updated</code> != '0000-00-00 00:00:00'
    	ORDER BY <code>last_updated</code> DESC LIMIT ");
    if (!is_array($blogs)) return false; // New Site?
    foreach($blogs as $blogid)
    
    global $wpdb, $wpmuBaseTablePrefix;
    $sql = "SELECT DISTINCT ID, post_title, post_password, comment_ID,
    comment_post_ID, comment_author, comment_date_gmt, comment_approved,
    comment_type,comment_author_url,
    SUBSTRING(comment_content,1,300) AS com_excerpt
    FROM $wpdb->comments
    LEFT OUTER JOIN $wpdb->posts ON ($wpdb->comments.comment_post_ID =
    $wpdb->posts.ID)
    WHERE comment_approved = '1' AND comment_type = '' AND
    post_password = ''
    ORDER BY comment_date_gmt DESC
    LIMIT 10";
    $comments = $wpdb->get_results($sql);
    $output = $pre_HTML;
    $output .= "\n<ul>";
    foreach ($comments as $comment) {
    $output .= "\n<li> <a href=\"" . get_permalink($comment->ID) ."#comment-" . $comment->comment_ID . "\" \">" .strip_tags($comment->comment_author)." på " .$comment->post_title . "</a><br />" . strip_tags($comment->com_excerpt). "</li>";
    }
    
    $output .= "\n</ul>";
    $output .= $post_HTML;
    echo $output;?>
  2. Klark0
    Member
    Posted 13 years ago #

    What i think is accepted is that we create global tables and then grab the data from it ..rather than running through each and every blog on the system. Same thinking applies to other stuff like recent posts or global tags.

    Here:

    http://wpmudevorg.wordpress.com/project/Sitewide-recent-comments

  3. leisegang
    Member
    Posted 13 years ago #

    i know of that plugin but it just outputs: "by:john"
    and i use it now. but the feed is a bit unstable.

    i want it to be: "john @ post title"

    and i need this for several other things to
    i just dont know how to select from all tables. named ie _comments

    its ok for it to create a new table but why not just go thru it?

  4. dsader
    Member
    Posted 13 years ago #

    http://wpmudevorg.wordpress.com/project/Sitewide-recent-comments indexes the blog_id and comment_post_id for each comment so you'll need something like the following to get post details(title). Then "john on post_title" output begins to take shape:

    $blogPostsTable = $wpdb->base_prefix.$comment['blog_id']."_posts";
    $id = $comment['comment_post_ID'];
    $thispost = $wpdb->get_results("SELECT *
    	FROM $blogPostsTable
    	WHERE ID = '$id'
    	AND post_type = 'post'
    	AND post_status = 'publish'
    	");
    $comment_author_details = get_userdata($comment['comment_author_user_id']);
    $content_characters = 100;
    
    echo $comment_author_details->display_name . ' on <a href="' . $comment['comment_post_permalink'] . '#comment-'.$comment['comment_id'].'">' . $thispost[0]->post_title . '</a>: '.substr($comment['comment_content_stripped'],0,$content_characters);
  5. leisegang
    Member
    Posted 13 years ago #

    where do add the code you wrote there?

  6. dsader
    Member
    Posted 13 years ago #

    Do you have an output function for http://wpmudevorg.wordpress.com/project/Sitewide-recent-comments ? Massage it into that, or directly into your template.

  7. leisegang
    Member
    Posted 13 years ago #

    i have the sample code:

    <?php
    global $wpdb, $blog_id;
    $site_comments = 'sitewide_comments';
    $blog_table = 'wp_blogs';
    
    if ($blog_id > 0 && $blog_id < 3){  // Only for blogs 1 and 2
      $sql = "SELECT blog_id, comment_ID, comment_post_ID, comment_author,
         comment_date, comment_date_gmt, comment_approved, comment_type, comment_author_url,
         SUBSTRING(comment_content,1,250) AS com_excerpt FROM $site_comments
         WHERE comment_approved = '1' AND comment_type = ''
         ORDER BY comment_date_gmt DESC
         LIMIT 20";
    
      // getting those sitewide comments
      $comments = $wpdb->get_results($sql);
    
      // creating a lookup table of blogs
      $all_blogs = $wpdb->get_results("SELECT blog_id, path FROM $blog_table WHERE deleted='0' ORDER BY blog_id");
    
      $output = $pre_HTML;
      $output .= "\n<ul>\n";
    
      for($i = 0; $i < count($all_blogs); $i++){
        $ach_blog_id = $all_blogs[$i]->blog_id;
        $ach_blog_path = $all_blogs[$i]->path;
    
        if ($ach_blog_path == "/"){
          $ach_blog_path = "MainBlog";
        }else{
          $ach_blog_path = trim($ach_blog_path, "/");
          $ach_blog_path .= "'s page ";
        }
        $ach_blog_lu[$ach_blog_id] = $ach_blog_path;
      }
    
      foreach ($comments as $comment) {
        $output .= "\n<li><b>".strip_tags($comment->comment_author)  .
          "</b> | <a href=\"" . get_blog_permalink($comment->blog_id, $comment->comment_post_ID) .
          "#comment-" . $comment->comment_ID . "\">" .
          get_comment_date('m/d/Y') .
          " " .
          get_comment_time();
    
        if ($comment->blog_id != $blog_id)
          $output .= " | " . $ach_blog_lu[$comment->blog_id];
    
        $output .= "</a>" .
          strip_tags($comment->com_excerpt) .
          "&nbsp;&nbsp;&nbsp;<a  href=\"" .
          get_blog_permalink($comment->blog_id, $comment->comment_post_ID) .
          "#comment-" . $comment->comment_ID . "\">" .
          "more</a>" .
          "</li>";
      }
    
      $output .= "\n</ul>";
      $output .= $post_HTML;
    
      echo $output;
    }else{
      // Do normal recent comment instead..
    }
  8. dsader
    Member
    Posted 13 years ago #

    The sample I've suggested looks like it'll massage/replace the output code in your foreach loop

    foreach ($comments as $comment) {
    $blogPostsTable = $wpdb->base_prefix.$comment['blog_id']."_posts";
    $id = $comment['comment_post_ID'];
    $thispost = $wpdb->get_results("SELECT *
    	FROM $blogPostsTable
    	WHERE ID = '$id'
    	AND post_type = 'post'
    	AND post_status = 'publish'
    	");
    $comment_author_details = get_userdata($comment['comment_author_user_id']);
    $content_characters = 100;
    $output .= $comment_author_details->display_name . ' on <a href="' . $comment['comment_post_permalink'] . '#comment-'.$comment['comment_id'].'">' . $thispost[0]->post_title . '</a>: '.substr($comment['comment_content_stripped'],0,$content_characters);
    }

    PS, your sample $sql already substr the comment_content characters to 250, where I've truncated the comment_content_stripped characters length to 100 later. You may have to change comment_content_stripped to comment_content if it isn't indexed to begin with.

  9. leisegang
    Member
    Posted 13 years ago #

    I now have this code but it is giving me an error
    line 38:
    Fatal error: Cannot use object of type stdClass as array in on line 38
    this is line 38:

    $blogPostsTable = $wpdb->base_prefix.$comment['blog_id']."_posts";
    <h2>Recent Comments</h2>
    <?php
    global $wpdb, $blog_id;
    $site_comments = 'sitewide_comments';
    $blog_table = 'wp_blogs';
    
    if ($blog_id > 0 && $blog_id < 3){  // Only for blogs 1 and 2
      $sql = "SELECT blog_id, comment_ID, comment_post_ID, comment_author,
         comment_date, comment_date_gmt, comment_approved, comment_type, comment_author_url,
         SUBSTRING(comment_content,1,250) AS com_excerpt FROM $site_comments
         WHERE comment_approved = '1' AND comment_type = ''
         ORDER BY comment_date_gmt DESC
         LIMIT 20";
    
      // getting those sitewide comments
      $comments = $wpdb->get_results($sql);
    
      // creating a lookup table of blogs
      $all_blogs = $wpdb->get_results("SELECT blog_id, path FROM $blog_table WHERE deleted='0' ORDER BY blog_id");
    
      $output = $pre_HTML;
      $output .= "\n<ul>\n";
    
      for($i = 0; $i < count($all_blogs); $i++){
        $ach_blog_id = $all_blogs[$i]->blog_id;
        $ach_blog_path = $all_blogs[$i]->path;
    
        if ($ach_blog_path == "/"){
          $ach_blog_path = "MainBlog";
        }else{
          $ach_blog_path = trim($ach_blog_path, "/");
          $ach_blog_path .= "'s page ";
        }
        $ach_blog_lu[$ach_blog_id] = $ach_blog_path;
      }
    
    foreach ($comments as $comment) {
    $blogPostsTable = $wpdb->base_prefix.$comment['blog_id']."_posts";
    $id = $comment['comment_post_ID'];
    $thispost = $wpdb->get_results("SELECT *
    	FROM $blogPostsTable
    	WHERE ID = '$id'
    	AND post_type = 'post'
    	AND post_status = 'publish'
    	");
    $comment_author_details = get_userdata($comment['comment_author_user_id']);
    $content_characters = 100;
    $output .= $comment_author_details->display_name . ' on <a href="' . $comment['comment_post_permalink'] . '#comment-'.$comment['comment_id'].'">' . $thispost[0]->post_title . '</a>: '.substr($comment['comment_content_stripped'],0,$content_characters);
    }
    
      $output .= "\n</ul>";
      $output .= $post_HTML;
    
      echo $output;
    }else{
      // Do normal recent comment instead..
    }
    
    ?>
  10. dsader
    Member
    Posted 13 years ago #

    The $comments is empty

    Try

    $sql = "SELECT * FROM " . $wpdb->base_prefix.$site_comments."
         WHERE comment_approved = '1' AND comment_type = ''
         ORDER BY comment_date_gmt DESC
         LIMIT 20";
      // getting those sitewide comments
      $comments = $wpdb->get_results($sql, ARRAY_A);

    Add a print_r($comments); to make sure the correct data is puked out by the query.

  11. cafespain
    Member
    Posted 13 years ago #

    Line 38 should be:

    $blogPostsTable = $wpdb->base_prefix . $comment->blog_id ."_posts";

    The clue is in the error message. :)

    Fatal error: Cannot use object of type stdClass as array in on line 38
    this is line 38:

    The default for get_results is to return the records as objects not arrays. Therefore you access the fields with -> instead of ['']. If you want to access the fields as arrays, then you need to specify this in the get_results call:

    $comments = $wpdb->get_results($sql, ARRAY_A);
  12. leisegang
    Member
    Posted 13 years ago #

    ? where to put the print_r($comments); ?

  13. dsader
    Member
    Posted 13 years ago #

    To debug whether a variable echos out the intended data, right after the variable $comments is defined. Then remove it if the data is o.k.

  14. leisegang
    Member
    Posted 13 years ago #

    i really dont get much of this, i am not that good with coding and sql queries.

    anyone want to fi it for me?

  15. cafespain
    Member
    Posted 13 years ago #

    already have, read my message a little bit further up.

  16. leisegang
    Member
    Posted 13 years ago #

    This is my file. i am getting a error:

    Parse error: syntax error, unexpected T_VARIABLE in comments.php on line 47

    <?php get_header(); ?>
    
    	<div id="content">
    
    	<h2>Recent Comments</h2>
    <?php
    global $wpdb, $blog_id;
    $site_comments = 'sitewide_comments';
    $blog_table = 'wp_blogs';
    
    if ($blog_id > 0 && $blog_id < 3){  // Only for blogs 1 and 2
    $sql = "SELECT * FROM " . $wpdb->base_prefix.$site_comments."
         WHERE comment_approved = '1' AND comment_type = ''
         ORDER BY comment_date_gmt DESC
         LIMIT 20";
      // getting those sitewide comments
      $comments = $wpdb->get_results($sql, ARRAY_A);
      print_r($comments);
    
      // getting those sitewide comments
      $comments = $wpdb->get_results($sql);
      print_r($comments);
    
      // creating a lookup table of blogs
      $all_blogs = $wpdb->get_results("SELECT blog_id, path FROM $blog_table WHERE deleted='0' ORDER BY blog_id");
    
      $output = $pre_HTML;
      $output .= "\n<ul>\n";
    
      for($i = 0; $i < count($all_blogs); $i++){
        $ach_blog_id = $all_blogs[$i]->blog_id;
        $ach_blog_path = $all_blogs[$i]->path;
    
        if ($ach_blog_path == "/"){
          $ach_blog_path = "MainBlog";
        }else{
          $ach_blog_path = trim($ach_blog_path, "/");
          $ach_blog_path .= "'s page ";
        }
        $ach_blog_lu[$ach_blog_id] = $ach_blog_path;
      }
    
    foreach ($comments as $comment) {
    $blogPostsTable = $wpdb->base_prefix . $comment->blog_id ."_posts";
    $id = $comment['comment_post_ID'];
    $comments = $wpdb->get_results($sql, ARRAY_A);
    	FROM $blogPostsTable
    	WHERE ID = '$id'
    	AND post_type = 'post'
    	AND post_status = 'publish'
    	");
    $comment_author_details = get_userdata($comment['comment_author_user_id']);
    $content_characters = 100;
    $output .= $comment_author_details->display_name . ' on <a href="' . $comment['comment_post_permalink'] . '#comment-'.$comment['comment_id'].'">' . $thispost[0]->post_title . '</a>: '.substr($comment['comment_content_stripped'],0,$content_characters);
    }
    
      $output .= "\n</ul>";
      $output .= $post_HTML;
    
      echo $output;
    }else{
      // Do normal recent comment instead..
    }
    
    ?>
    	</div>
    <?php get_sidebar(); ?>
    
    <?php get_footer(); ?>
  17. dsader
    Member
    Posted 13 years ago #

    You are so close leisegang.

    http://pastebin.com/m3938c745

  18. leisegang
    Member
    Posted 13 years ago #

    http://pastebin.com/m6ed913af

    updated it and now i get this error:Warning: Invalid argument supplied for foreach() in comments.php on line 39

    at least i can se a page :D

  19. dsader
    Member
    Posted 13 years ago #

    Do you have comments in the db?

    $site_comments = 'sitewide_comments';

  20. leisegang
    Member
    Posted 13 years ago #

    yes i have added 2 comments to the DB. where to put the $site_comments = 'sitewide_comments';

    i have it in line 8.

  21. leisegang
    Member
    Posted 13 years ago #

    still not working ...

  22. dsader
    Member
    Posted 13 years ago #

    leisegang, I've tested the last sample code you posted to pastebin and it works for me. The problem may be whether or not the rest of the plugin is actually populating a table in your database called 'sitewide_coments'.

    How are you certain that the db table 'sitewide_comments' is indexing comments from across the site?

  23. andrewbillits
    Member
    Posted 13 years ago #

    Hi Guys,

    If you're using our Comment Indexer plugin then the table is "PREFIX_site_comments". Ignore me if the code mentioned above isn't using our plugin.

    Thanks,
    Andrew

  24. leisegang
    Member
    Posted 13 years ago #

    now i got an output. :) yeah!

    bit it only outputs this:

    Recent Comments
    * on :
    * on :

    so there is a little error in the output then.. i guess.

  25. dsader
    Member
    Posted 13 years ago #

    If nothing is coming out of a table in the database, how do you know anything is being put in there?

    Use phpmyadmin to have a look at the db table after adding a few test comments, are new comments being added?

    What did you have to "fix" to get the output you have now?

  26. leisegang
    Member
    Posted 13 years ago #

    the DB table was called "sitewide_comments" not "wp_sidewide_comments" an now i changed that in the file. and it is now adding test comments to the "wp_sidewide_comments" table.

    output is still the same:
    Recent Comments
    * on :
    * on :

  27. leisegang
    Member
    Posted 13 years ago #

    dsader do you have msn?

  28. dsader
    Member
    Posted 13 years ago #

    No msn.

    I golf.

    It's time to pick up the ball and move on to another hole.

  29. leisegang
    Member
    Posted 13 years ago #

    now i get an output. the only thing i am missing now is the URL to the post.

    this is the code i have:

    <?php
    /*
    Template Name: Tags
    */
    ?>
    <?php get_header(); ?>
    
    	<div id="content">
    
    	<h2>Recent Comments</h2>
    <?php
    global $wpdb, $blog_id;
    $site_comments = 'sitewide_comments';
    $blog_table = 'wp_blogs';
    
    if ($blog_id > 0 && $blog_id < 3){  // Only for blogs 1 and 2
    $sql = "SELECT * FROM " . $wpdb->base_prefix.$site_comments."
         WHERE comment_approved = '1' AND comment_type = ''
         ORDER BY comment_date_gmt DESC
         LIMIT 20";
      // getting those sitewide comments
      $comments = $wpdb->get_results($sql, ARRAY_A);
      print_r($comments);
    
      // creating a lookup table of blogs
      $all_blogs = $wpdb->get_results("SELECT blog_id, path, domain FROM $blog_table WHERE deleted='0' ORDER BY blog_id");
      $output = $pre_HTML;
      $output .= "\n<ul>\n";
    
      for($i = 0; $i < count($all_blogs); $i++){
        $ach_blog_id = $all_blogs[$i]->blog_id;
        $ach_blog_path = $all_blogs[$i]->path;
    
        if ($ach_blog_path == "/"){
          $ach_blog_path = "MainBlog";
        }else{
          $ach_blog_path = trim($ach_blog_path, "/");
          $ach_blog_path .= "'s page ";
        }
        $ach_blog_lu[$ach_blog_id] = $ach_blog_path;
      }
    
    foreach ($comments as $comment) {
    $blogPostsTable = $wpdb->base_prefix.$comment['blog_id']."_posts";
    $id = $comment['comment_post_ID'];
    $thispost = $wpdb->get_results("SELECT *
    	FROM $blogPostsTable
    	WHERE ID = '$id'
    	AND post_type = 'post'
    	AND post_status = 'publish'
    	");
    
      print_r($thispost);
    // How many characters it will output
    $content_characters = 70;
    // Spacer is the word between author and post title.
    $spacer = ' på ';
    $output .= '<li><a href="' . $comment['comment_post_permalink'] . $thispost ['post_name']. '#comment-'.$comment['comment_ID'].'">'.$comment['comment_author']  .$spacer.  $thispost[0]->post_title . '</a>: <br />'.substr($comment['comment_content'],0,$content_characters);'</li>';
    
    }
    
      $output .= "\n</ul>";
      $output .= $post_HTML;
    
      echo $output;
    }else{
      // Do normal recent comment instead..
    }
    
    ?>
    	</div>
    <?php get_sidebar(); ?>
    
    <?php get_footer(); ?>
  30. dsader
    Member
    Posted 13 years ago #

    Could it be capitalization errors?

    example: comment_ID or comment_id
    comment_post_ID or comment_post_id ?

    How are these collumns labeled in your db tables by the plugin code?
    Should be consistent I figure.

About this Topic