0

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.

6
  • slug=$s must be slug=%s and LIKE $s% must be LIKE %s.
    – fuxia
    May 26, 2013 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, 2013 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.
    – fuxia
    May 26, 2013 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, 2013 at 10:43
  • Again: you cannot use a plain % in that string.
    – fuxia
    May 26, 2013 at 10:47

1 Answer 1

1

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 */
3
  • 1
    Or use %% to get a literal.
    – t31os
    May 27, 2013 at 10:36
  • @t31os prepare() add quotes around the value, so the result would be 'value'%.
    – fuxia
    May 27, 2013 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, 2013 at 10:53

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.