Create wordpress tables with unique index

Sometimes you wish to create tables with multiple unique indexes in wordpress or a tutorial on how to create them, but they are hard to find. Well, I don't give you a tutorial but an example of a database update script and simply leave you to fend for yourself.



Well, here I give you a script where you simply have to add your own sql table creation script.(Easiest way to get your own creation script if you're not fluent in mysql is to create the table layout you want in a tool like phpMyAdmin and export it and take the creation from the .sql file that is generated.)

To create an unique index add the CONSTRAINT UNIQUE INDEX STYLE_CLASS_NAME (name)
after your primary key creation.
Buildup

CONSTRAINT
This means you are telling SQL this is is a rule to which values entered into the rule you are defining need to adhere to this rule.

UNIQUE
This is pretty self explanitory, but it tells SQL that the values needs to be unique

INDEX
This tells SQL to make it in index. Indexes are what you use to speed up lookup of items/rows.

STYLE_CLASS_NAME
This is the name of the rule, not of the column the rule applies to. This Rule prefably is an unique name that also explains what it does and applies to. In my case you could figure out it applies to a style class name ;-)

(name)
This is the column name the rule applies to. This has to be the actual column name
Below my full sql database update script that you can place in the functions.php of your wordpress template or place it in your plugin. Feel free to do with it what you like, but I would appriciate a comment if you use it ;-)



 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
<?php
global $YOURVARIABLEPREFIX_db_version;
$YOURVARIABLEPREFIX_db_version = "1.0";// Update this value to execute the script. 
function JX_modules_install()  
 {
 global $wpdb;
 global $JX_db_version;
 require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
 // You can simply place the code without any explinatiory comments
 // in your sql string
 $table_name = $wpdb->prefix . "_YOURPREFIX_YOURTABLENAME";
 $sql = "CREATE TABLE $table_name (
   id int(11) NOT NULL AUTO_INCREMENT,
   active int(11) NOT NULL,
   name tinytext NOT NULL,
   text text NOT NULL,
   UNIQUE KEY id (id)
   );";
 $res = dbDelta( $sql );
 echo "Update $table_name.<BR/>" . implode('<BR/> - ',$res) . '<BR/><BR/>';
 
 $table_name = $wpdb->prefix . "_YOURPREFIX_YOUROTHERTABLENAME";
 // Or you can place comments by using the mighty dot...
 $sql = "CREATE TABLE $table_name (
   id int(11) NOT NULL AUTO_INCREMENT,".// You can place comments
   "name varchar(255) NOT NULL,". // On why you are
   "something text NULL,". // Using certain talbes
   "content text NOT NULL,". // This way for future reference....
   "UNIQUE KEY id (id),". // ID as primary key, gotta love integers
   "CONSTRAINT UNIQUE INDEX STYLE_CLASS_NAME (name)".//try to create a nice table index for the names so no duplicates can be inserted.
   ");";
 $res = dbDelta( $sql );
 echo "Update $table_name.<BR/>" . implode('<BR/> - ',$res) . '<BR/><BR/>';
 update_option( "YOURVARIABLEPREFIX_db_version", $YOURVARIABLEPREFIX_db_version );
 }
function JX_install_fmv_theme()
 {
 global $YOURVARIABLEPREFIX_db_version;
 if(get_option('YOURVARIABLEPREFIX_db_version') != $YOURVARIABLEPREFIX_db_version)
  {  
  echo '<div id="message" class="updated below-h2"><p>The database for the WHAT EVER YOUR PLUGIN/TEMPLATE NAME IS need to be updated from version ';
  echo get_option('YOURVARIABLEPREFIX_db_version');
  echo ' to version '. $YOURVARIABLEPREFIX_db_version .'<BR/><BR/>';
  echo 'Installing database updates & settings<BR/>';
  JX_modules_install();
  echo '<BR/>Update complete. </p></div>';
  }
 }
// This is to make sure this gets executed.
// You can also attach this to a hook if you like.
JX_install_fmv_theme();
?>

Comments

Post a Comment

Popular Posts