Support » Fixing WordPress » query fails, returns 0.

  • I’m working on a search function which should search the database for the keywords a user enters in a textfield. For some reason I can’t get this query to work, it keeps returning 0.

    Also the escaping of LIKE statement doesnt seem to work either. My guess is that the function is deprecated as I get this back.

    Fatal error: Call to undefined method wpdb::esc_like()

    // Place the search keywords in array
    $keywords = explode(' ', $_POST['keywords']);
    
    $where = '';
    foreach($keywords as $keyword) {
       // $keyword = esc_like($keyword); - function deprecated?
       $where .= " post_title LIKE '%" . $keyword . "%' OR post_content LIKE '%" . $keyword . "%' OR";
    }
    
    // Remove last 'OR'
    $where = substr($where, 0, -2);
    
    // The sql
    $sql = "
            SELECT * FROM wp_posts
    	WHERE (" . $where . ")
    	AND post_type = '%s'
    	AND post_status = '%s'
    	ORDER BY post_date DESC
    ";
    
    $query = $wpdb->query(
         $wpdb->prepare($sql, array('post', 'publish'))
    );
    
    print_r($query);

    Any help is appreciated

Viewing 6 replies - 1 through 6 (of 6 total)
  • I think the problem is in the use of the single ‘%’ signs in creating the where clause. I believe that $wpdb->prepare() is trying to use these to insert parameters. Try using this:

    $where = '';
    foreach($keywords as $keyword) {
       // $keyword = esc_like($keyword); - function deprecated?
       $where .= " post_title LIKE '%%" . $keyword . "%%' OR post_content LIKE '%%" . $keyword . "%%' OR";
    }
    Moderator keesiemeijer

    (@keesiemeijer)

    From WordPress 4.0 the like_escape() function is deprecated and is replaced by the the esc_like() method:

    global $wpdb;
    $escaped_string = $wpdb->esc_like( $like_string_to_escape );

    @vtxyzzy, yes you are correct. I actually changed it %%% %% like below, and it works.

    $where .= " post_title LIKE '%%%" . $keyword . "%%' OR post_content LIKE '%%%" . $keyword . "%%' OR";

    @keesiemeijer, so since I’m running 3.9.2 I should use the like_escape() function for the LIKE statement? Doesnt it get properly sanitized within the prepare() function?

    Moderator keesiemeijer

    (@keesiemeijer)

    Doesnt it get properly sanitized within the prepare() function?

    You should only use it for the LIKE text before escaping the sql with esc_sql() or prepare().

    like_escape() and $wpdb->esc_like() escape the characters % (percent) and _ (underscore), as they have special meaning in LIKE statements. The output from these functions is not sql safe by itself.

    $wpdb->prepare() is generally preferred over esc_sql() because it corrects a few common formatting errors.

    Here is a part of the comments for the $wpdb->esc_like() method in wp-includes/wp-db.php

    /**
     * First half of escaping for LIKE special characters % and _ before preparing for MySQL.
     *
     * Use this only before wpdb::prepare() or esc_sql().  Reversing the order is very bad for security.
     *
     * Example Prepared Statement:
     *  $wild = '%';
     *  $find = 'only 43% of planets';
     *  $like = $wild . $wpdb->esc_like( $find ) . $wild;
     *  $sql  = $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE post_content LIKE %s", $like );
     *
     * Example Escape Chain:
     *  $sql  = esc_sql( $wpdb->esc_like( $input ) );
     *
     * ...
     */

    http://codex.wordpress.org/Class_Reference/wpdb/esc_like
    https://developer.wordpress.org/reference/classes/wpdb/esc_like/

    Use $wpdb->esc_like() if the method exists (from WordPress 4.0 and up), else use like_escape().

    global $wpdb;
    
    // if else
    if ( method_exists( $wpdb, 'esc_like' ) ) {
    	$keyword =  $wpdb->esc_like( $keyword );
    } else {
    	$keyword = like_escape( $keyword );
    }
    
    // The same with the ternary operator
    $keyword = method_exists( $wpdb, 'esc_like' ) ? $wpdb->esc_like( $keyword ) : like_escape( $keyword );

    Moderator keesiemeijer

    (@keesiemeijer)

    Here is an example how you could use it in your code:

    global $wpdb;
    
    $keywords = $results = array();
    $like = '';
    
    // check if $_POST index 'keywords' exists
    if ( isset( $_POST['keywords'] ) && $_POST['keywords'] ) {
    
    	// Sanitize (strip tags etc.) and place the search keywords in array
    	$keywords = (  explode( ' ', sanitize_text_field( $_POST['keywords'] ) ) );
    }
    
    // no need to use $wpdb->prepare for this part of the query
    $where = "SELECT * FROM $wpdb->posts WHERE post_type = 'post' AND post_status = 'publish'";
    
    if ( !empty( $keywords ) ) {
    
    	foreach ( $keywords as $keyword ) {
    
    		// check if new WP 4.0 method esc_like() exists
    		$keyword = method_exists( $wpdb, 'esc_like' ) ? $wpdb->esc_like( $keyword ) : like_escape( $keyword );
    		$keyword = '%' . $keyword . '%';
    
    		// prepare the like statement for the current keyword
    		$like_statement = $wpdb->prepare( " post_title LIKE %s OR post_content LIKE %s OR", $keyword, $keyword );
    		$like .= $like_statement;
    	}
    
    	// create the full like statenement and remove last 'OR'
    	$like  = ' AND (' . substr( $like , 0, -2 ) . ')';
    
    	// get posts
    	$results = $wpdb->get_results( $where . $like . ' ORDER BY post_date DESC' );
    }
    
    if ( !empty( $results ) ) {
    
    	// do something with results
    	var_dump( $results );
    
    } else {
    	echo 'no posts found';
    }

    Moderator keesiemeijer

    (@keesiemeijer)

    And as a final note, have you tried it with a WP_Query and the search parameter ‘s’.
    http://codex.wordpress.org/Function_Reference/WP_Query#Search_Parameter

    I think WordPress searches in the post_title and post_content as of version 3.7

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘query fails, returns 0.’ is closed to new replies.