I’m building a catalog site in WordPress for one of my collections. The catalog currently exists in a large Excel spreadsheet and I’ve been usng it to create a CSV file that can be imported using the WP CSV plugin. This greatly speeds up the creation of the content for the catalog. My plan is that each item in my catalog is a post and the CSV file contains all of the information for each post. One of the imported values in the CSV file includes is the post date. I imported each item with an identical post date without giving it too much thought. This is because the catalog site isn’t going to actually display any post dates. Anyway, it turns out that this was a bad idea because the Next Post and Previous Post links in the single.php template will not bring up any of the posts with the same post date. A bit of digging about and I’ve worked out why.

My single.php theme file contains calls to previous_post_link() and next_post_link() which are supposed to render the next/previous links. I drilled down into these functions which can be found in the /wp-includes/link-template.php file. Both functions call the function get_adjecent_post(). Looking at that a bit closer it includes a bunch of SQL criteria code (for selecting posts from the same taxonomy for example) and, as you’d expect it creates a date criteria clause. That bit of php looks like this:

$adjacent = $previous ? 'previous' : 'next';
 $op = $previous ? '<' : '>';
 $order = $previous ? 'DESC' : 'ASC';

Those little ‘<' and '>‘ symbols give the answer why my imported posts with identical post dates don’t display in the next/previous post links. The date criteria to select the next/previous post is “greater than” or “less than” and obviously if the post dates are identical they are NOT greater than or less than. So, the solution is to NOT import posts with identical post dates. Unfortunately I’d already done this so I opened up the wp_posts table in MySQL workbench and edited the post date of each post I’d imported. Next time I do a bulk import I’ll make sure to increment the post date column values in the CSV BEFORE I do the import.

By default WordPress category archives are sorted by date.  This is fine when you’re publishing a blog but if you’re using WP for other reasons you might want to adjust the way the archives are sorted.  This is done rather easily by creating a new filter in your functions.php file like this:

//filter to alphabetically sort category archives
add_filter('posts_orderby','sort_category_archive_alphabetically');

And then creating a new sort function like this:

function sort_category_archive_alphabetically($orderby )
{
 if (is_category())
 {
 return "post_title ASC";
 }
 return $orderby;
}

In this example I’m alphabetically sorting all categories.  You could restrict it to a specific category by changing the is_category() call to include a category ID (is_category(8)) or by including a category name (is_category(‘Your Category Name’)).

I’m doing a simple sort by post_title here but more complex sorts are possible.  For example, the WordPress API gives a more complex example that explains how to do a LEFT JOIN in the SQL to select posts and sort by values in tables other than the WP_POSTS table.

I’m building a numismatic reference website in WordPress for the Perth Numismatic Society that is eventually going to list several thousand items along with images. The images will be presented as thumbnails which can be clicked on to show larger images using the Responsive Lightbox plugin. The client wanted a default caption under each image instructing users to click on the image to view a larger one. I honestly thought adding a default image caption would be easy but it turns out it isn’t. Nothing I Googled up actually worked including this example in the WordPress Codex or this plugin. So I actually had to use the old grey matter and work it out myself.

So I had a poke around the and ended up in the /wp-admin/includes/media.php file and found this function and filter:

function image_add_caption( $html, $id, $caption, $title, $align, $url, $size, $alt = '' )
{
//function code removed
}
add_filter( 'image_send_to_editor', 'image_add_caption', 20, 8 );

Ahah, simple. Remove the existing image_add_caption filter and replace it with my own. So, I’ve grabbed that code and added it and a couple of other things to my child theme functions.php file as:

remove_filter( 'image_send_to_editor', 'image_add_caption');
add_filter( 'image_send_to_editor', 'add_default_caption', 100, 8);
function add_default_caption( $html, $id, $caption, $title, $align, $url, $size, $alt = '' ) {

	/**
	 * Filter whether to disable captions.
	 *
	 * Prevents image captions from being appended to image HTML when inserted into the editor.
	 *
	 * @since 2.6.0
	 *
	 * @param bool $bool Whether to disable appending captions. Returning true to the filter
	 *                   will disable captions. Default empty string.
	 */
        if (substr($html,0,strlen('([0-9]+)/', $html, $matches ) )
		return $html;

	$width = $matches[1];

	$caption = str_replace( array("rn", "r"), "n", $caption);
	$caption = preg_replace_callback( '/<[a-zA-Z0-9]+(?: [^<>]+>)*/', '_cleanup_image_add_caption', $caption );
	// convert any remaining line breaks to <br>
	$caption = preg_replace( '/[ nt]*n[ t]*/', '<br />', $caption );

	$html = preg_replace( '/(class=["'][^'"]*)align(none|left|right|center)s?/', '$1', $html );
	if ( empty($align) )
		$align = 'none';

	$shcode = '' . $html . ' ' . $caption . '';

	/**
	 * Filter the image HTML markup including the caption shortcode.
	 *
	 * @since 2.6.0
	 *
	 * @param string $shcode The image HTML markup with caption shortcode.
	 * @param string $html   The image HTML markup.
	 */
	return apply_filters( 'image_add_caption_shortcode', $shcode, $html );
}

You can see I start by removing the existing image_add_caption filter but it turns out this is an utter waste of time because the filter executes even though I’ve removed it. So I’ve got around this by declaring the new filter with a lower priority than the existing one (so it happens AFTER) the existing filter. And then checked the $html to see if it starts with the caption shortcode. That’s this line:

if (substr($html,0,strlen('[caption'))=='[caption')
 return $html;

So, if a caption has already been applied we just exit from our new filter.  However, if there’s no caption we can now apply our default caption like this:

if (empty($caption))
 $caption="Click Image to Enlarge";

I tend to use the Yoast SEO Plugin on most of my WordPress sites. It’s a useful tool that gives you more finely grained control of the on-page SEO for each WordPress post and page. However, if you’re creating custom page or post types or database driven pages it can be useful to be able to override the <title> ,META description, and Canonical URL generated by the Yoast plugin and replace them with your own.  As it turns out the plugin has a pretty good API and it’s a fairly simple matter to use the built in WordPress add_filter function to intercept various Yoast functions and replace them with your own.  To be able to do this you’re going to have to be using a WordPress child theme. Once you’ve done that open up the child theme’s functions.php file in the WordPress Editor.

When that is open add in this line:

add_filter('wpseo_title','change_yoast__page_titles',100);

This creates a filter that intercepts the Yoast plugin before it can output a page title and allows you to create your own. To do that we need to create a function called change_yoast_page_titles (you can change this function name to anything you like). We could change it to something like this:

function change_yoast_page_titles($title)
{
  global $pagename;
  $new_title=$title;
  if ($pagename=='widget')
  {
	global $widget_name;
	if (isset($widget_name))
		$new_title=$widget_name." | ".get_bloginfo('name');	
  }
  return $new_title;
}

What we’re going here is accessing the WordPress global $pagename and testing if it’s ‘widget’. Then if that is true we’re testing for the existence of the variable $widget_name and creating a new title that concatenates that and your WordPress site name. If the page name isn’t widget or the variable $widget_name isn’t set then the title is left at the default set by Yoast. This all ties in nicely with our recent post about displaying child database records on a custom WordPress page type.

Other useful Yoast filters are those that can replace the META description and the canonical URL. The canonical URL is especially useful if you’re tying together custom URL parameters and re-write rules. Filters to replace the META description and canonical URL can be created like this:

add_filter('wpseo_metadesc','change_yoast_description',100,1);
add_filter('wpseo_canonical','change_yoast_canonical',100,1);

And suitable functions to make the actual changes to the description and URL could look something like this:

function wpseo_metadesc($description)
{
  global $pagename;
  $new_description=$description;
  if ($pagename=='widget')
  {
	global $widget_name;
	if (isset($widget_name))
		$new_description='All you could ever want to know about '.$widget_name;	
  }
  return $new_description;
}

function change_yoast_canonical($url)
{
  global $pagename;
  $new_url=$url;
  if ($pagename=='widget')
  {
        global $widget_name;
	if (isset($widget_name))
		$new_url=str_replace(" ","-",$widget_name).'/';	
  }
  return $new_url;
}

In the last entry I ran through displaying group of records from a custom MySQL table and part of that included creating links suitable for the selection and display of child records. If we tie that in with reading custom URL parameters in WordPress we can create a set of database driven pages to display child records. If we again use the example that we want to create some pages about widgets, we’d have custom MySQL table containing all the information about each type of widget. In WordPress we want to display a list of all the widget types and be able to click on a link for each widget type and drill down and see the detailed information for each type. Our URL structure would look like this:

http://www.our-site.com/widgets/
http://www.our-site.com/widgets/blue-widgets/
http://www.our-site.com/widgest/red-widgets/

I’ve already covered how you’d display all the widget types using a ShortCode so in this entry I’ll show how we create the detail page for each type of widget.

Create a Custom Page Type Template

You’ll need to be using a WordPress Child Theme and you’re going to need to create a new custom Page Type Template. The easiest way to do that is to copy the page.php template from your parent theme into the folder for your child theme and rename it. In our case we’ll rename it to widget.php. Then open up widget.php in the WordPress editor and at the top of the file just under then opening <?php tag you have to enter the following code:

<?php

/*
Template Name: Widget Template
*/

Once you’ve done that your new page type template will appear in the Template dropdown when you add a new page in WordPress.

Create a New Page

Now that we’ve created the custom page template we can create a new page in WordPress using the custom template. Go to the Pages->Add New and give your new page a title. The page title needs to be the pagename we’re going to use in our new re-write rules (if you’re not sure what that means then you need to read this entry again). Remember that the new re-write rule looks like this:

array('widgets/([^/]+)/?$' => 'index.php?pagename=widget&widget_name=$matches[1]');

So we’re going to title our new page Widget (because of the pagename=widget part of the new re-write rule). Make sure to select the custom template we created above in the Page Attributes info box on the right of the screen. Click the Publish button to publish the new page. If you view the published page you should see exactly nothing because, of course, we still haven’t put any code in our new page type template to pull data from the database.

Add the Database Code to the Custom Page Type Template

In this case I’m modifying the standard page template for the Reponsive WordPress Template. Your template may look at bit different but the general principles will still apply. Open up the widgets.php file in the editor a enter the following just below the PHP title block.

$page_content= "<p>Oops.  I think you've come here by mistake.</p>";
$page_title='';

$widget_name='';
if(isset($wp_query->query_vars['widget_name'])) 
{
	$widget_name= urldecode($wp_query->query_vars['widget_name']);
}

Here we’ve set some default values for the page title and page content. Then we’re checking for the existence of the URL parameter widget_name which is stored in the WordPress query_vars array and if it does exist it’s getting stored in the $widget_name variable. Now let’s add this code:

if ($widget_name!='')
{

	$widget_name=str_replace("-"," ",$widget_name);
	global $wpdb;
	$authors=$wpdb->get_results("select * from widgets where name='".addslashes($widget_name)."'");
	foreach ($widgets as $widget)
	{
          $page_title='<h1 class="entry-title post-title">Information about '.$widget_name.'</h1>';
	  $description=$widget->description;
          $price=$widget->price;
          $image=$widget->image_link;
          $page_content='<p>Description: '.$description.</p>';
          $price='<p>Price :'.$price.'</p>';
	}
}

That code is fairly easy to understand. If $widget_name is set then we’re removing the hyphens from it and use it with the $wpdb object to do a SELECT on the widgets table. The returned data is then stored in the $page_content variable while the page title is stored in the $page_title variable. Now that the data has been retrieved we need to display it at the correct point in the template file. The $page_title should be echoed like this:

<?php responsive_entry_top(); ?>

<?php echo $page_title; ?>

<div class="post-entry">

Then you can put the page content a bit further down the page like this:

<div class="post-entry">
<?php 
if ($page_content!="<p>Oops.  I think you've come here by mistake.</p>")
{
  the_content( __( 'Read more &#8250;', 'responsive' ) );
}
?>
<?php echo $page_content; ?>
<?php wp_link_pages( array( 'before' => '<div class="pagination">' . __( 'Pages:', 'responsive' ), 'after' => '</div>' ) ); ?>
</div>

Once you’ve done that save the custom page template. To test if the custom page is working load up the page that contains the complete list of widgets (the one we created in this posts) and click on the detail links for one of the widgets. You should then be taken to the details page for the widget you clicked on.

As part of a recent WordPress development project I needed to be able to display data from a custom MySQL database table in WordPress. This data was master product data what would link to child records via custom URL parameters. I’ve covered how read the custom query vars in WordPress . Continuing with the widgets example in that post basically I wanted to create a page of widgets that would link to detail page for each different type of widget. I also wanted to be able to allow the WordPress user to maintain all other content on that page. The solution was to implement the widgets table in a WordPress ShortCode. A shortcode is a short macro code (such as [widget_table]) that can be inserted into a normal WordPress page or post. It will be parsed by WordPress before being displayed an replaced with some other text. In our case we want the [widget_table] shortcode to be replaced by the data we want to display from the custom MySQL widget table.

To create a shortcode it’s simply a matter of adding this code to your child template’s functions.php file:

add_shortcode('widget_table','generate_widget_table');
//[widget_table]
function generate_widget_table($atts) 
{
	return "widget table goes here";
}

Now, if we put the short code [widget_table] in a WordPress post or page and view the page we’ll see the text “widget table goes here”. Of course, that’s not very useful because we’d really like to display some data from our custom MySQL table that contains all of our widget information. To do that we need to make use of the WordPress global database object, $wpdb. We can use that to run a SQL query against our custom table and it will return an array of objects representing each row selected. Each one of those objects has a property representing each column selected from the table. Knowing that we can replace our generate_widgets_table function with something that looks like this:

function generate_widget_table($atts) 
{
	global $wpdb;
	$widgets=$wpdb->get_results("select distinct(name) from widgets order by name asc");
        if ($widgets!=null)
        $content="no widget records selected";
        {
	    $content= '<table>';
	    foreach ($widgets as $widgets)
	    {		
		    $content.= '<tr>';
	    	    $content.= '<td>'.$widget->name.'</td>';
		    $widget_url=str_replace(" ","-",$widget_name);		
		    $content.= '<td><a href="/widgets/'.$widget_url.'/">View More Information about the '.$widget->name.'</a></td>';
		    $content.= '</tr>';
	    }
	    $content.= '</table>';
        }
	return $content;
}

Notice that we iterate through the returned $widgets array and construct a link for each type of widget selected. The link is of the form “/widgets/widget-name/”. This form of URL can be used directly with the custom URL parameters we defined in this post to select individual widget records. Note also that we test that some data is actually selected (using the $widgets!=null test) and display some useful feedback. You might also notice that the generate_widget_table function has a parameter passed to it called “$atts”. While it’s not used in this example it does allow your WordPress users to pass your shortcode some parameters to refine your query or similar. I’ll cover how that works in a future post.

To be able to pull data from custom tables in WordPress you’re almost always going to need some custom URL parameters to select the right data.  For example, let’s assume we are a company that sells widgets, and we have several types of widgets including “Blue Widgets” and “Red Widgets” and “Black Widgets”.  What we want from our new Amazing New WordPress Site is to display a page listing all of the types of widgets we sell and for users to be able to click on a link for each widget type and drill down and see more details on each type of widget.  The URL structure for such a site might look like this (in mod_rewrite permalink form):

http://www.our-site.com/widgets/
http://www.our-site.com/widgets/blue-widgets/
http://www.our-site.com/widgets/red-widgets/

To be able to pull the data for blue widgets from a custom MySQL table we must get some information from the URL the user is requesting. If your WordPress install is using mod_rewrite permalinks then this can be pulled from the URL by adding rewrite rules to WordPress. To do this you must have created a WordPress child theme. Once that is done open the child theme’s functions.php file and add the following code:

/*
* Add re-rewite rules for widget pages.  Remember to refresh permalinks if changing this
*/
function add_rewrite_rules($aRules) {
$aWidgetPage = array('widgets/([^/]+)/?$' => 'index.php?pagename=widget&widget_name=$matches[1]');
$aRules = $aWidgetPage + $aRules;
return $aRules;
}

// hook add_rewrite_rules function into rewrite_rules_array
add_filter('rewrite_rules_array', 'add_rewrite_rules');

The add_filter call allows us to add rules to the WordPress rewrite rule array. The function add_rewrite_rules adds the actual rewrite rule itself. Note that you must refresh WordPress permalinks after you’ve added this code for it to work correctly. Basically the rule we’ve added passes everything after the /widgets/ part of the URL as the URL parameter “widget_name” to a WordPress page called “widget”. The URL parameter will automatically be added to the WordPress query_vars array. Of course we’re going to have to create a WordPress page called “widget” and once we’ve done that the widget_name URL parameter is going to be available for use. You can access it via PHP (perhaps from a custom page template) using this code:

$widget_name='';
if(isset($wp_query->query_vars['widget_name'])) 
{
	$widget_name= urldecode($wp_query->query_vars['widget_name']);
}

Once you’ve read the widget_name parameter you can use it to access database information. Of course you’re going to need to make sure widget_name is unique in your database table. If it’s not you might like to consider embedding a database ID in your product links and parsing that out of the URL parameter. You could do that pretty simply by creating product links that look like this:

http://www.our-site.com/widgets/blue-widgets-1/
http://www.our-site.com/widgest/red-widgets-2/
http://www.our-site.com/widgest/purple-widgets-3/

And then exploding them out of the URL parameter like this:

$widget_name='';
if(isset($wp_query->query_vars['widget_name'])) 
{
	$widget_name= urldecode($wp_query->query_vars['widget_name']);
        $widget_array=explode("-",$widget_name);
        $widget_id=$widget_array[count($widget_array-1)];
}

Next post I’ll cover how to create the master list of widgets including pulling the records from a custom table putting that information in a ShortCode that can be used in any WordPress post or page.