Search problem in WordPress 2.3.1 fixed

One of the reasons I have no problems making a living on recommending clients around the world investing in commercially available Content Management Systems instead of going for open source solutions, is the inherent lack of control when it comes to releases or even the most basic quality control across the vast open source domain.

I love WordPress and have been using it for years for that reason. I run several websites based on this platform and can only recommend this as a very flexible blogging solution. And WordPress is doing well. A lot of people have adapted WordPress over the years.

Unfortunately popularity apparently introduces a darker side of the internet. WordPress has been target for several security exploits, I have seen examples if backdoors and other security threats being build in to some nice looking themes etc. You will have to be alert online – just like in the real society.

This is not true for WordPress alone. Just look at what is going on in the hithertho safe Apple world – I had a major security update coming in again yesterday and trust we can all expect to see an increasing frequency in security updates from Cuppertuno in the future.

Needless to say, when WordPress releases an urgent security fix, I feel sincerly inclined to mend the holes immediately and get all of my websites up to date.

And here comes trouble. Almost everytime I have done so, something stops working.

Here comes trouble - Machete from the Grindhouse project

It is usually one of the plugins and it always involves some level of code inspection that I would rather be without. Rather because I basically have to re-learn the some internals of WordPress and sometimes analyze the database structure. This is a major task for me and if there is something I have in limited stock, it is my time.

However, when I am am traveling I do get some extra time in airports around the world. Hence, this is where I do most of my website updates. As a sad consequence some of my sites will be left down and out for months because the upgrade just did not work for some reason – and I had no further time to invest. The plane was waiting…

Sometimes, I am lucky though. And during this trip in APAC, I have had enough stopovers to upgrade and fix most of the issues that came with the latest WordPress 2.3.1 upgrade. One of the sites I manage is Lyngby Taekwondo Club where we have quite a few active content contributors and a very successful WordPress installation going in general. We use it for almost everything and I have been building extensions to cover theory etc and naturally taking advantage of many of the plugins generally available.

Calendar and Activities

Now here is a tough one for somebody rather unfamiliar with PHP programming. A central part of the website is the club based activities we are announcing on the website. The well written plugin I have used for that Eventcalendar3 is also offering activities to be avaliable for searching.

Or at least that was the authors intention. And it used to work. Until this release…

When I did a search on a simple term like Course (Kursus in Danish) the website front pages was replaced by this piece of information:


SELECT SQL_CALC_FOUND_ROWS lwp_posts.* FROM lwp_post s
LEFT JOIN lwp_post2cat ec3_post2cat ON ec3_post2ca t.post_id=id
LEFT JOIN lwp_ec3_schedule ec3_sch ON ec3_sch.post_id=id AND c3_sch.end>=’2007-12-22
00:00:00′
WHERE 1=1 AND (((post_title LIKE ‘%kursus%’) OR (post_content LIKE ‘%kursus%’)))
AND post_type = ‘post’
AND (post_status = ‘publish’)
AND (ec3_sch.post_id IS NOT NULL OR ec3_post2cat.category_id!=5)
ORDER BY post_date DESC LIMIT 0, 5

You probably already guessed what is going on here. Looks like the WordPress team decided to change the database structure and introduced a couple of new tables to include taxonomy and dropped the old superfluous tables. Mind you, we are talking about a minor release :-)

I have fixed this along with another problem related to being able to search pages instead of just searching posts as well. See below if you are interested in this.

Technical Details

you can read about the new tables at wordpress.org but in summary, it looks like the old table post2cat is dropped in favor of three more generic tables for taxonomy:

term_taxonomy is used to host the new taxonomy, e.g. as “category”, or “link_category”.
terms holds all the term names, e.g. 1:Uncategorized, 2:Information etc…

And finally the key to fixing my problems in this upgrade:

lwp_term_relationships:
object_id  - link to lwp_post, and
term_taxonomy_id - link to lwp_terms

Eventcalendar table
I can see from the error message above that not only is the now deprecated post2cat table being used, but also a table used by the Eventcalendar module:


lwp_ec3_schedule
sched_id
post_id
start, end.allday,rpt

So the key here is to get the failed SQL statement working again. After looking at tables and field (and trying to remember what a LEFT JOIN is doing :-)) I managed to get the statement working in the phpmyadmin tool so kindly offered by my hosting provider.

The working statement looks like this:

SELECT lwp_posts.* FROM lwp_posts
   LEFT JOIN lwp_term_relationships cat 
          ON cat.object_id=id 
   LEFT JOIN lwp_ec3_schedule ec3_sch 
          ON ec3_sch.post_id=id 
          AND ec3_sch.end>='2007-12-22 00:00:00' 
WHERE (((post_title LIKE '%kursus%') 
  OR (post_content LIKE '%kursus%'))) 
  AND post_type = 'post' 
  AND (post_status = 'publish') 
  AND (ec3_sch.post_id IS NOT NULL 
  OR cat.term_taxonomy_id!=5)
ORDER BY post_date DESC LIMIT 0, 5

Code updates

Now, a bit of searching relearned (yes, re-learned!) me that search is done internally in WordPress and cannot be found as a separate function anywhere. I took a lot of FTP before I identified the module that I could update and fortunately it could all be done in the Eventcalendar3.php module.

Fixes in Eventcalendar3.php:

In Line #227 update this:

$where.=" AND (ec3_sch.post_id IS NOT NULL OR " ."ec3_post2cat.category_id!=$ec3->event_category)"; 

to use the new taxonomy term instead of the old cat2post table:

          
 $where.=" AND (ec3_sch.post_id IS NOT NULL OR ". "ec3_post2cat.term_taxonomy_id != ". $ec3->event_category)";

and in line #251, this code:

$join.=" LEFT JOIN $wpdb->post2cat ec3_post2cat ON ec3_post2cat.post_id=id";

Should be updated to:

$join.=" LEFT JOIN $wpdb->term_relationships ec3_post2cat ON ec3_post2cat.object_id=id";

Searching in pages problems fixed

Now that I was looking the error message it dawned on me why the plugin I had deployed for being able to search in all the articles we have written, but not as posts, for some reason had stopped working some time ago as well.

The plugin author David B. Nagle’s has apparently given up. My theory is that the poor guy may have a day job and like other WordPress users like myself, only have limited time available keeping up to date with the developments across the huge open source project.

Nevertheless, the fix is easy and by using the phpmyadmin sql interpreter again, I came up with a modified version of David’s original plugin that get’s the job done.

New plugin: tbr_search_pages

It is available for download here: here.

Installation

  1. Upload tbr_search_pages.php to the /wp-content/plugins/ directory
  2. Activate tbr_search_pages through the ‘Plugins’ menu in WordPress.
  3. Et voila, you can now search in paged content as well

4 Replies to “Search problem in WordPress 2.3.1 fixed”

  1. Believe it or not. I had to do another very important security upgrade of the WordPress system today.

    Fortunately nothing broke this time. Phew.

  2. Sorry, your link to tbr_search_pages.php doesn’t work at all. I believe that I’ll find some properly working search script. The newest WordPress 2.3.2 has a serious problem with search function.

Leave a Reply

Your email address will not be published. Required fields are marked *