CODEWITHSUNDEEP
phpmysqlwordpress
Peter
Peter

Reputation: 11835

Custom query in posts_search

How can I use this query as my custom search query?

add_filter('posts_search', 'my_search_is_perfect', 20, 2);
function my_search_is_perfect($search, $wp_query) 
{
   $sWord = 'Zukunft haus';

   return "
       SELECT *, 
              MATCH(post_title) AGAINST('$sWord' IN BOOLEAN MODE) AS Score 
        FROM `wp_posts` 
       INNER JOIN wp_term_relationships ON wp_term_relationships.object_id = ID
             AND wp_term_relationships.term_taxonomy_id = 1
       WHERE MATCH( post_title) AGAINST ('$sWord' IN BOOLEAN MODE) 
         AND `post_status` = 'publish'
         AND `post_type` = 'post'
       ORDER BY score DESC
  "; 
}

The query is correct (I checked this in phpMyAdmin) but in WordPress I get the message, no results.

Upvotes: 8

Views: 5235

Answers (4)

KalenGi
KalenGi

Reputation: 2077

It looks like your intention is to completely disregard WordPress' query and fetch posts using your own custom query. In this case posts_search is the wrong filter to use. The purpose of posts_search is for you to insert additional search clauses into the the $search parameter: https://developer.wordpress.org/reference/hooks/posts_search/

The correct filter to use to perform a customized posts fetch is posts_pre_query. It gives you the opportunity to fetch the posts and return them: https://developer.wordpress.org/reference/hooks/posts_pre_query/. A good example of this is Paginating results from posts_pre_query - Wordpress.

And here posts_pre_query is used to completely block fetching of posts: https://wordpress.stackexchange.com/a/354103/657

The answer by @Juancho Ramone is perfect, only that the filter in use should be posts_pre_query:

function my_search_is_perfect($posts, &$query) 
{
    global $wpdb;

    $sWord = 'Zukunft haus';

    $query = "SELECT *, 
                  MATCH(post_title) AGAINST('$sWord' IN BOOLEAN MODE) AS Score 
            FROM `".$wpdb->prefix."_posts` 
           INNER JOIN ".$wpdb->prefix."_term_relationships ON ".$wpdb->prefix."_term_relationships.object_id = ID
                 AND ".$wpdb->prefix."_term_relationships.term_taxonomy_id = 1
           WHERE MATCH( post_title) AGAINST ('$sWord' IN BOOLEAN MODE) 
             AND `post_status` = 'publish'
             AND `post_type` = 'post'
           ORDER BY score DESC";

    $myrows = $wpdb->get_results( $query );

    return $myrows;
}
add_filter('posts_pre_query', 'my_search_is_perfect', 20, 2);

Upvotes: 0

Atul Jindal
Atul Jindal

Reputation: 976

Custom queries in wordpress are executed via $wpdb. If you use simply mysql query filters in wordpress may not execute the queries to avoid SQL injections. So use $wpdb to create custom queries.

Upvotes: 0

Dr.Tricker
Dr.Tricker

Reputation: 553

In function.php file:

add_filter('posts_search', 'my_search_is_perfect', 20, 2);
function my_search_is_perfect() 
{
    global $post;
    global $wpdb;
    $sWord = 'Zukunft haus';

    $sel_query = "SELECT *, 
                          MATCH(post_title) AGAINST('$sWord' IN BOOLEAN MODE) AS Score 
                    FROM ".$wpdb->prefix."posts 
                   INNER JOIN ".$wpdb->prefix."term_relationships ON ".$wpdb->prefix."term_relationships.object_id = ID
                         AND ".$wpdb->prefix."term_relationships.term_taxonomy_id = 1
                   WHERE MATCH( post_title) AGAINST ('$sWord' IN BOOLEAN MODE) 
                     AND post_status = 'publish'
                     AND post_type = 'post'
                   ORDER BY score DESC";
    $totaldata = $wpdb->get_results($sel_query);

    return $totaldata;
}

Upvotes: 5

Juan Girini
Juan Girini

Reputation: 1168

As suggested by @Gustavo Straube, it would be better to use $wpdb in which case you should implement it like this:

add_filter('posts_search', 'my_search_is_perfect', 20, 2);

function my_search_is_perfect() 
{
    global $wpdb;

    $sWord = 'Zukunft haus';

    $query = "SELECT *, 
                  MATCH(post_title) AGAINST('$sWord' IN BOOLEAN MODE) AS Score 
            FROM `".$wpdb->prefix."_posts` 
           INNER JOIN ".$wpdb->prefix."_term_relationships ON ".$wpdb->prefix."_term_relationships.object_id = ID
                 AND ".$wpdb->prefix."_term_relationships.term_taxonomy_id = 1
           WHERE MATCH( post_title) AGAINST ('$sWord' IN BOOLEAN MODE) 
             AND `post_status` = 'publish'
             AND `post_type` = 'post'
           ORDER BY score DESC";

    $myrows = $wpdb->get_results( $query );

    return $myrows;
}

You can find more information in https://codex.wordpress.org/Class_Reference/wpdb

Upvotes: 0

Related Questions