Plugin Dev SQL Help Please

  • Resolved Digital Raindrops

    (@adeptris)


    16 years, 6 months ago

    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

    (@adeptris)

    16 years, 6 months ago

    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

    (@adeptris)

    16 years, 6 months ago

    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

    Thread Starter Digital Raindrops

    (@adeptris)

    16 years, 6 months ago

    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