Database query that automatically gets current multisite table prefix?

I'm trying to create a plugin for use on our multisite install, but I can't quite figure out how to the db query in a way that will automatically get data from the correct subsite's db tables.

Here's a query that works:

$reportList = $wpdb->get_results("SELECT wp_2_watupro_taken_exams.ID,
			wp_2_watupro_taken_exams.exam_id,
			wp_2_watupro_taken_exams.user_id,
			wp_2_watupro_taken_exams.end_time 'completed',
			wp_2_watupro_master.name 'assessment',
			wp_users.display_name 'name',
			wp_users.user_email 'email'
			FROM wp_2_watupro_taken_exams
			JOIN wp_2_watupro_master ON wp_2_watupro_master.ID = wp_2_watupro_taken_exams.exam_id
			JOIN wp_users ON wp_users.ID = wp_2_watupro_taken_exams.user_id
			WHERE wp_users.user_email LIKE '%".$_POST['client']."%'
			ORDER BY wp_users.display_name;");

I want to make it so that I don't have the "wp_2_" in there -- i.e., so that it will just automatically get the results based on the right prefix for the current site.

I've done a bunch of searching and reading, and here's what I've got (which doesn't work):

$reportList = $wpdb->get_results($wpdb->prepare("SELECT $wpdb->watupro_taken_exams.ID,
			$wpdb->watupro_taken_exams.exam_id,
			$wpdb->watupro_taken_exams.user_id,
			$wpdb->watupro_taken_exams.end_time 'completed',
			$wpdb->watupro_master.name 'assessment',
			$wpdb->users.display_name 'name',
			$wpdb->users.user_email 'email'
			FROM $wpdb->watupro_taken_exams
			JOIN $wpdb->watupro_master ON $wpdb->watupro_master.ID = $wpdb->watupro_taken_exams.exam_id
			JOIN $wpdb->users ON $wpdb->users.ID = $wpdb->watupro_taken_exams.user_id
			WHERE $wpdb->users.user_email LIKE '%".$_POST['client']."%'
			ORDER BY $wpdb->users.display_name;"));

I've probably missed something simple... but I can't see it.

I'll try any suggestions.

Thanks,
Scott

EDIT: I had pasted the wrong code in the "this works" section above... corrected.

  • Adam Czajczyk

    Hello Scott,

    I hope you're well today and thank you for your question!

    You shouldn't use prefix in your code directly as it's defined in your site's "wp-config.php" file and may be changed. A user may install his site setting prefix e.g to "my_super_site_" and the code will then break.

    Taking that into account your second example seems to be fine and it should work "out of the box" on single WP install. To address tables with proper prefix, please consider following scenario:

    Assuming that the current site is of "wp_2_" prefix

    $wpdb->base_prefix

    will return "wp_"

    $wpdb->prefix

    will return "wp_2_"

    Please also make sure that you globalized the "$wpdb" first:

    global $wpdb;

    I hope that helps!
    Best regards,
    Adam

  • scottmcculloch

    Thanks Adam Czajczyk -- here's what I ended up with, and it works!

    $reportList = $wpdb->get_results($wpdb->prepare("SELECT ".$wpdb->prefix."watupro_taken_exams.ID,
    ".$wpdb->prefix."watupro_taken_exams.exam_id,
    ".$wpdb->prefix."watupro_taken_exams.user_id,
    ".$wpdb->prefix."watupro_taken_exams.end_time 'completed',
    ".$wpdb->prefix."watupro_master.name 'assessment',
    wp_users.display_name 'name',
    wp_users.user_email 'email'
    FROM ".$wpdb->prefix."watupro_taken_exams
    JOIN ".$wpdb->prefix."watupro_master ON ".$wpdb->prefix."watupro_master.ID = ".$wpdb->prefix."watupro_taken_exams.exam_id
    JOIN wp_users ON wp_users.ID = ".$wpdb->prefix."watupro_taken_exams.user_id
    WHERE wp_users.user_email LIKE '%".$_POST['client']."%'
    ORDER BY wp_users.display_name;"));

    Thanks!

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.