Working With Custom Database Tables In WordPress

Ads by Google

featured wordpress   Working With Custom Database Tables In WordPressI’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.

Ads by Google

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.

wp database browser   Working With Custom Database Tables In WordPress

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.

wp custom page template   Working With Custom Database Tables In WordPress

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

wp custom template working   Working With Custom Database Tables In 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.

wp only registered   Working With Custom Database Tables In WordPress

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

21 Comments - Write a Comment

Reply

Narenjinews

Why have you focused on WordPress. Put something about Blogger.

James Bruce

Blogger is a free blogging platform that gives zero access to the database. WordPress is a self installed PHP application to run on your own server. You will never be able to do anything with blogger beyond blogging. … 

Reply

James Bruce

Marlan, why don’t you think this would be appropriate for WP? As long as you have caching enabled, it should handle this fine. I’ve made similar (scraper) sites in the past, and those were done either with regular posts on another wp install, or using custom post types. WordPress is more than capable of handling huge numbers of entries – makeuseof currently has 12,000 articles on the main site with 100,000 or so comments, and that serves millions of impressions a day. 

Marlan

James – You may have a point. 

I had found an awful lot of articles which led me to believe just putting an extra table in the wp database and then using a page and a little plugin would be easy and perform well. And it was true – in about a day I had my database up online. But now I see that making the comment system work with the database is trickier than I thought – not impossible – but tricky. Not a lot of literature out there on how to add comment filters and I can’t tell if I can filter comments on meta-data… so I have lots of questions on that.I currently have about 200 pages in my “pages” part of WP. But I’m finding having lots of pages is a pain to go through when trying to find and update. But maybe you are right and I should bring everything into the pages methodology. My data doesn’t change much and cacheing would work just fine. Then I just get real comfortable with using the admin search to get to my pages.What do do what to do? That is always the question. 

Reply

Bill Woodland

If you create your own tables in the WP database, does the WP tools/export back it up for you, or do you have to write your own backup routine for your own tables?

muotechguy

The export tool only extracts specific data and stores it as XML file, so I don’t believe it would back up custom tables. Other plugins would though, as well as obviously any regular SQL export.

Reply

Randy

Wow…. some pretty ungrateful comments. This is a great tutorial. Very useful for those with a little more MySQL knowledge than WP knowledge.

I’d be curious as to how you could use the wp_search() function to perhaps search that separate MySQL table as well, if at all possible, rather than using LIKE in a MySQL query.

Great tut.

muotechguy

Thanks Randy. If we assume that modifying core WP code is a no-go, I dont think this would be possible. Presumably, you’d want the information separate somehow though, so my approach would probably be to modify the search results page, intercept the search term, and display an additional set of result using a custom query.

Reply

Igor

Hi
Could you please tell me how did you get your “custormers” table into wordpress database so that it became visible on Database Browser. I added new table inside wordpress database but it wasnt populated on the Database Browser drop down menu

muotechguy

Double via phpmyadmin; how can you be sure you added the table?

Igor

I added it from Cpanel in phpadmin

muotechguy

You’re sure its actually a table in the same database wordpress uses? Maybe you accidentally created a new database. Theres no reason for it not be visible.

Reply

hassan

Hello everyone, please tell how can i access database of WordPress(make new tables and get data from tables)through android application please share any code example if any body have.

Reply

Tom

Hi there,
I followed your tutorial and it works! I used your tutorial to display a list of events.
My question: How can I get detailpages. Regarding to your example, I have a list of customers, but how can I make detailpages for each customer?
Including fancy urls like http://www.domain.com/customer/john-doe/
Do you have a tutorial for that?

Tom

custom post types is not an option. In my event-database there are several options that take a lot of (re)programming. So I prefer working with my own database.

Any suggestions to create detailpages?

muotechguy

I’m not how you would the exact format you specified, but you could pass the customer as a request variable in the url to the customers page, with a custom template.

so, domain.com/customers?name=whatever would find the name variable and display a detail page instead?

This wouldn’t be indexable though, I think.

Reply

Fabio

excellent tutorial, men!..

Reply

Heidi

Great article.
You mentioned that you would post the full code. I can’t find where that is.
Perhaps a good follow up post would be creating a page with a form to add information to that table.

James Bruce

Hi Heidi, glad you appreciate it. The full code block is the one just under the subtitle “securing things”.

This method of working with the database is only really recommended if you already have something external that you’ve used to import or deal with the data. If you’d like to add information to the db from within wordpress, I’d suggest you then make those datatypes into custom posts – see the tutorial here: http://www.makeuseof.com/tag/events-listing-custom-post-types-wordpress/ – that’ll give you a form in the admin section to add new items.

Your comment