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.