Menu

How to Create a WordPress Database Table

March 1, 2014 - wordpress

Ref:- http://www.kylejlarson.com/blog/2013/how-to-create-a-wordpress-database-table/

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:

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:

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:

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:

get_results(“SELECT name FROM wp_names WHERE girl = 1 and cute = 1 ORDER BY name”);

if(!empty($results)) {
foreach($results as $r) {
echo “

“.$r->name.”

“;
}
} else {
echo “

Boo, we couldn’t find anything that is in all these groups. Try removing a category!

“;
}
?>
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).

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.

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 belo

Leave a Reply