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.

I have problem with wpdb prepare and sql query. If i run the query directly thru wpdb->get_results it's working fine. Here it is:

$alabala_sql = $wpdb->prepare(
    "
    SELECT * 
    FROM $wpdb->posts p 
    INNER JOIN $wpdb->term_relationships tr 
      ON p.ID = tr.object_id
    INNER JOIN $wpdb->term_taxonomy tt 
      ON tr.term_taxonomy_id=tt.term_taxonomy_id 
    inner join  $wpdb->terms t 
      on t.term_id=tt.term_id and t.slug='$curPageId'
    WHERE 
      p.post_title LIKE '$getChar%'        
      AND p.post_type = 'filmi-i-serialii' 
      AND p.post_status = 'publish';
    "
);
/*Make sql query to take posts*/
$eventsPosts = $wpdb->get_results($alabala_sql);

But it doesn't work. Also i tried that way:

$alabala_sql = $wpdb->prepare(
    "
    SELECT * 
    FROM $wpdb->posts p 
    INNER JOIN $wpdb->term_relationships tr 
      ON p.ID = tr.object_id
    INNER JOIN $wpdb->term_taxonomy tt 
      ON tr.term_taxonomy_id=tt.term_taxonomy_id 
    inner join  $wpdb->terms t 
      on t.term_id=tt.term_id and t.slug=$s
    WHERE 
      p.post_title LIKE $s%        
      AND p.post_type = 'filmi-i-serialii' 
      AND p.post_status = 'publish';
    ", $curPageId, $getChar
);
/*Make sql query to take posts*/
$eventsPosts = $wpdb->get_results($alabala_sql);

But again it didn't worked. Please tell me where is my mistake ? Both vars are strings. Regards.

share|improve this question
    
slug=$s must be slug=%s and LIKE $s% must be LIKE %s. –  toscho May 26 '13 at 10:23
    
That way:SELECT * FROM $wpdb->posts p INNER JOIN $wpdb->term_relationships tr ON p.ID = tr.object_id INNER JOIN $wpdb->term_taxonomy tt ON tr.term_taxonomy_id=tt.term_taxonomy_id inner join $wpdb->terms t on t.term_id=tt.term_id and t.slug=%s WHERE p.post_title LIKE %s% AND p.post_type = 'filmi-i-serialii' AND p.post_status = 'publish'; ", $curPageId, $getChar Didn't work. –  Devoted May 26 '13 at 10:29
    
Of course it didn’t. Lookup how sprintf() works. You cannot use a plain % in a string evaluated by sprintf. Use $getChar . '%' instead. –  toscho May 26 '13 at 10:33
    
Well. I'm not sure i get it but like this: SELECT * FROM $wpdb->posts p INNER JOIN $wpdb->term_relationships tr ON p.ID = tr.object_id INNER JOIN $wpdb->term_taxonomy tt ON tr.term_taxonomy_id=tt.term_taxonomy_id inner join $wpdb->terms t on t.term_id=tt.term_id and t.slug=$curPageId WHERE p.post_title LIKE $getChar. '%' AND p.post_type = 'filmi-i-serialii' AND p.post_status = 'publish'; ",$curPageId, $getChar It doesnt worked again. –  Devoted May 26 '13 at 10:43
    
Again: you cannot use a plain % in that string. –  toscho May 26 '13 at 10:47
show 1 more comment

1 Answer

up vote 1 down vote accepted

To get a % character into your SQL string without confusing sprintf() add it to the replacement string:

$alabala_sql = $wpdb->prepare(
    "
    SELECT *
    FROM $wpdb->posts p
    INNER JOIN $wpdb->term_relationships tr
        ON p.ID = tr.object_id
    INNER JOIN $wpdb->term_taxonomy tt
        ON tr.term_taxonomy_id=tt.term_taxonomy_id
    INNER JOIN  $wpdb->terms t
        ON t.term_id=tt.term_id and t.slug= %s
    WHERE
        p.post_title LIKE %s
        AND p.post_type = 'filmi-i-serialii'
        AND p.post_status = 'publish';
    ", 
    $curPageId, 
    $getChar . '%'
);           /* ^ like this */
share|improve this answer
    
Ahh... thank you :) –  Devoted May 26 '13 at 10:50
    
Or use %% to get a literal. –  t31os May 27 '13 at 10:36
    
@t31os prepare() add quotes around the value, so the result would be 'value'%. –  toscho May 27 '13 at 10:38
    
Actually, prepare doesn't quote the value when you use a literal. So LIKE '%%%s' would work just fine. It would be a little confusing to read that back to yourself later though(and you might easily forget why that one instance is written that way). –  t31os May 27 '13 at 10:53
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.