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.
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!";?>
<!--?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:
<?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.
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.
No comments:
Post a Comment
[Please do not advertise, or post irrelevant links. Thank you for your cooperation.]