Using custom database tables for your WordPress plugins is one of the way that you can greatly improve the kind of plugins you are able to create. DB tables give you a way to manage, organize, and save data for your plugin, and creating them for your plugin is very simple.
The following code can be placed in your main plugin file. The function, which will create the necessary database tables only if they don’t already exist, will be run when the plugin is activated.
$your_db_name = $wpdb->prefix . 'your_db_name'; // function to create the DB / Options / Defaults function your_plugin_options_install() { global $wpdb; global $your_db_name; // create the ECPT metabox database table if($wpdb->get_var("show tables like '$your_db_name'") != $your_db_name) { $sql = "CREATE TABLE " . $your_db_name . " ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `field_1` mediumtext NOT NULL, `field_2` tinytext NOT NULL, `field_3` tinytext NOT NULL, `field_4` tinytext NOT NULL, UNIQUE KEY id (id) );"; require_once(ABSPATH . 'wp-admin/includes/upgrade.php'); dbDelta($sql); } } // run the install scripts upon plugin activation register_activation_hook(__FILE__,'your_plugin_options_install'); |
Any tips on debugging this? I cannot tell you how many times I’ve tried adding a table in this manner only to get the dreaded “You have X character of unexpected output…” The best way I’ve found to debug this was to call var_dump in the core (cannot remember where at the moment). Wondering if you have any tips to make this go better.
@Zach – I don’t have any particular tips. Using var_dump does work well. If you do it the way I’ve described here, you shouldn’t get the “X char” error.
I had the same issue with this solution. The problem is with $your_db_name variable, which is defined outside the function. Not sure why, but it seems that even with global it don’t work as it supposed to, and dbDelta() don’t create new table cause of that. If you define $your_db_name inside the function – the it works like a charm.
@Pipin, you may want to upgrade your code, while even on codex variable with table name is defined inside the function:
http://codex.wordpress.org/Creating_Tables_with_Plugins
Thanks for the heads up. This tutorial is definitely a bit out of date now. I’ll try and get it updated soon.
To debug activation errors, write the error out to a file and examine that.
function save_output_buffer_to_file()
{
file_put_contents(ABSPATH. ‘wp-content/plugins/activation_output_buffer.html’, ob_get_contents());
}
add_action(‘activated_plugin’,’save_output_buffer_to_file’);
I need the same help. hope anyone has better tips.
Absolutely…I use the same technique, but since dbDelta is so picky you some times get weird errors. My favorite is when the SQL executes just fine when you directly query it, but running it through dbDelta chokes.
Pingback: Create Database Tables When Plugin is Activated | Pippin’s Plugins | WpMash - WordPress News
What about deleting tables when your plugin is removed?
Take a look at the uninstall hook: http://codex.wordpress.org/Function_Reference/register_uninstall_hook
Ok wpdb->insert is not working for me at all.
I’m using it through the ajax so can that be related to my problem. Every other thing i saw on the internet doesn’t help me at all, and all the things are correct checked everything milion times.
Ok let me know if you can help me
thanks for that, some way it helps, but still didn’t cover my question about this 🙁
-I’m trying to connect my DB to my code which I have it already in plugin, to execute a simple PHP code for viewing data form my DB such as name, description, price and the most important “IMAGES”
-I followed your tutorial about “writing you first plugin…” and I got it very well, put I have a question ,, can I replace “single OR singular” by something means like that … if post (or page) = to NAME_OF_POST3 ,, any name that we have created it using dashboard
I’m not sure I understand your question, sorry. Can you rephrase it?
okay
I know about writing plugin,, I wrote one to save a twitter url from the setting which I write it, to show the Twitter info.after the content of pages and posts.. (I was followed you tutorial)
Now I want to write a plugin to be show in the site page ,, which contain photos (as table of photos) and brief info. About each photo, and then user can click on it and see more detail about it , then add to cart it if they want to buy (that item) …. then go to the payment page and pay ,,,
hint: the admin is the one who is gonna add the items info such as(upload photo, discription and price and name ) then it gonna show in the menu…..
I’ve done this before … but in regular a theme, how can I use my codes and do some changes to them to be active in WordPress theme
my fingers confused a little bit in typing,, I am sorry about that
I want to write a plugin to be shown in the site page for example if I have a page called E-menu ,, how can I determine this thing cause I don’t want it to be in all pages, or all post, by using sing/singular…
There’s no difference in writing this as a plugin than there is in writing it as a theme. The code is identical, just in a different location.
you mean that, I can to create my pages inside the “theme folder” which I have it in “wp-content”
And I can determine where I would like to show that page?
such as: sing up, menu item for sell, register page, payment page,, ?
a quick question also, how can I add “log in” above the header,, I know I have to “log in” link automatically down in within the footer,, how can I change that
(I hope to find answers from you) cause I spent weeks to figure out the answers.. andI have no time to finish my task… please help >.<"
I tried using this script, and placed it in my plugins php file and it did not create any tables in the wp db. Did I do something incorrectly?
$jo_plugin_data = $wpdb->prefix . ‘jo_plugin_data’;
function jo_plugin_install() {
global $wpdb;
global $jo_plugin_data;
if($wpdb->get_var(“show tables like ‘$jo_plugin_data'”) != $jo_plugin_data)
{
$sql = “CREATE TABLE ” . $jo_plugin_data . ” (
`user_id` int(11) NOT NULL,
`primary_key` int(11) NOT NULL AUTO_INCREMENT,
`length` varchar(5) NOT NULL,
`ground` varchar(5) NOT NULL,
`date` date NOT NULL,
UNIQUE KEY id (id)
);”;
}
require_once(ABSPATH . ‘wp-admin/includes/upgrade.php’);
dbDelta($sql);
}
register_activation_hook(__FILE__,’jo_plugin_install’);
In the main PHP file of the plugin?
Please remove semicolon in ” ” in our query..
I think this will works
thanx for the post but its not working for me.
need help thank you.
What part of it is not working? Do you get an error message of some kind?
thank for reply.
no error.when ever i ma activate the plugin it should create a table in database right?but its not creating.
Thank you. You always provide clear explanations on wordpress plugin development.
// use this function:
global $jal_db_version;
$jal_db_version = “1.0”;
function jal_install() {
global $wpdb;
global $jal_db_version;
$table_name = $wpdb->prefix . “liveshoutbox”;
$sql = “CREATE TABLE $table_name (
id mediumint(9) NOT NULL AUTO_INCREMENT,
time datetime DEFAULT ‘0000-00-00 00:00:00’ NOT NULL,
name tinytext NOT NULL,
text text NOT NULL,
url VARCHAR(55) DEFAULT ” NOT NULL,
UNIQUE KEY id (id)
);”;
require_once( ABSPATH . ‘wp-admin/includes/upgrade.php’ );
dbDelta( $sql );
add_option( “jal_db_version”, $jal_db_version );
}
function jal_install_data() {
global $wpdb;
$welcome_name = “Mr. WordPress”;
$welcome_text = “Congratulations, you just completed the installation!”;
$table_name = $wpdb->prefix . “liveshoutbox”;
$rows_affected = $wpdb->insert( $table_name, array( ‘time’ => current_time(‘mysql’), ‘name’ => $welcome_name, ‘text’ => $welcome_text ) );
}
————————————————————
//Now that we have the initialization function defined, we want to make sure that WordPress calls this function when the plugin is activated by a WordPress administrator. To do that, we will use the activate_ action hook. If your plugin file is wp-content/plugins/plugindir/pluginfile.php, you’ll add the following line to the main body of your plugin:
// run the install scripts upon plugin activation
register_activation_hook( __FILE__, ‘jal_install’ );
register_activation_hook( __FILE__, ‘jal_install_data’ );
http://codex.wordpress.org/Creating_Tables_with_Plugins
Hey Pippin, what’s the best/ most efficient way to insert multiple tables on plugin activation? I’m writing a plugin to manage a community football league and want to have seasons, standings and fixtures tables created on plugin activation.
Well . . . the same way as is shown here for one table. Any reason that doesn’t work for you?
I’ve been trying for a day to get create a table with a plugin. My plugin has one file: plugin.php
It has one function (create table function). It has one hook (register activation hook).
I found your post. I copied and pasted what you have above exactly into my plugin.php file. I deactivated and reactivated my plugin.
No errors. No database table. I simply have not been able to create the table in my database. I have tried countless variations. Finally just tried using your code exactly, so it should have created a table called “wp_your_db_name”.
Absolutely nothing. So frustrating. Any idea why this might not be working? As I said, it’s not giving me any errors to work with.
Can you show me the plugin? Paste it to pastebin.com and then share the link.
Hi Pippin,
Do you have a tutorial on how to setup a settings page where we interact (store, read, edit, delete) with data onto this db?
I have a simple settings page which I’m grabbing $_REQUEST data from on the top. Is this the best practice?
Great tutorials by the way. Always great information.
I’d recommend Tom McFarlin’s Settings API series: http://code.tutsplus.com/tutorials/the-complete-guide-to-the-wordpress-settings-api-part-1-what-it-is-why-it-matters–wp-24060
Pingback: Custom Database API - Creating the Tables - Pippins Plugins
Hey there,
Thanks for posting this.
In this example, if one of the fields is unique (lets day field2 is email address in a user table) . How would the code look like? Thanks.
You will add a `UNIQUE KEY` after the `PRIMARY KEY`, like this:
Don’t use backticks (`) in the sql for the parameter make sure that the sql statement works.
Have a look @ this article:
https://www.itsupportguides.com/wordpress/resolved-wordpress-undefined-offset-1-in-upgrade-php-on-line-2164-error/
Hi Pippin,
Do you have tutorial for how to create admin page?
Thanks!
My Plugin Development 101 series covers that: https://pippinsplugins.com/series/plugin-development-101/
Nice piece of code
Thanks you save me lot of time.
please next time more understandable
I also use the same process and thanks for sharing this with us.
really need help with this tutorial glad I found your site,