1

This query does what I need, return a list of data from a widget in several tables from a Wordpress Multi Site database.

There must be an easier way to do this. I have 30 tables I need to include, how can I get some type of loop to just return option value from all wp_n_option tables?

SELECT option_value
FROM `wp_options`
WHERE option_name = 'widget_thin_search'
UNION
SELECT option_value
FROM `wp_3_options`
WHERE option_name = 'widget_thin_search'
UNION
SELECT option_value
FROM `wp_4_options`
WHERE option_name = 'widget_thins_search'
INTO OUTFILE '/tmp/result.csv'

Edit: As Brandon pointed out, if it was a static 30 tables, I could build the query. However, the tables will increase as time goes on.

Ivan
  • 89
  • 9

1 Answers1

2

You could create a table with one column containing table names. Then create a T-SQL proc to loop through those table names and construct a query string resembling what you have in your example. Then run that query string with the exec command.

Just note that UNION removes duplicates whereas UNION ALL does not. That may not be an issue for you but I just wanted to point it out.

Brandon Moore
  • 8,590
  • 15
  • 65
  • 120
  • But is this for 30 tables that aren't going to change (i.e. you won't be adding more tables or removing any)? If so I would say just take the 10 minutes to finish the query the way you were doing it and get it done. You'd spend just as much or more time creating the proc. – Brandon Moore Nov 21 '11 at 01:57
  • The 30 tables will increase as time progresses. An automated solution would be best. I was thinking of doing some sort of nested query in the FROM clause that would select all wp_n_options tables but I can't figure it out. – Ivan Nov 21 '11 at 02:14
  • Do you have to use pure TSQL or could you utilize a programming language like C#? TSQL is like an archaic language trying to remain backwards compatible for the last 2 decades. If you need to use TSQL then put something like "select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'wp_%'" into a cursor and then loop over the results to make your query. – Brandon Moore Nov 21 '11 at 02:22
  • I am open to using anything as long as it works! I just asked regarding SQL because that's what I know. – Ivan Nov 21 '11 at 04:47
  • Gotcha. Well TSQL will probably be faster than learning a new language then :) – Brandon Moore Nov 21 '11 at 04:59