Simple search engine queries

[eluser]Berik[/eluser]
Hi folks!

For an upcoming project I'll be making a small and simple text-based search engine to find products on the website I'll be building.

I'm wondering what would be the best way of dealing with multiple search terms within my db query.
- Should I run a separate query for each term (and slap all the results in an array)?
- Is there a more elegant (and less ressource-hungry) way of doing it in a single query?

For a single term a simple $this->db->like('title', $term); is fine but for multiple words I was thinking of doing something along the way of:

Code:

$terms = explode(' ', $search_terms);
foreach ($terms as $term)
{
   // run a $this->db->like('db_field_to_search', $term) query
   // append results to a $search_results[] array
}

As a side question, is there a way of clearing Active Record's query parameters between each new query? Haven't found that in CI's documentation (or maybe I can't read :p)


Thanks for any input you have on the subject

[eluser]Dirk Einecke[/eluser]
Hi,

use $this->db->distinct(); to get no duplicates.

Dirk

[eluser]Colin Williams[/eluser]
Each time you run a query, you get a clean slate with active record

[eluser]Berik[/eluser]
Hey thank you both for the fast replies

I take it that my (stupidly simple) initial idea of looping queries is a correct way of handling this issue.

@Dirk: If I run multiple queries how will distinct() prevent duplicates? Must be something I'm missing here! I was thinking of removing already found IDs in subsequent queries to do that.

@Colin: that's cool, didn't know that thanks

[eluser]Dirk Einecke[/eluser]
Hi,

[quote author="Berik" date="1261168627"]@Dirk: If I run multiple queries how will distinct() prevent duplicates? Must be something I'm missing here! I was thinking of removing already found IDs in subsequent queries to do that.[/quote]

You don't have to make multiple queries if you want to get one results list.

You can combine all search terms in one query:

Code:

foreach ($terms as $term) {
  $this->db->or_like('db_field_to_search', $term);
}

Dirk

[eluser]Berik[/eluser]
Thanks Dirk, hadn't thought about that

[eluser]aidehua[/eluser]
If you're using a MySql database, have you looked at the full-text search functions?
http://dev.mysql.com/doc/refman/5.0/en/f...earch.html

(Might help, depending on how the data in your database is structured.)

[eluser]Eric Cope[/eluser]
fulltext searches may be better. They can sort by relevance, something LIKE won't do.
http://stackoverflow.com/questions/10168...-relevancy