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.

  • slug=$s must be slug=%s and LIKE $s% must be LIKE %s. – fuxia 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. – fuxia 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. – fuxia May 26 '13 at 10:47
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 */
  • 1
    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'%. – fuxia 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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