Title: Plugin Dev SQL Help Please
Last modified: August 19, 2016

---

# Plugin Dev SQL Help Please

 *  Resolved [Digital Raindrops](https://wordpress.org/support/users/adeptris/)
 * (@adeptris)
 * [16 years, 6 months ago](https://wordpress.org/support/topic/plugin-dev-sql-help-please/)
 * Hi Guys,
    I am creating a couple of plugins, they work ok and I am using the 
   WordPress update_option(key,value) and get_settings(key,value) while testing.
 * I want to emulate these functions with my own table and add a couple of fields
   plugin_name and theme_name, this is to enable clean up of the tables when a one
   of my plugins is deleted or a theme is deleted.
 * I copied the table structure for table wp_12_options and added my fields, the
   table is created and I can see the empty table and all the fields.
 * I created the function below to insert and update the records but is is not working,
   I am not an expert with mysql but I cannot see anything wrong with the SQL strings
   so a second set of eyes would help
 *     ```
       function drf_update_option($optionname, $optionvalue){
       	global $wpdb;
       	$drfoptionname = $optionname;
       	$drfoptiovalue - $optionvalue;
       	$drftablename = $wpdb->prefix . "drf_plugins";
       	$pluginname = 'style_switcher';
       	$themename = stripslashes(get_current_theme());
       	$drfdb = $wpdb->get_results("SELECT * FROM '$drftablename' WHERE  option_name = '$drfoptionname' ORDER BY id DESC", OBJECT);
       	if ($drfdb) {
       		$theid = $drfdb->id;
       		$updatedb = "UPDATE $drftablename SET option_value = '$drfoptionvalue', WHERE id='$theid'";
       		$results = $wpdb->query($updatedb);
       	} else {
       		$updatedb = "INSERT INTO $drftablename (blog_id, plugin_name, theme_name, option_name, option_value) VALUES (0,'$pluginname', $themename, '$drfoptionname', '$drfoptionvalue')";
       		$results = $wpdb->query($updatedb);
       	}
       }
       ```
   
 * Test insert
    `drf_update_option('supper', 'fish and chips');`
 * Thanks in advance
 * David

Viewing 3 replies - 1 through 3 (of 3 total)

 *  Thread Starter [Digital Raindrops](https://wordpress.org/support/users/adeptris/)
 * (@adeptris)
 * [16 years, 6 months ago](https://wordpress.org/support/topic/plugin-dev-sql-help-please/#post-1359553)
 * Just so you have the full picture here is the table structure!
 *     ```
       $table_name = $wpdb->prefix . "drf_plugins";
       if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
       $sql = "CREATE TABLE " . $table_name . " (
       	  id bigint(20) UNSIGNED NOT NULL auto_increment,
       	  blog_id int(12) DEFAULT '0' NOT NULL,
       	  plugin_name VARCHAR(64) NOT NULL DEFAULT '',
       	  theme_name VARCHAR(64) NOT NULL DEFAULT '',
       	  option_name VARCHAR(64) NOT NULL DEFAULT '',
       	  option_value longtext NOT NULL,
       	  PRIMARY KEY (id, blog_id, option_name),
       	  KEY option_name (option_name),
       	  KEY plugin_name (plugin_name),
       	  KEY theme_name (theme_name),
       	  DEFAULT CHARSET=utf8,
       	);";
       dbDelta($sql)
       ```
   
 *  Thread Starter [Digital Raindrops](https://wordpress.org/support/users/adeptris/)
 * (@adeptris)
 * [16 years, 6 months ago](https://wordpress.org/support/topic/plugin-dev-sql-help-please/#post-1359563)
 * Sorted it myself 🙂
 * Resolution changed the sql added {} to the variables tested and working fine 
   now custom options for plugin and theme.
 *     ```
       function drf_update_option($optionname, $optionvalue){
       	if ((!$optionname) || (!$optionvalue)) return;
       	global $wpdb;
       	$drftablename = $wpdb->prefix . "drf_plugins";
       	$pluginname = 'style_switcher';
       	$themename = stripslashes(get_current_theme());
       	$drfdb = $wpdb->get_results("SELECT * FROM {$drftablename} WHERE  option_name = '{$optionname}' ORDER BY id DESC", OBJECT);
       	if ($drfdb) {
       		$theid = $drfdb->id;
       		$results = $wpdb->query("UPDATE {$drftablename} SET option_value = '{$optionvalue}'  WHERE id='{$theid}'");
       	} else {
       		$results = $wpdb->query("INSERT INTO {$drftablename}
       				(blog_id, plugin_name, theme_name, option_name, option_value)
       				VALUES ('0', '{$pluginname}', '{$themename}', '{$optionname}', '{$optionvalue}')
       			");
       	}
       }
       ```
   
 * David
 * [project: style switcher plugin](http://www.digitalraindrops.net/Boards/Topic.aspx?TopicID=5)
 *  Thread Starter [Digital Raindrops](https://wordpress.org/support/users/adeptris/)
 * (@adeptris)
 * [16 years, 6 months ago](https://wordpress.org/support/topic/plugin-dev-sql-help-please/#post-1359580)
 * Corrected working Solution
 *     ```
       function drf_update_option($optionname, $optionvalue){
       	if ((!$optionname) || (!$optionvalue)) return;
       	global $wpdb;
       	$drftablename = $wpdb->prefix . "drf_plugins";
       	$pluginname = 'style_switcher';
       	$themename = stripslashes(get_current_theme());
       	$theid = $wpdb->get_var("SELECT id FROM {$drftablename} WHERE option_name = '{$optionname}'");
       	if ($theid) {
       		$wpdb->query("UPDATE {$drftablename} SET option_value = '{$optionvalue}'  WHERE id='{$theid}'");
       	} else {
       		$wpdb->query("INSERT INTO {$drftablename}
       			(blog_id, plugin_name, theme_name, option_name, option_value)
       			VALUES ('0', '{$pluginname}', '{$themename}', '{$optionname}', '{$optionvalue}')
       		");
       	}
       }
       ```
   

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘Plugin Dev SQL Help Please’ is closed to new replies.

## Tags

 * [code](https://wordpress.org/support/topic-tag/code/)
 * [sql](https://wordpress.org/support/topic-tag/sql/)

 * 3 replies
 * 1 participant
 * Last reply from: [Digital Raindrops](https://wordpress.org/support/users/adeptris/)
 * Last activity: [16 years, 6 months ago](https://wordpress.org/support/topic/plugin-dev-sql-help-please/#post-1359580)
 * Status: resolved

## Topics

### Topics with no replies

### Non-support topics

### Resolved topics

### Unresolved topics

### All topics