Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

When I do this:

$transients = $wpdb->get_col(
    "
    SELECT     option_name
    FROM       $wpdb->options
    WHERE      option_name
    LIKE       '_transient_wb_tt_%'
    "
);

It works fine, but when I use prepare like so:

$transients = $wpdb->get_col( $wpdb->prepare(
    "
    SELECT     option_name
    FROM       %s
    WHERE      option_name
    LIKE       '_transient_wb_tt_%'
    ",
    $wpdb->options
) );

It doesn't work, what am I doing wrong here?

share|improve this question
    
why are you using prepare here? –  Bainternet Jan 24 '13 at 19:41
    
I was thinking as good practice to prepare everything but after looking over the examples maybe it's just silly? –  wickedbroccoli Jan 24 '13 at 19:45
    
Prepare is used to escape and sanitize user input which is not really your case here. –  Bainternet Jan 24 '13 at 19:53
add comment

1 Answer

up vote 1 down vote accepted

I agree with @bainternet. You don't need $wpdb->prepare. There isn't any user supplied content.

The answer to the question is that to get a wildcard % to pass through prepare you need to double it in your code.

LIKE  '_transient_wb_tt_%%'

Try that or this if you want a good look at the generated query:

var_dump($wpdb->prepare("
    SELECT     option_name
    FROM       %s
    WHERE      option_name
    LIKE       '_transient_wb_tt_%%'
    ",
    'abc')); 
die;

Other than being unnecessary, using $wpdb->prepare like this won't work. The attempt to use prepare to swap in the tablename will result in a tablename with quotes around it. That is invalid SQL. The query should be simple:

SELECT     option_name
FROM       {$wpdb->options}
WHERE      option_name
LIKE       '_transient_wb_tt_%%'
share|improve this answer
    
I just spotted this and tried it before but it is now spitting out - WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''wp_options' WHERE option_name LIKE '_transient_wb_tt_%'' at line 2] SELECT option_name FROM 'wp_options' WHERE option_name LIKE '_transient_wb_tt_%' –  wickedbroccoli Jan 24 '13 at 19:57
    
var_dump output - string(133) " SELECT option_name FROM 'wp_options' WHERE option_name LIKE '_transient_wb_tt_%' " Array ( ) –  wickedbroccoli Jan 24 '13 at 20:01
    
You shouldn't have quotes around the tablename. I just noticed that you were using prepare to swap in the the tablename. Don't do that. It won't work. $wpdb->options is a safe value. Just write it into the query directly. –  s_ha_dum Jan 24 '13 at 20:04
    
Ah, ok. Thank you. –  wickedbroccoli Jan 24 '13 at 20:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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