How to Create a WordPress Database Table

I recently launched a new website called My Dog’s Name where people can find dog names for their new puppies. I’ve been building a lot of sites on WordPress and wanted to do the same with this one. The trick was creating a table in the WordPress database and being able to call to it from within my page templates. Fortunately, it was actually pretty easy to do with a little research and testing. This article will cover how you can use the $wpdb class to build your own WordPress web app.

my dogs name home

Creating a WordPress Custom Database Table

Once you have WordPress up and running the first step is creating the new table in our WordPress database. I’ve got phpMyAdmin setup on my server so that’s how I went in and edited the database, but you can use any tool to do this. You’ll want to find your WordPress database, which is typically named something like ‘_wrdp1’, but you can also check the wp-config.php file in your WordPress root director to find the value for ‘DB_NAME’.

Once you’re in that database you should see a list of the different tables (wp_posts, wp_users, etc). Add a new table for your site and give it a name. For my dog site I named the table ‘wp_dognames’. Then I added the columns that I needed and imported my CSV file from Excel with my list of names.

Creating a Custom WordPress Template

Next, you’ll probably want to have a custom template for your page that will be querying the table we just created. The quickest way to do this is to make a duplicate of your page.php file that is inside of your theme folder (/wp-content/themes/your-theme/). Then rename the file to something that you’ll remember (for my site I named it results-page.php) and open it in an editor so we can add some code to let WordPress know what the name of the template is. After the initial <?php we’ll add in a comment with the template name like this:

<?php
/* Template Name: Results */
?> 

Make sure that the edited file is up on your server in your theme’s folder. Now you’ll be able to edit it from within the WordPress admin and it will show up as a template option when you create a new page.

You can go ahead and create a new page and assign this template to it. This will be our test page.

Pulling Data Back with $wpdb Query

WordPress uses the $wpdb class to access data within the WordPress database (and the table we just created). WordPress has a good reference for $wpdb that will be helpful in addition to what I cover here.

In your page template we’ll add in our custom PHP after the WordPress call for the page content:

<?php the_content(); ?>

To start off we’ll set $wpdb as a global so we can access the database and then using $wpdb we’ll run the get_results function to pull all our table’s data into an array called $results and print them to the screen:

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

You can add your own SQL query in there to get the desired results. On my site I’ve got a column called ‘names’ that I pull from to get only the list of dog names. Then I’m using a loop to output each name with some additional formatting. For example to find Cute Girl Dog Names my query looks like this:

<?php	 	 
global $wpdb;	 	 
$results = $wpdb->get_results("SELECT name FROM wp_names WHERE girl = 1 and cute = 1 ORDER BY name");	 	 

if(!empty($results)) { 
     foreach($results as $r) {	 
          echo "<p>".$r->name."</p>"; 
     }
} else {
     echo "<p>Boo, we couldn't find anything that is in all these groups. Try removing a category!</p>";	 	 
} 
?> 

You can use this code example to list out any rows you might be pulling back into your ‘$results’ array. If you only want to pull a specific row or column you can use get_row() or get_col() (checkout the wordpress reference for more info).

WordPress database results

Using Variables with $wpdb Queries

The next step to making this even better is using variables to make the queries. Then a single results page can run a variety of searches. For my dogs site, I’m passing the queries in using parameters from the url (e.g. http://www.mydogsname.com/names/?gender=boy&q=tough). Once this page loads, I’m just setting each of these parameters to variables and then using them to query the WordPress database.

<?php
global $wpdb;

$gender = esc_sql($_GET["gender"]);
$query = esc_sql($_GET["q"]);

$results = $wpdb->get_results("SELECT name FROM wp_names WHERE $gender = 1 and $query = 1 ORDER BY name");

Adding to the WordPress Database with $wpdb Insert

For my site I don’t have a way for users to insert data into my table, but it’s not hard if you’d like to. The code example below adds a new name to the ‘wp_names’ table. The insert function includes the table name, an array of data to enter, and the format the data is in. The first array begins with a column name (‘name’) and then inserts a variable into it ($name). These could also be values themselves instead of variables, but in most cases you’ll be using variables that you captured via a form. The second array contains the variable type for each column inserted into the row. In this example, the first two are strings (%s) and the second two are numbers (%d). You could also have a floating point number (%f).

global $wpdb;

$name = 'Toby';
$gender = 'boy';
$category1 = 1;
$category2 = 0;

$wpdb->insert('wp_names',
     array(
          'name'=>$name,
          'gender'=>$gender,
          'category1'=>$category1,
          'category2'=>$category2
     ),
     array( 
          '%s',
          '%s',
          '%d',
          '%d'
     )
);

Tags: , ,

« Older     Newer »

Leave a Comment

30 Comments

  1. Sebastian

    Hi!

    Thanks a lot, this is exactly what I was looking for. However, I don’t really understand the last part that explains how to add content to the table.

    – Is there another method, like with a plugin or so (let’s say the easy way)?
    – If there isn’t, could you just explain your second array, with strings and numbers and points? I’m afraid I don’t get it.

    Cheers,

    Sebastian

    • Kyle Larson

      Sorry I missed this… basically just starting off giving my variables some values (Toby, boy, etc). Then telling wordpress what table to put those values into, which columns they’ll be in and what type of values to expect.

      $wpdb->insert(‘your table’, ‘your columns and what values to insert into them’, ‘what data type each of those values are’);

  2. Sibi

    Thank you, very good tutorial. I tried it and it’s always give an empty array on my wordpress-site… could you help me?

    • Kyle Larson

      Rather than having logins and storing these in a wp user database, I’m just storing them in localstorage. If you search for HTML5 localstorage there are a bunch of articles on it… this for example: http://diveintohtml5.info/storage.html

      Basically, I’m firing a JavaScript function when they’re clicked checking if it is checked and if contains a value and, if so, then adding that value into localStorage. If it’s being unchecked then it removes that value.

  3. Mark

    Thanks for the article! I am just starting out with WordPress and need to query a table like this.
    The think I don’t understand is how to connect the form to the search results.

    In your example, would the HTML form be at the top of the results-page.php?

  4. Concerned Third Party

    This was mentioned before, but it’s worth saying again. “IF YOU USE USER DATA WITHOUT SANITIZING IT FIRST YOU ARE VULNERABLE TO SQL INJECTION.” please update your article to fix this asap. A lot of people might do something similar and compromise the security of their website. Thanks! 🙂

  5. WPAnthony

    Very helpful post Kyle! I used to do all these custom coding too for my WP site until I found this tool called Caspio. You can create and embed all sorts of tables, searchable databases without any coding. Here’s one of their videos that shows how to build and embed a searchable contacts database in a few mins: https://www.youtube.com/watch?v=BgHV7ZPplo0 They also have a free account.

  6. Scott

    How would I change the query to pull 5 items from a specific row or column .
    I want a user to choose an option from a drop down menu and be shown all the data for that item in a table.

    Any suggestions?

  7. Xavier

    Hello Kyle,
    thanks for your post, these are indeed precious informations! I m trying to adapt it to my case. How did you get these ticking-squares? Are these all provided by your template? I could not recognize any of these in the code you provided.
    Regards,
    Xavier

Back to top
More Posts