Support » Fixing WordPress » wpdb and wildcards! need help almost done with project!

  • Resolved Mort

    (@rugbert)


    SO, Im building a custom search for my first WP and after a lot of bumps I finally have it almost working. I was using query_posts to try and filter my posts but had to move on to wpdb in order to meet all search criteria.

    Its almost working, I was able to filter posts based on multiple fields and correctly compare numbers but now have the problem where I cant submit a query that has a wildcard in it.
    lets just say $searchUtils = ‘%Water%’;
    Heres my code:

    $postids=$wpdb->get_col($wpdb->prepare("
    SELECT DISTINCT post_id
    FROM   wp_postmeta
    WHERE  post_id IN (SELECT post_id
                       FROM   wp_postmeta
                       WHERE  ( meta_key = 'Price'
    AND meta_value BETWEEN 1 AND $searchPrice ))
    	   AND post_id IN (SELECT post_id
    				   FROM   wp_postmeta
    				   WHERE  ( meta_key = 'Bedrooms'
    				AND meta_value = $searchBeds ))
           AND post_id IN (SELECT post_id
                           FROM   wp_postmeta
                           WHERE  ( meta_key = 'Area'
                                    AND meta_value = '$searchArea' ))
           AND post_id IN (SELECT post_id
                           FROM   wp_postmeta
                           WHERE  ( meta_key = 'Utilities'
                                    AND meta_value = '$searchUtils' ))
     ", $searchPrice, $searchBeds, $searchArea)); 
    
    if (!empty($postids)) {
       foreach ($postids as $id) {
        $post=get_post(intval($id));
        setup_postdata($post);

    which then displays all posts where the criteria is met. I built the query based on this example but then this part later in the page explains that wpdb->prepare is for protection for sql injections so I guess thats why my wild cards arent working.

    I tried changing prepare to query but then I dont get ANY search results back. Ive tested out tons of queries manually with mysql in terminal so I know the stuff Im searching for exists but I dont know how to make it work from WP.

    This is the last thing I need to finish this site so I reaaalllly need some help.

Viewing 5 replies - 1 through 5 (of 5 total)
  • http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

    See the link above for examples of using LIKE for wildcard searching..

    Mort

    (@rugbert)

    OMFG Im an IDIOT. I had already found the answer (the %%) but I dont know why I took the LIKE out and put an equal sign there. UGH thanks

    You’re welcome, have made the same mistake myself… 😉

    Mort

    (@rugbert)

    actually, I cant get using * to work. I switched it up so if a user selects “Any” from a drop down, it turns the variable into a ‘*’ so that way the query would in theory select everything from the column. Butttt it doesnt work. I also tried %% but no go.

    also – Im not using LIKE for this particular part. Any suggestions?

    Can you give an example of the problem area?

    Does the same problem query get data if you run the query manually, eg. in phpmyadmin or whatever..

    I tend to work out bigger queries in phpmyadmin first then move them into the $wpdb method after, i’ve yet to find a query that doesn’t give matching results between the two.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘wpdb and wildcards! need help almost done with project!’ is closed to new replies.