$wpdb->prefix DB selection

Quick question, not had any problems with MultiDB and plugins or themes so far until now. Been debugging the SupportPress theme with WooThemes to try and find a solution, but the theme was/is having issues pulling data from two databases with the following function:

$watchers = $wpdb->get_col("SELECT DISTINCT user_email
FROM ".$wpdb->prefix."supportpress_watching_tickets as watch
LEFT JOIN $wpdb->users ON watch.user_id = $wpdb->users.ID
WHERE watch.item_id = $item_id;");

When this runs it pulls the data from the site’s DB, but doesn’t look in the global users table for the users. Instead it tries to pull data from the current site’s DB with a wp_siteid_ prefixed user table. We’re at a loss, so I just hardcoded the proper query for now. Was just wondering if there was some special way of calling from two separate DB’s when using MultiDB or if it should just do it automatically? I assumed it was all done by MultiDB but this one is strange. Not willing to go to a Dev to fix as WooThemes themselves can’t fix it and hardcoding it has fixed it for now, be nice to get some closure on it if possible though.

  • aecnu
    • WP Unicorn

    Greetings Ed Cooper,

    Thank you for bringing this issue to our attention and I must say it is intriguing.

    From the information provided by you it appears that this particular theme is not Multi DB compatible out of the box, and I commend you on your hard coded fix which is in the theme itself?

    Would you consider posting that fix?

    If it is the case that it is in the theme then we know the answer as to where the problem is at and I recommend you FTP in and permission the modified file 444 read only so that it is not over written during a future update.

    Please advise as to where the hard coding took place.

    Cheers, Joe

  • PrimaryT
    • Site Builder, Child of Zeus

    Hi Joe,

    Indeed, the change is in includes/theme-emails.php in the function function woo_supportpress_get_item_watchers( $item_id ){ on line 92.

    Changed from:

    $watchers = $wpdb->get_col("SELECT DISTINCT user_email
    FROM ".$wpdb->prefix."supportpress_watching_tickets as watch
    LEFT JOIN $wpdb->users ON watch.user_id = $wpdb->users.ID
    WHERE watch.item_id = $item_id;");

    To:

    $watchers = $wpdb->get_col("SELECT user_email
    FROM the_site_id's_db.wp_siteid_supportpress_watching_tickets as watch
    LEFT JOIN the_site_id's_db.wp_users ON watch.user_id = wp_users.ID
    WHERE watch.item_id = $item_id;");

    Obviously changing the relevant fields for security.

    I had wondered if it was a MultiSite issue, but the developer has tried it on his single DB WPMS install and it seems to be behaving correctly. It’s just a matter of getting confused about where the DB’s are located. Have you come across anything similar? Reading other thread’s, I might try moving the site to the global DB and test that.

    Good call on the 444, forgot about that.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.