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 lineHow To Backup Your Website Through SSH Command LineHow To Backup Your Website Through SSH Command LineBacking up your website or blog can be an expensive and arduous task, requiring a variety of plugins, or additional plans from your hosting provider - but it needn't be really. If you have SSH...Read More 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.

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.

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

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:

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):

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:

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.

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:

Hi James, fantastic article. I have a question. Since you have a custom page template, that probably means that you don't have a way to use this method to access data on a page or post or something that is independent of the theme, right? If you change the theme, you'll have to make a new custom template for that theme. So is there is a way to do it that will work fine even if you change the theme?

I thought of having just a PHP include that would do the work and I could include it in a custom template file so there is slightly less work in theme change, but still not great.

I know you've mentioned custom post types in the comments. Could that work somehow for this? I read your next post about AJAX/form submission/SQL INSERT as well, but I see modifications to the theme file functions.php there.

I suppose you could do this from a template by hooking into a specific page name - I've just always preferred to hack things into the template for ease and portability (only one thing to edit and remember to update/move).

Hi, i tried this tutorial and its great but it is not displaying any values, if i use the "ïf use logged in" and i run page while logged out, it displays that i have to be logged in, but its not displaying results. please help

probably just an error in your code. Make sure youve enabled debug mode by changing the line in wp-config about wp_debug from false to true. Post back with the error and code if you don't know how to fix it.

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

I am wondering if anyone can help me. For a start this is ideal for me but I need to use a form to add an entry to a database. Is it as simple as using this and then leaving the action blank to make it submit to the same page? I can put together the php to write to the database ect. It's just most the solutions I have tried and simply taken me to a 404 error page after I submit the form.

First Of all Thank you for the awesome tutorials :)
My project was to create a form ,store its data to database and get the data on front page so i just did it.
but i want it like wordpress posts. Mean to say when custom row id is click i want to open the content in single page how can i do that.
i think it is not impossible although difficult

Two ways. You can either make your new content as a new custom post_type, which would give ti the same structure as any other wordpress post. Or you could direct all clicks to a single page which acts as the handler - pass in the item ID as a parameter on the link, and then that page fetches the relevant post. That would give you a single URL for SEO purposes, which has dynamic content depeneding on parameter. If you want Google to index this content, you will need to work within WordPress cusotm post types.

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.

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?

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

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.

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.

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.

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. ...