WordPress.org

Forums

[resolved] wpdb and wildcards! need help almost done with project! (6 posts)

  1. Kyle Jennings
    Member
    Posted 4 years ago #

    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.

  2. Mark / t31os
    Moderator
    Posted 4 years ago #

    http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

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

  3. Kyle Jennings
    Member
    Posted 4 years ago #

    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

  4. Mark / t31os
    Moderator
    Posted 4 years ago #

    You're welcome, have made the same mistake myself... ;)

  5. Kyle Jennings
    Member
    Posted 4 years ago #

    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?

  6. Mark / t31os
    Moderator
    Posted 4 years ago #

    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.

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.