Monthly Archives: December 2009

Excluding Page IDs from a Custom Query

6 Replies

Available on the DailyBlogTips website is a theme called StudioPress_Red. The theme uses a custom query to list the pages in the site navigation while applying a ‘current page’ class for styling the nav bar. The navigation code sets up a nav scheme with the traditional ‘Home’ button followed by the listing of pages. This works out just fine for the typical blogger who has his blog as his home page. It breaks down a bit, however, for the blogger who wants to set one of her static pages as the Home page, since she will then have both the ‘Home’ button as well as the selected page’s button showing in the nav bar. Typically, with the wp_list_pages function, you can simply add an exclude parameter to hide any pages you don’t want to show. With the StudioPress_Red theme, however, it not quite that straightforward.

Below is the block of navigation code from the header.php file. Line 47 (highlighted) is the line of interest that needs to be modified in order to exclude the appropriate pages from the nav bar:

<div id="nav">
<?php function get_the_pa_ges() {
  global $wpdb;
  if ( ! $these_pages = wp_cache_get('these_pages', 'pages') ) {
     $these_pages = $wpdb->get_results('select ID, post_title from '. $wpdb->posts .' where post_status = "publish" and post_type = "page" order by ID');
}
  return $these_pages;
 }

 function list_all_pages(){

$all_pages = get_the_pa_ges ();
foreach ($all_pages as $thats_all){
$the_page_id = $thats_all->ID;

if (is_page($the_page_id)) {
  $addclass = ' class="current_page"';
  } else {
  $addclass = '';
  }
$output .= '<li' . $addclass . '><a href="'.get_permalink($thats_all->ID).'" title="'.$thats_all->post_title.'"><span>'.$thats_all->post_title.'</span></a></li>';
}

return $output;
 }
?>
<ul>
<?php

if (is_home()) {
  $addclass = ' class="current_page"';
  } else {
  $addclass = '';
  }
echo "<li" . $addclass . "><a href='" . get_option('home') . "' title='Home'><span>Home</span></a></li>";
echo list_all_pages();?>
</ul>

<div class="cleared"></div>
</div> <!-- Closes Nav -->

What line 47 is doing is talking to the WordPress database and retrieving all published pages by their ID numbers and returning the titles of those pages to the navigation list, sorted by the pages’ IDs. In order to exclude certain pages from the list, a new parameter
needs to be added to line 47 to tell function to skip a page that’s set as the Home page. Unfortunately, even the WordPress Codex doesn’t provide any real documentation for page exclusions in a custom query, so you have to know your WordPress core and MySQL syntax to make it work.

Prior to WordPress 2.7, post ID numbers were saved to the database under the post_id field. From WordPress 2.7 and on, post_id was changed to just ID. So, in order to exclude the Home page ID from our custom query, we have to add and ID !="##" to line 47, where ## is the ID number of the page you want:

<div id="nav">
<?php function get_the_pa_ges() {
  global $wpdb;
  if ( ! $these_pages = wp_cache_get('these_pages', 'pages') ) {
     $these_pages = $wpdb->get_results('select ID, post_title from '. $wpdb->posts .' where post_status = "publish" and post_type = "page" and ID != "##" order by ID');
   }
  return $these_pages;
 }

 function list_all_pages(){

$all_pages = get_the_pa_ges ();
foreach ($all_pages as $thats_all){
$the_page_id = $thats_all->ID;

if (is_page($the_page_id)) {
  $addclass = ' class="current_page"';
  } else {
  $addclass = '';
  }
$output .= '<li' . $addclass . '><a href="'.get_permalink($thats_all->ID).'" title="'.$thats_all->post_title.'"><span>'.$thats_all->post_title.'</span></a></li>';
}

return $output;
 }
?>
<ul>
<?php

if (is_home()) {
  $addclass = ' class="current_page"';
  } else {
  $addclass = '';
  }
echo "<li" . $addclass . "><a href='" . get_option('home') . "' title='Home'><span>Home</span></a></li>";
echo list_all_pages();?>
</ul>

<div class="cleared"></div>
</div> <!-- Closes Nav -->

This tells the query to find all published pages that don’t have an ID of ## and return those page titles for display in the nav bar. Since I don’t work with the way WordPress communicates with the database very often, it took me a couple of hours of (nearly futile) research and experimentation to figure out what would work here. Hopefully you’ll find this information useful.

Bulk Clean-up of ‘Approved’ Spam Comments

Leave a reply

I recently had to clean up the database for a WordPress installation where several thousand spam comments had managed to slip by the filters and get themselves approved. Naturally, going through and marking each individual one as spam would have been a mind-numbing and infuriating process, and there is no ‘Check for Spam’ button in the approved comments queue. Obviously, you can’t allow those spam comments to just sit there, so here’s the solution. Fair warning: it’s a bit geeky.

Log into your phpMyAdmin interface and navigate to the comments table (wp_comments by default). Click on the SQL tab ,paste this snippet of code in, and click ‘Go.’ 

UPDATE wp_comments SET comment_approved='0' WHERE comment_approved='1'

This will bump all approved comments back to a pending status, which you can then go into and click the ‘Check for Spam’ button. Depending on your server’s settings and the number of spam comments you have to deal with (7000+ in my case), you may end up with a timeout error of some kind, which is ok. Just back up your browser and repeat the check process until all spam comments have been filtered into the appropriate queue. What you should have left are your legitimate comments. (Any leftover spam you should be able to clean up manually without too much trouble.)

From there, depending on how many legitimate comments you have remaining in the pending queue, you can either do a bulk approve using WordPress’s native functions or simply reverse the SQL command you issued above:

UPDATE wp_comments SET comment_approved='1' WHERE comment_approved='0'

Voila! Like magic (and with a bit of brute force), you’ve just gotten rid of a LOT of approved spam comments. It’s not necessarily the most elegant solution, but it beats having to click on every spam comment and preserves all the legitimate comments your readers have made.

Oh, and don’t forget to empty out your spam queue.

Fallen Out of Favor: AVG

2 Replies

noavg Once upon a time, AVG was one of the best free anti-virus programs available. It was fast, thorough, and had a small footprint. Somewhere around version 7 or 8, however, AVG took a turn for the worst — that is to say, it started to turn into what is (unaffectionately) called “bloatware.” It quickly became clear that AVG now requires more of your computer’s resources to run, negatively affecting the operation of all your other processes. AVG 9 was supposed to rectify this by being faster and having a smaller footprint, but in recent weeks, I’ve removed the software from several computers because of the amount of interference it creates with normal operations. Much as I loved AVG in the past, I can no longer recommend it as a quality anti-virus program.

Fortunately, though, Microsoft also recently released a new free program called Security Essentials. Even before it was officially released, Microsoft Security Essentials was receiving rave reviews from beta testers and early previews. Subsequent tests have shown that the program catches up to 95% of viruses — and it has a teeny-tiny footprint. Even while running a full scan, you never notice that Security Essentials is there. Pair it up with Spybot Search & Destroy, and you have a winning combination.