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 »
  • Pingback: How to Create a WordPress Database Table | Design News()

  • 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

  • ravin sharma

    Thank you. This is good reference.

  • 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?

  • newWpUser

    Thank you, it’s a good tutorial. I tried it, but the output is always a empty Array… Could you help me….

  • Pingback: How to Create a WordPress Database Table | My Code()

  • Rob

    How did you add the check boxes to return true and false values (add or remove saved items)?

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

  • 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’);

  • Ms. Green

    If you use raw $_GET without sanitizing it you open up to SQL injection.

  • Gonkas

    How do you put variables inside the query? $gender it works?

  • Navneil Naicker

    What if we want to paginate the custom tables. Do you have tutorials based on it.

  • Pingback: How to Create a WordPress Database Table | Anil Kumar Suryavanshi()

  • conkid

    Really appreciate the references – they helped a lot.

  • 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?

  • Pingback: Adding a table to WordPress | Virgil Bierschwale()

  • Pingback: Should I use an additional column in the DB? - Wordpress Helpdesk()

  • Zack Ward
  • Freelancer Web Designer

    Thanks,Nice Information……………,
    Freelance Web designer in Hyderabad

  • PAl

    Hi

    How to create database in wordpress

  • 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! 🙂

  • Thanks! I updated the article to esc_sql on the get statements to help this.

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

  • Thanks this is Very Good Coding Technique.. awesome Thanks for sharing..

    http://evincetech.com/CMS-applications.html

  • Lily

    It’s really helpful for me to h=get the tips of creating wordpress database table…but how about create a database on web hosting, I got the answer from https://hostingreview360.com/how-to-create-database-web-hosting/

  • David Hunter

    What if you wanted to add/update/delete data from your custom db table ?

  • 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?

  • If you follow the example above ‘name’ is a column (eg. SELECT name FROM wp_names …) you could use a variable there to call whichever column the user selected. Then you should be able to add a limit (eg. SELECT name FROM wp_names LIMIT 5)… http://www.w3schools.com/php/php_mysql_select_limit.asp

  • 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

  • chad480

    The codex is a good place to go: https://codex.wordpress.org/Class_Reference/wpdb

Back to top
More Posts