Page 1 of 1

search query & keyword issue

Posted: Tue Oct 11, 2011 4:35 am
by darrel82
Hi All,

Just a couple of things with my site search, I am having trouble getting the search results to pickup any and all keywords. For example, if keywords for a search result are listed in the keywords column of my db as 'one, two, three' a search query for 'one two' is fine but a search for 'one three' will not display the search result. Instead of treating the keywords seperately it is treating all keywords as a whole phrase.

How can I make the search results pickup any keyword regardless of order.

Secondly I'm having trouble getting the search results to display by keyword relevance, any help is greatly appreciated.

Thanks.

Code: Select all



	function search()
	{
		parent::Controller();
		$this->load->model('Templating');
		$this->load->model('Company');
	}

	function index()
	{
		$this->load->view('search');
	}
	function search_redirect()
	{
		if ($_POST['keyword'] == '') $_POST['keyword'] = 'Keyword';
		redirect('search/showlist/'.
			urlencode(underscore($_POST['location'])).'/'.
			urlencode(underscore($_POST['category'])).'/'.
			urlencode(underscore($_POST['keyword'])));
	}
	function showlist($location = 'any', $category = 'any', $keyword = 'keyword',  $offset = '0')
	{
		/* get results */

		$data['companies'] = $this->Company->search($keyword, $category, $location, $offset, $name);
		/* paginate results */
		$row = $data['companies']->row();

		if($keyword == 'keyword' and $category=='any' ) {
			$data['categoryList'] = $this->buildCategoryList($location);
		}
		elseif(isset($row->categoryId)) {
			$categoryId = $row->categoryId;
			$data['linkList'] = $this->buildRefineList($categoryId);
		}
		$this->load->library('pagination');
		$config['base_url']    = site_url().'search/showlist/'.$location.'/'.$category.'/'.$keyword.'/';
		$config['total_rows']  = $this->Company->total_companies;
		$config['per_page']    = $this->Company->per_page;
		$config['num_links']   = 3;
		$config['uri_segment'] = $this->uri->total_segments();
		$this->pagination->initialize($config);

		$data['pagination'] = $this->pagination->create_links();
		$data['logged_in'] = $this->session->userdata('logged_in');
		$data['company_id'] = $this->session->userdata('company_id');
		$data['search_category'] = $category;
		$data['search_location'] = $location;
		if ($this->session->userdata('admin') != ''){
			$data['admin'] = $this->session->userdata('admin');
		}

		/* initialise template settings */
		$center = 'center/list_companies';
		$this->load->vars($data);
		$this->Templating->show($center);
	}

Re: search query & keyword issue

Posted: Tue Oct 11, 2011 10:30 am
by ouchiko
Two options;

On the assumption you setup the table correctly you can opt for this:
http://dev.mysql.com/doc/refman/5.5/en/ ... guage.html

It's quite nice. If the site is big then maybe have a think about Apache Solr.


If you want to do it yourself then..

Split up the keyword > keywords:

Code: Select all

$keywords = explode(" ", $keyword);
Now build up a query to use against the database. You need to asses each keyword against the col. and then rank each word. For instance, if the first word matches it will have a higher ranking than if the last.

Here's an example:

Code: Select all


$keyword = $_GET['q'];

$keywords = explode(" ", $keyword);

$extension = "if (User LIKE '$keyword', 5000, 0) + ";
$extension .= "if (User LIKE SOUNDEX('$keyword'), 2500, 0) + ";

foreach ( $keywords as $key )
{
  $extension .= "if (User LIKE '$key', 1000, 0) + ";
  $extension .= "if (User LIKE SOUNDEX('$key'), 900, 0) + ";
  $extension .= "if (User LIKE '$keyword %', 800, 0) + ";
  $extension .= "if (User LIKE '% $keyword', 700, 0) + ";
  $extension .= "if (User  LIKE '% $keyword %', 600, 0) + ";
  $extension .= "if (User  LIKE '%$keyword%', 500, 0) + ";
}

$extension = preg_replace("/\+ $/","",$extension);

$query = "SELECT User, ($extension) as Rank FROM mysql.user ORDER BY Rank DESC";

print $query;

Jim.

Re: search query & keyword issue

Posted: Tue Oct 11, 2011 10:37 am
by ouchiko
Ooops, shouldn't type in these small windows:

a) obviously escape any incoming keywords
b) it should be SOUNDEX(User) LIKE SOUNDEX('$key')