problems in create advanced search using query

Question

I have a problem in creating advanced search with custom query and using $wpdb->get_results($query , OBJECT);

In Normal search in wordpress when we search xxx yyyy or search yyyy xxx we have same results and it's good. But when I am forced to use query to create an advanced search then sequence of words in search fields are important and further xxx yyyy or search yyyy xxx aren't same result. I want to say with an example: I create two input field one for Title and another for Author of my posts(Author is an example only and in this place is a custom fields ) I try to read these fields and search them in wordpress

<?php
$t = $_REQUEST['title'];
$a = $_REQUEST['author'];
global $wpdb;
$query = "SELECT DISTINCT wp_posts.* FROM wp_posts, wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id";

if ($t != '') {
    $t_sql = " AND wp_posts.post_title like '%$t%' ";
}

if ($a != '') {
    $a_sql = " AND wp_postmeta.meta_key = 'Author' AND wp_postmeta.meta_value like '%$a%' ";
}
$query .= $t_sql;
$query .= $a_sql;
$pageposts = $wpdb->get_results($query , OBJECT);
global $post;

if ($pageposts):
foreach ($pageposts as $post):
setup_postdata($post);
//...
endforeach;
endif;
?>

In Your idea, What do I must to do?

Answer 1

Actually, I've review your code on web developer portals, specially the SQL queries. And I also didn't knew why by searching xxx yyyy and yyyy xxx aren't same result with your PHP script. But the only tips that I can give to you:

$query .= $t_sql;
$query .= $a_sql;
// is to add an sql order by keyword like this
$query .= " ORDER BY wp_posts . post_title ";

Give it a try! And don't forget to addslashes() when you used $_GET, $_POST or $_COOKIE variable if the PHP of your server doesn't runs addslashes() on those variables. You can check this by using the function get_magic_quotes_gpc().

Answer 2

Making custom query which search in the WP DB is not a good way. Use the WP_Query to do this.

Here is a link where someone was experiencing the same problem:

https://wordpress.stackexchange.com/questions/18703/wp-query-with-post-title-like-something

Answer 3

You can split up your search terms by a Space character and then construct your query to look up every possible order of the words. Here is an example for your Title field:

// Assuming the title is "One Two Three"
$t = $_REQUEST['title'];

// Split the TITLE by space character
$terms = explode(' ', $t); // $terms = ["One", "Two", "Three"]

// Concats each search term with a LIKE operator
$temp = array();
foreach ($terms as $term) {
    $temp[] = "title LIKE '%".$term."%'";
    // $temp = ["title LIKE %One%", "title LIKE %Two%", ...
}

// Adds an AND operator for each $temp to the query statement
$query = "SELECT * FROM titleTable WHERE (".implode(' AND ', $temp).")";
// $query = SELECT * FROM titleTable WHERE 
       // (title LIKE '%One%' AND title LIKE '%Two%' AND title LIKE '%Three%')