Displaying Child Records from a Custom MySQL Table in WordPress

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.

This entry was posted in php, WordPress on by .

About markn

Mark is the owner and founder of Timesheets MTS Software, an mISV that develops and markets employee timesheet and time clock software. He's also a mechanical engineer, father of four, and a lifelong lover of gadgets.