SHOW TABLES LIKE

Is there any reason not to use $wpdb and a SHOW TABLES LIKE tablename query to see if a table already exists?

--Robert Deaton 10:31, 1 Jul 2005 (UTC)

No, page updated.

--J2000ca 06:54, 28 Aug 2005 (GMT)

Actually, yes, there is a good reason not to use SHOW TABLES LIKE instead of using options. Two of them:

1- Hopefully WordPress has already cached some data by the time your plugin executes the code to create the tables. That data would include options. You can potentially save yourself a SHOW TABLES LIKE query on every page execution by looking at the pre-cached options rather than running that query.

2- You can check the version of the table using the stored option, which you can't do with a simple SHOW TABLES LIKE query.

It would be dangerous to assume that your plugin's activation event fired successfully to install your table, or that the user of the plugin installed it correctly at all, or that the table is a current version just because it exists.

I suppose you can write your plugin however you want, but using options would potentially generate fewer support issues for people who screw up their installs in the first place. I'm not sure how important that is to you, but I know I would rather not hear from the folks who don't follow the installation instructions, and if this minor issue helps avoid that, then cool.

--Ringmaster 14:01, 21 Mar 2006 (GMT)

Actually, dbDelta() is supposed to do this check itself, so it really isn't necessary to check the database option before calling dbDelta(), although this does avoid extra overhead. For the activation hook, I might just call dbDelta() without checking the option value. That way, if a the initial install doesn't work for some reason, or the database happens to get messed up somehow, all the user has to do is deactivate their plugin and activate it again, and the database will be created/fixed by dbDelta(). For the update function that is called on every page load, though, Ringmaster is right, the option check is much faster.

--Jdgrimes 20:11, 24 July 2014 (UTC)

global variables and register_activation_hook

I just tried the example code from the article page and $jal_db_version never has a value inside jal_install(), even though it's declared global. If I put a regular call to jal_install() instead of "register_activation_hook(__FILE__,'jal_install');" then $jal_db_version has the value expected.

I'm new to PHP, so I don't understand all the scoping and visibility rules, but it seems that whatever context jal_install() is called in when using register_activation_hook can't see the global variable in the same source file. I solved the problem using a constant instead of a global variable.

--JonathanRogers 11:35, 5 May 2008 (UTC)

This should be fixed now. The problem is that you have to declare $jal_db_version to be global wherever it is originally defined, because the plugin itself is not in the global scope, it is being included within a function. That's why it is best practice to explicitly declare a variable global where you define it if you want it to be global.

I use constants as well, and I prefer that. It doesn't pollute the global variable scope, and avoids issues like this since it is always global. Perhaps the examples should be updated to use a constant.

--Jdgrimes 20:01, 24 July 2014 (UTC)

If this is a Must Use Plugin the you cannot register activation hooks. You must take care of the database initialization in the init hook.

Earnest Boyd, EBoyd53 Enterprises, LLC (talk) 22:28, 6 March 2015 (UTC)

Adding an Upgrade Function

This string:

if( $installed_ver != $jal_db_version )

If $installed_ver is empty or not set, also be different than $jal_db_version. Why not:

if( $installed_ver and $installed_ver != $jal_db_version )

--metacortex 13:52, 05 Jan 2010 (UTC)

Why would the installed version be empty? You should define it in a constant or a global variable as shown in the examples. I don't think this should be changed.

-Jdgrimes 19:56, 24 July 2014 (UTC)

Multisite-aware example needed

This document is misleading in that it does not offer code, or even make mention of the fact that table creation in a multisite environment must keep whether the plugin is individually or network activated in mind. It's not clear from this documentation where the table(s) are created in multisite, or what to do if you need one master table vs one table per site, and how to handle activation/removal across those sites.

K3davis 13:06, 13 December 2012 (UTC)

Charsets and Collation Missing

I highly recommend adding the charset/collate code from /wp-admin/include/schema.php It's 2014 and everything should be fine - just that it's not! Language based encoding can still be a big pain and point of frustration and I think it should be a best practice to add this little step when creating new tables. One thing less to worry about..

So maybe modify the sample code in the section Creating or Updating the Table like this:

global $wpdb;

// insert this code
$charset_collate = '';

if ( ! empty($wpdb->charset) )
  $charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset}";

if ( ! empty($wpdb->collate) )
  $charset_collate .= " COLLATE {$wpdb->collate}";

// continue
$sql = "CREATE TABLE $table_name (
// ...

// end with this line instead
){$charset_collate};";

Thanks for suggesting this; I just had this problem with my own plugin. I've just updated the examples.

-Jdgrimes 19:53, 24 July 2014 (UTC)

register_activation_hook doesn't fire from include file

I split my main plugin function.php in to separate includes. The register_activation_hook was placed in an included php file however I found that it doesn't fire on activation.

I left the function that it called inside the included php file but placed the register_activation_hook in the main function.php file and it seemed to work fine.