A-Z Category Index WordPress Plugin
If you have a lot of categories on your WordPress website, you might want to organize these categories by each alphabetical letter. In other words, build an A-Z index that allows an end user to click on any letter, which then loads a page that lists all the categories that start with that letter. This is often the design or interaction in music websites which deal with many artists and albums.
I noticed that some people have been looking for this type of WordPress plugin but most of the plugins I have found for such organization have been developed around posts not categories. So, I decided to build this plugin, but more importantly, document the process, as this WP plugin will make a new table in the database, runs queries, hook into WordPress actions, leverage a page template, and much more.
My first thought is that you could manually create this A-Z index, create all the pages for each letter, create a template file for these pages where you would grab all the categories and loop through them to retrieve the correct categories per letter. The small problem with that approach is that it is manual work and the big problem is that it is not going to perform very well when you have 100s if not 1000s of categories.
Another approach would be to make 26 A-Z categories and use sub-categories organized under each parent category letter. This would allow you to use get_categories call more effectively but it would move the manual process to category creation and you would always have to think about this child-parent relationship when using categories. This isn’t really ideal, what if you don’t want the letter to be in your link structure? Either way, any category creation or import still needs some function or manual work to place the category under the appropriate letter.
My approach is to create a new table in the database which stores each category, the category ID, and the first letter so that the code can really quickly query the database using a category letter. This table gets created through a WordPress plugin when it gets activated, at which point it also grabs all the current categories and inserts them into the new table. Then, it hooks into WordPress category creation, edit, and removal actions to update the tables accordingly. It creates a page template file with a function that will query the categories by letter from the new table. Finally, it provides the end user a simple function that can be placed into any PHP file to render the A-Z letter index.
WordPress SQL Taxonomy
I took a look at WordPress database structure and realized that all categories and tags live together under wp_terms table and you cannot tell them apart without an extra lookup in wp_term_taxonomy table. Another interesting point is that term_group under wp_terms is not really used for anything and I could use it for the letters (represented as numbers since it it set as bigint). But, this doesn’t really help much since you cannot make the get_categories call to include something by term_group, only order by it, and I really want to abstract this plugin from the default WordPress tables and fields.
So, let’s have the plugin create it’s own table. The WordPress documentation on table creation was actually really helpful and here is my SQL query to create a new table using any already created categories:
CREATE TABLE $table_name AS SELECT SUBSTR(name,1,1) AS alpha, term_id, name FROM wp_terms WHERE term_id IN (SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE taxonomy = ‘category’)
Let’s dissect this for a second. First of all, I chose to do it this way because we will always have a category (Uncategorized) and I wanted to capture, insert, and organize all the already created categories into my new table when the plugin gets activated. The SUBSTR part takes the first letter from the category or tag name in wp_terms and WHERE term_id IN array matches any categories or tags in wp_terms to the category taxonomy defined in wp_term_taxonomy. As a result, we are only indexing categories and not tags in our new table. Finally, we add a DROP TABLE IF EXISTS $table_name SQL call to another function that fires when the plugin gets deactivated.
Hooking into WordPress Admin Actions
Next, we need to decide on how this table can be updated when we add, edit, and delete categories. We could create an index button somewhere that would just rerun a similar SQL call from above but I wanted to make this more automatic and hook into WP Admin category functions. So, I created 3 functions and 3 WordPress add_action hooks:
add_action(‘create_category’, ‘tcreate_category’);
add_action(‘edited_category’, ‘tedit_category’);
add_action(‘delete_category’, ‘tdelete_category’);
This part is relatively straightforward, when you create a category, the tcreate_category function fires, grabs the name of the category you created, grabs its first letter, grabs its id, and makes an SQL call to insert the category into my table. The edit category function does an update SQL call on my table and when you delete a category, I run a DELETE query on my table. You can see the details of this in the PHP plugin code below.
Creating WP Template File with a Query to retrieve correct categories
I decided on using pages to display a list of categories by letter because I liked the clean URL structure as well as the ability to utilize a page template for WordPress database queries. The problem with a page template is that it generally lives under the theme directory so we need a special function in our plugin to help WordPress find the page template stored in my plugin directory. We add an add_filter action for page_template which points to a function that utilizes the is_page check, and if the end user is requesting one of our single letter pages, it points the page template path to the plugin page template file.
I created that page template file with special logic and queries to retrieve the proper categories. Since this page template is used for all A-Z letter pages, we first need to figure out what letter is being requested. This can easily be done with pure PHP using REQUEST_URI:
$pageletter = basename(parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH));
But, this made me consider changes to permalinks. From my quick tests, it seems that pages only respond to either the default permalink structure (using page_id=ID) or the page title. Clearly, if someone out there is using the default permalink structure, my $pageletter variable above will not retrieve any letter. So, I first test for URL query string (page_id=ID) in the URL, and if it exists, I grab the page ID followed by a WordPress get_page_uri call to get the letter. Otherwise, I just grab the pageletter using the statement above.
Now, category structure depends on having default permalinks versus something custom and the category base option. If you are using the default permalink structure, categories are always referenced via IDs (?cat=ID), otherwise, any custom permalink structure results in categories being reference via category or whatever has been specified in the category base option. So, I make a call to see if non-default permalinks have been set:
$az_permalink_query = $wpdb->get_var( $wpdb->prepare(“SELECT option_value FROM wp_options WHERE option_name = ‘permalink_structure’”));
If this returns blank, we know we have default permalinks and need to use category ids. Otherwise, I look at the category_base field in the database:
$az_cat_query = $wpdb->get_var( $wpdb->prepare(“SELECT option_value FROM wp_options WHERE option_name = ‘category_base’”));
If this returns some name, we use that name in creating the category link, otherwise we just use the word category (default). One edge case to this is that if you do not define a category base and use a non-tag word (for example archives in /archives/%post_id%) in the permalinks structure, the category permalink will also use that non-tag word in the category link. Basically, if default permalink structure is set, categories are always referenced via ?cat=ID. If a permalink structure is changed and category_base is set, categories are always referenced via category_base word. If category_base is not set and permalinks are not using default settings, categories are referenced by any non-tag word in permalink structure followed by the word “category”. Take a look at the code in the page template to see this conditional.
I used the WordPress get_site_url() call to grab the website url and the following SQL query to retrieve category names and ids by the page letter:
$myrows = $wpdb->get_results($wpdb->prepare(“SELECT name, term_id FROM wp_categoryindex WHERE alpha = ‘$pageletter’”));
As you can see, we grab the name and id from our table where it matches the letter of the page we are on. After that, I make a foreach loop and structure the output on the page in a list format with links to each category. If we are on the “0-9″ page (for categories that do not start with a letter), I use the following SQL call:
$myrows = $wpdb->get_results($wpdb->prepare(“SELECT name, term_id FROM wp_categoryindex WHERE alpha IN (’0′,’1′,’2′,’3′,’4′,’5′,’6′,’7′,’8′,’9′)”));
Creating WordPress Pages and Sub-Pages on Plugin Activation
When the plugin activates, we need to create these A-Z pages. This is pretty easy, I used the PHP range(‘A’,'Z’) function in a foreach loop and wp_insert_post call to insert the pages into WordPress. I added an extra call for the 0-9 page. Since, wp_delete_post uses IDs that I did not want to store anywhere, I created a single SQL call to delete all these pages when the plugin gets deactivated:
$sql = “DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_title IN (‘A’,'B’,'C’,'D’,'E’,'F’,'G’,'H’,'I’,'J’,'K’,'L’,'M’,'N’,'O’,'P’,'Q’,'R’,'S’,'T’,'U’,'V’,'W’,'X’,'Y’,'Z’,’0-9′)”;
As you can see, deleting pages or posts requires changes in wp_posts, wp_postmeta, and wp_term_relationships tables.
WordPress Plugin creating the A-Z Category Index
The final part of this plugin deals with giving the WordPress Admin a function that creates the A-Z Category Index, which could be placed anywhere in the template pages. I think such an index should probably live in the header.php file but it could live anywhere. So I created a simple add_cat_index() function which returns the A-Z index html code, but it again made me think about the issue of changing permalinks. If your permalink structure uses IDs instead of names, I will need extra queries to figure out where each page in the index is located.
I ended up using get_permalink and get_page_by_title calls for each link to a letter page. This is not ideal but it will support all kinds of permalink settings and it really highlighted the point that everyone using WordPress should really use some caching plugin. WordPress by itself makes many database calls for each post or page but if you add to it some fancy plugins, there is a lot of database interaction that could be removed for some time period with caching.
I hope this tutorial is helpful to others, I wrote it as a reference for my future plugin work. Here you can download the AZ-Category-Index WordPress Plugin. You can see a live demo of this plugin on jLyrics.
Plugin Installation Instructions
- Download the plugin using the link above and unzip the contents.
- Move the unzipped categoryindex folder into your WordPress plugins folder.
- Find and activate the plugin for your WordPress Admin Plugins page
- Edit your theme files (I edited header.php) and place this code where you want the A-Z index to appear: echo add_cat_index();
