2

I'm having trouble passing a table variable to $wpdb->prepare(); Here is functioning code:

$table = $wpdb->get_blog_prefix( $blog_id ) . 'my_table';

$voters = $wpdb->get_row($wpdb->prepare("SELECT * FROM $table WHERE user_id= %d AND post_id = %d;", $user_ID, $post->ID  ));

This works great. However I think I should also be including my table in the prepare statement. But, it breaks when I change it to this:

$table = $wpdb->get_blog_prefix( $blog_id ) . 'my_table';

$voters = $wpdb->get_row($wpdb->prepare("SELECT * FROM %s WHERE user_id= %d AND post_id = %d;", $table, $user_ID, $post->ID  ));

Any ideas on why it might be breaking?

2 Answers 2

3

The prepare() method escapes %s. The second piece of code you listed breaks because quotation marks are added to the table name, hence it doesn't match what's in the DB.

The first piece of code works because it's a straight string replacement hence matching the name of the table in the database.

What is the error message you are getting?

HTH

1
  • For anyone's future reference, it appears I was wrong. In keeping with the examples in the codex it appears that I do NOT need to include the table in the prepare statement. Also @rxn is correct as to why this wasn't working. Aug 14, 2011 at 16:03
0

you need to register the table with the $wpdb class so that you could use something like:

$table = $_POST['table'];
$res = $wpdb->get_results($wpdb->prepare("
SELECT * FROM {$wpdb->my_custom_table}
WHERE user_id= %d AND post_id = %d;",
array($user_ID, $post->ID  )
);

Here's a link to nice function, that when called in init will create your table if it doesn't exist, but will always register it under what ever name you like:

http://snipplr.com/view/52178/

This is the important code you need in that function, (edited a bit my me) for those that are interested in just that:

//include the wordpress db functions
require_once(ABSPATH . 'wp-admin/upgrade-functions.php');
dbDelta($sql);

    if (!isset($wpdb->custom)) $wpdb->custom = new stdClass();
    //add the shortcut so you can use $wpdb->custom->my_table
    $wpdb->custom->$search_table = $search_table;
    $wpdb->tables[] = str_replace($wpdb->prefix, '', $search_table);
1
  • You wouldn't want to add to the $wbdb object. A) It makes no difference to not retrieving it from the object and B) you're filling something that doesn't belong there into a global WP object.
    – kaiser
    Nov 16, 2012 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.