Access Tables with number prefix

Question

I have multiple sites setup within my site. I need to access all the various options tables which are named 'wp_#_options'. How can I select these tables that have a number prefix?

Using

$wpdb->get_results($wpdb->prepare("SELECT * FROM ".$wpdb->7_options." WHERE ....."));

gives me a parse error.

Answer 1

I think there are a couple of ways to what you are doing based on your setup. If you just want to add the table prefix to the code this could work.

"SELECT * FROM %s", "{$wpdb->prefix}options" WHERE...

The $wpdb->prefix part uses the default WP table prefix from the main config file. You could create your own variable using information from another source if available.

Basic example...

$table = "wp_7";
"SELECT * FROM %s", "{$table}_options" WHERE...

I've not tried this though so can't be entirely sure it's correct. Hope it helps!

Answer 2

When in multisite, the wpdb prefix is always the prefix required to access the tables for the current site. This includes the site ID

so this should work:

$wpdb->get_results($wpdb->prepare("SELECT * FROM ".$wpdb->prefix."options WHERE ....."));

There should be no difference at all when using multisite, just make your queries as if it were single site.