Removing 2 records at a time

[dartiss]

Hi,

I have a very specific issue that I'm unable to work out a solution to. It's related to WordPress, but that's for context and isn't otherwise relevant.

I have a table named wp_options. It's used to store various option information using 2 fields - option_name and option_value. These are, as they suggest, a unique name for the option and a value for that option.

This table is also used to store "transients" - temporary option values. These are stored as 2 records:

Record 1 has the name format of _transient_timeout_xxx, where xxx is the unique name. The value contains an expiry time.

Record 2 has the name format of _transient_xxx, where xxx is the unique name used as per record 1. The value contains the temporary option value.

What I want to do is to remove any transient records which have expired. Basically, hunt down any "record 1's" which have expired and to delete that and the matching record 2. But I can't work out how to do this withing a single MySQL statement. Is anybody more capable able to think of something?

Any help will be gratefully appreciated.
David.

[Szabi Zsoldos]

What are your WHERE fields and values for the two tables ?

[dartiss]

It's just the one table, different records. The only fields are option_name and option_value

[Szabi Zsoldos]

Please paste a preview with some data from the table.

[cereal]

Try:

DELETE FROM wp_options WHERE option_name IN ('_transient_timeout_xxx','_transient_xxx');

If using PHP then:

$options = implode(',',array("'_transient_timeout_xxx'","'_transient_xxx'"));
mysql_query("DELETE FROM wp_options WHERE option_name IN ($options)") or die(mysql_error());

[dartiss]

SELECT option_name, option_value FROM `wp_options` WHERE option_name LIKE '_transient_%'

option_name option_value

_transient_timeout_gad_cache_5_776fa175e6cc472b8c7... 1367585413

_transient_gad_cache_5_776fa175e6cc472b8c7234b3095... a:75:{i:0;a:3:{s:4:"name";s:11:"ga:pagePath";s:5:"...

[dartiss]

Sorry, columns did align after submitting.

[dartiss]

Thanks cereal but the "xxx" in the name was just an example. It could be anything, and I want to clear down any record that matches that format, not just one specific one.

David.

[dartiss]

Essentially, I need some SQL to delete BOTH records (even though their key is slightly different) if the record with the timeout has expired.

[cereal]

Ok, try:

SELECT * FROM wp_options AS main, (SELECT SUBSTR(option_name,-3,3) AS string FROM wp_options WHERE STRCMP(option_name,'_transient_timeout_') = 1 AND option_value < unix_timestamp()) AS sub WHERE option_name IN (CONCAT('_transient_',sub.string),CONCAT('_transient_timeout_',sub.string));

If it works then just convert the main query to delete. In my example I'm using:

SUBSTR(option_name,-3,3)

to extract the last 3 characters which are used as sub.string in the CONCAT statements, but you can rearrange it as you prefer, depending on the length of your unique values... for more info check:

• http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring
• http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#function_strcmp

Anyway, it would be easier to store a third column as timestamp, that way you could avoid the second row:

CREATE TABLE `wp_options` (
  `option_name` varchar(255) DEFAULT NULL,
  `option_value` varchar(255) DEFAULT NULL,
  `option_timeout` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8

[dartiss]

Thanks Cereal. Unfortunately, it's a WordPress default set-up so the third column isn't an option. Ideally they wouldn't be sharing a table intended for another use ;)

Thanks for the code but it won't, at present work. Although I used 'xxx' as an example it's not 3 digits long - in fact, it's of a variable length. Is there a way around this?

David.

[cereal]

Yes, start to count from the beginning, for example for:

_transient_timeout_gad_cache_5_776fa175e6cc472b8c7

we start to count up to _transient_timeout_ which is 19 and use SUBSTR to take the rest:

select substr('_transient_timeout_gad_cache_5_776fa175e6cc472b8c7',20) as string;
+---------------------------------+
| string                          |
+---------------------------------+
| gad_cache_5_776fa175e6cc472b8c7 |
+---------------------------------+

[cereal]

Just to be clear, use: SUBSTR(option_name,20) instead of SUBSTR(option_name,-3,3)

[dartiss]

@cereal - just tried your suggestion and it works a treat. However, that's returning the 2 records that need deleting. Can I perform the actual deletion within the same statement?

David.

[cereal]

Yes you can, just change the first SELECT statement with a DELETE as this:

DELETE FROM wp_options, (SELECT SUBSTR(option_name,20) AS string FROM wp_options WHERE STRCMP(option_name,'_transient_timeout_') = 1 AND option_value < unix_timestamp()) AS sub WHERE option_name IN (CONCAT('_transient_',sub.string),CONCAT('_transient_timeout_',sub.string));

Bye!

[dartiss]

Thanks Cereal - greatly appreciated!