Working With Custom Database Tables In WordPress

Ads by Google

custom database tableI’ve shown you many ways in which WordPress is already the most flexible CMS. A quick scan of the Best of WordPress Plugins page will also reveal some of the many unique and niche ways you can make your blog work harder. I’ve even shown you how to make use of custom post types to create your own mini database; but I’ve left one thing out, I think.

What if you already have a database of say, customer information, but you want to be able to query that data and display it within a WordPress template? Today I’ll be showing you just how to do that, safely within the WordPress engine.

Requirements

  • Your own self-hosted WordPress site, obviously.
  • Basic PHP and MySQL skills – I recommend the Tizag PHP and MySQL tutorials, as they cover more than enough and you can work through them in a day and reference them again when needed.
  • An existing dataset in MySQL.
  • Command line of PHPMyAdmin access to merge the databases.
  • A single database with both datasets – this means you either need to merge your WordPress database tables into an existing database and change wp-config.php to reflect the new database username and password details; or import an existing dataset into your WordPress database. It’s easier if you don’t have another system that’s relying on the data. Either way, I’m going to assume you’ve done this step already – check out my article on how to do a full database backup via an SSH command line if you need some pointers there.

This tutorial is about as advanced as we are going to get at MakeUseOf, but it should open up a world of possibilities to you.

Why Would I Do This?

Despite the many plugins and extensions available to us in WordPress, sometimes you already have a dataset and migrating it to a format WordPress likes would be more hassle than it’s worth – especially if you then have another system you need to interoperate with.

Today, I’ll be taking the example of a simple customer information database, and we’ll be creating a page template that lists these customers – only to registered WordPress users (though the page itself will be accessible from the front end of the site).

As a reference for column and table names in the database, you might find it helpful to install the Database Browser plugin, which will also let you run basic where and order by queries to test your SQL code. Here’s a screenshot with a sample dataset I’ve created – in this case, a table called Customers, containing some basic information about each of my very important clients.

custom database table

Ads by Google

What Precisely Are We Going to Do Here?

  • Creating a new page template which we can then apply some custom PHP code to.
  • Looking at how to create a custom query to the database, and then parse the results – using built-in WordPress database classes.
  • Looking at permissions in case you want to restrict access.

Making A Custom Template

If you want to use some of your own PHP code, the easiest way to do this is to create a custom template, then apply the template to a particular page you create in WordPress. Start by opening up your theme files and duplicating the page.php (or single.php if there isn’t one). Rename it something obvious, like “template-customers.php” as I’ve chosen.

At the very top of the file, we need to tell WordPress this is a custom template. Do this by adding the following (this is a PHP style comment, so it should be after any opening PHP tag if present):

/*  Template Name: Customers  */

Obviously, call it whatever you like.

Now, find the main content function. You can delete it if you want, but I’m just going add the extra code after it. With the default twenty-eleven theme, you’re looking for:

<?php get_template_part( 'content', 'page' ); ?>

But in most themes, it’ll be something like:

<?php the_content();?>

That’s the bit that displays your post content, so anything you add after that will be shown just after the main content area. Just to check it’s all working, let’s add a basic echo statement and save the file.

<?php echo "This is our custom template!";?>

Before we can check this, we’ll need to create a page on the WordPress admin page, and apply our page template to it.

custom database table wordpress

Publish, and check out the page to see if your echo statement has worked.

custom database table wordpress

The Custom Query Class

To gain direct access to the database, all you need to do is use the $wpdb object by making it global. These three lines should do it – replace the generic echo statement we made earlier with this:

<?php  global $wpdb;  $customers = $wpdb->get_results("SELECT * FROM customers;");  print_r($customers);  ?>

Save, and refresh the page. The print_r() function just dumps out all the data from the customer’s object – so you should see that your simple SQL statement to select everything from the customer table has worked nicely. Now all you need to do is parse the results to something useable. Of course, you can put any SQL select statement into the get_results() method, but I’m not here to teach you SQL so we’ll stick with just grabbing everything for now.

To parse the results out into something more meaningful, I’ll just be using a basic table for now. Replace the print_r method with the following code (don’t worry, I’ll be pasting the full code later on if you don’t want piece it together yourself):

echo "<table>";  foreach($customers as $customer){  echo "<tr>";  echo "<td>".$customer->name."</td>";  echo "<td>".$customer->email."</td>";  echo "<td>".$customer->phone."</td>";  echo "<td>".$customer->address."</td>";  echo "</tr>";  }  echo "</table>";

Once you have each customer object inside a foreach, you can access the field names easily with $customer->field_name – it really couldn’t be simpler.

Securing Things

In this instance, I don’t really want my customer data displayed to just anyone and indexed by the search engines – but I do still want it on the front end displayed using this template; so what can we do? Easy, we’re going to make use of the WordPress conditional is_user_logged_in(), and display a quick message if they aren’t. Here’s the whole block of code again with the new conditional added:

<?php    if (is_user_logged_in()):    global $wpdb;  $customers = $wpdb->get_results("SELECT * FROM customers;");    echo "<table>";  foreach($customers as $customer){  echo "<tr>";  echo "<td>".$customer->name."</td>";  echo "<td>".$customer->email."</td>";  echo "<td>".$customer->phone."</td>";  echo "<td>".$customer->address."</td>";  echo "</tr>";  }  echo "</table>";  else:  echo "Sorry, only registered users can view this information";  endif;    ?>

Save and refresh, and you should still see the content. However, log out, then refresh the page, and you’ll now see the “Sorry, only registered users…” message.

custom database table

If you wanted to restrict this to certain levels of users rather than all registered users, then you would use the current_user_can() conditional instead, along with an associated capability (read more about capabilities on the codex). This would check for admin users, for example – the only users who can manage plugin options:

current_user_can( 'manage_options' )

Summary

I’m going to leave it there today as anything else would become an SQL tutorial or how to style your output with CSS (we have an excellent free guide on that, by the way). The sky really is the limit with WordPress, and I hope this comes in useful to some of you in your WordPress projects.

Next week I’ll be tackling the slightly trickier topic of how to insert data back into your custom database using a form on the page, and a bit of AJAX/jQuery magic.

Do you have any problems, or do you want to shoot some ideas off me? The comments are open for all, so please go ahead and I’ll try my best to answer them.

Ads by Google
Comments (34)
  • shilpi

    This is just fantastic…. I was desperately searching for this… Thanks… thank you very much

  • SimbaLion

    Hi I tried following this with some minor adjustments but it is not working. Here is what I’ve got:

    get_results(“SELECT * from business where id=1;”);print_r($business);
    ?>
    name; ?>

    the print_r function works and prints out the whole array, but the echo function displays nothing. What am I doing wrong? :(

  • Dan

    Thanks man. This helped a lot and was exactly what I needed.

  • Kossi

    Hey James,
    Would please tackle the topic of how to insert data back into your custom database using a form on the page, using AJAX/jQuery as you have mentioned it earlier? That will be much appreciated.
    Thank you James

  • Kossi

    Awesome work man! You really nailed this tutorial down.

Load 10 more
Affiliate Disclamer

This review may contain affiliate links, which pays us a small compensation if you do decide to make a purchase based on our recommendation. Our judgement is in no way biased, and our recommendations are always based on the merits of the items.

For more details, please read our disclosure.
Affiliate Disclamer

This review may contain affiliate links, which pays us a small compensation if you do decide to make a purchase based on our recommendation. Our judgement is in no way biased, and our recommendations are always based on the merits of the items.

For more details, please read our disclosure.