Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm working on a voting plugin for my site and I want to create 2 tables: one that stores votes and another that stores voter ips.

In Codex it suggests to use an if statement to see if the table has already been created when installing the plugin but how can I alter the code if I'm creating 2 tables?

This is my if statement in the plugin install function, currently set to check if 1 table already exists.

...

   $table_name1 = $wpdb->prefix . "voters_ip";
   $table_name2 = $wpdb->prefix . "vote_posts";
   $installed_ver = get_option( "postvote_version" );  

   if($wpdb->get_var("show tables like '$table_name'") != $table_name1) { //unsure how to add both tables

      $sql = "CREATE TABLE " . $table_name1 . " (
      id bigint(20) NOT NULL AUTO_INCREMENT,
      vote_post_id bigint(20) NOT NULL,
      voter_ip varchar(100) NOT NULL,
      UNIQUE KEY id (id)
    );";

      $sql = "CREATE TABLE " . $table_name2 . " (
      id bigint(20) NOT NULL AUTO_INCREMENT,
      vote_post_id bigint(20) NOT NULL,
      up int(11) NOT NULL,
      ddown int(11) NOT NULL,
      UNIQUE KEY id (id)
    );";

      require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
      dbDelta($sql);

      add_option("postvote_version", $postvote_version);
   }

....

What is the correct way to check if both tables exist?

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

Basic programming techniques you should have learned before building a plugin:

  1. You can concatenate checks with && (and) and || (or).
  2. You can (and should) guard each CREATE query with its own check

SQL syntax you should have looked into before writing queries on your own:

On a related note, please make sure that you delete these tables when the plugin is uninstalled/deleted.

PS: No offense intended, but it does look like you copy pasted without knowing what the code does. Please be aware that by doing this in a plugin you risk other people's installations!

share|improve this answer
1  
Thank you! No offence taken. I'm making the plugin for personal use on my website. Perhaps I'll share it when properly coded. –  at least three characters Nov 20 '10 at 16:08
    
As somebody arriving at this question from Google, I found this answer to be completely unhelpful. Downvoting. –  aendrew Feb 19 '12 at 23:47
    
That's not good. Can you elaborate on why you feel this way, please? Rereading my answer I did not get item 2. myself, so I rewrote it. Does this help? By the way, note that the answer relates to the question/problem the OP was really having (imho), not the question title. –  Raphael Feb 20 '12 at 7:51
add comment

I only took a cursory glance at your code, but you've at least three issues.

The first is security related:

CREATE TABLE " . $table_name1 . "

You never know what kind of garbage your function might receive, so better write it like so:

CREATE TABLE `" . str_replace('`', '', $table_name1) . "`

The second is SQL related and already highlighted:

CREATE TABLE

Should be:

CREATE TABLE IF NOT EXISTS

The last and most important is PHP syntax related. The second call to:

$sql =

Should be:

$sql .=

Else you'll never create the first table. Ever.

Lastly, as pointed out in a previous message, it's good practice to store the version of your plugin, or of its tables, in an option. This allows to upgrade it more easily when its own options, and its schema, change.

share|improve this answer
    
Very good annotations. And I could not have written it that simple and even with examples that compact. –  hakre Nov 20 '10 at 2:49
    
Thank you for pointing out the security issue, I'll definitely implement your code and using the statement in mysql is much better :-) –  at least three characters Nov 20 '10 at 16:15
    
BTW, for the second call to $sql = can I just use $sql2 = ? –  at least three characters Nov 20 '10 at 16:24
    
$sql2 works too, yes, if you call dbDelta twice. –  Denis Nov 20 '10 at 19:21
    
thx that's what I was doing –  at least three characters Nov 20 '10 at 20:37
add comment

If you're using unique table names, you can assume with a fair amount of confidence that table 2 exists if table 1 exists. So I would only check for the existence of 1 and, if it's there, skip the scripts ... if it's not, run the scripts.

You can also key off an option you set for your plug-in:

  • If the option exists (i.e. get_option('my-voting-version')) then your plug-in is installed and you don't need to run your database scripts.
  • If the option isn't there, run your scripts and set the option so you don't run them twice.

Even if the plug-in is removed, the option will remain so when it's reactivated you won't re-write the tables. Storing your database version and running scripts based on that also gives you the power of managing upgrades. You can then selectively run upgrade scripts based on what table version previously existed for the site.

share|improve this answer
    
I dislike this answer very much. First, it does not give a clear, good-style solution for creating an SQL table only if it is absent. Second, it assumes that tables and options are not removed when the plugin is deleted which is so bad style. –  Raphael Nov 19 '10 at 22:05
2  
It's not bad style at all, to leave the tables around. The deactivation hook gets fired when the plugin is deactivated and reactivated when trying to debug a site for instance. If you're using a membership plugin, or a voting plugin, the very last thing you want is to lose all of your data when you do that kind of stuff. The keyword is deactivate, not uninstall. –  Denis Nov 20 '10 at 2:04
    
I agree that tables/options should be removed when uninstalled/deleted - but this is not an automatic operation, it requires explicit action by the user. Removing tables/options whenever you deactivate a plug-in can have significant negative consequences - the first thing any developer will tell you do to if your site breaks is to deactivate all plug-ins. If this removed all of your data and customizations as well, it would be tragic! –  EAMann Nov 20 '10 at 4:06
    
Thank EAMann, although it may not be the best technique, I studied other plugins and as you said they check just 1 table. –  at least three characters Nov 20 '10 at 16:11
    
I never said to remove tables/options on deactivation. EAMann writes "Even if the plug-in is removed, the option will remain" -- I have to read that as keeping things after deinstallation. It is, by the way, not only good style to remove every trace of your plugin on deinstallation but absolutely necessary: how else can users get rid of mangled settings that break the plugin? See also: wordpress.stackexchange.com/questions/715/… –  Raphael Nov 20 '10 at 18:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.