Complex SQL query, involving three tables and variable table names

Question

I have two different tables from which I need to pull data

blogs

which has the following column

blog_id

and another table which has a variable name, like

$blog_id . "_options

Which has the following columns:

option_id, option_name, option_value

For example:

option_id = 1, option_name='state', option_value='Texas'
option_id = 2, option_name='blog_name', option_value='My Blog'

And finally, I am provided with the following POST data

state

---

Here's what I need to do: Get the name of any blogs in the area of the POST data. To put it more succinctly, I need to select the option_value of option_name 'blog_name' on the same table that option_name='state' and option_value="$_POST['state']' and the table names are created from a list of blog_id's (from the table blogs) with '_options' appended to the end.

God I don't even know if what I am trying to do can be said with a human mouth.

Anyways, I figure stackoverflow is the place to ask, if anywhere.

Let me know if I can clarify anything for you, i will try.

By the way this is because I am using Wordpress MU and have opted to put some extra settings on the various blog's dynamically created tables.

Answer 1

I don't think you're doing this the right way.

Instead of using the table name _options, why not have something like

blog_options

Which contains the fields

• blog_id
• option_id
• option_name
• option_value

Then you can happily JOIN based on blog_id

Answer 2

If i get this correct, you are creating tables with variable names.

I don't think that that is a good idea.

Why don't you create just one table with a variable field in it that contains the variable name in type varchar? That field could contain the $blog_id.

Just like the other answer on your question.

Answer 3

You can do this in direct SQL but I think you'd be better off using built in wordpress functions so that if that DB structure changes at all (which it might since the MU and regular WP cores are set to be merged soon) you're still OK.

It sounds like you want to be able to pull info about other blogs from the active blog. I'd do it in two steps:

$blogs = get_blog_list(0,'all');
foreach($blogs as &$blog) {
    switch_to_blog($blog['id']);
    $blog['state'] = get_option('state');
    restore_current_blog();
}
restore_current_blog();

That'll give you a list of details for all active blogs on the MU install + the state field from the options table.

Yeah, its less than elegant, but its functional with little mess. If you need to use this info multiple times in a page load then use WP's object cache to store the variable for later use. There's also a myriad of ways you could either call this via ajax or web-service from the parent blog or implement a memcache solution so that this data can be centrally stored and managed if this becomes an issue, but I think if you use the object cache here with something like WP Super Cache on the front end you should be fine.