how to build an advanced search feature for a CI site

[eluser]metaprinter[/eluser]
i want an advanced search option like
http://ellislab.com/forums/search/

for a product catalog site (oberk.com).

what do i need to do? where do i start?

[eluser]erik.brannstrom[/eluser]
What is so advanced about the CodeIgniter forum search? Isn't it pretty standard? Sorry, but I need something more to go on here.

[eluser]haydenk[/eluser]
You could integrate Google Search or do like the main site (not the forum) of this site does and pass it through google search with "site:codeigniter.com".

[eluser]pickupman[/eluser]
The only thing different used on the forums here is that it creates a db record for a search, and then you are redirected to url with a hash appended to find the result. This is done for pagination, so that on each page, you can retrieve the search record rather than depending on a _POST value or session value. Other than that, search is really based on how you want to implement it. Jelmer has MP_Search library that will index content from the db, and use it for search. MySQL has good support for different types of search syntax LIKE, MATCH/AGAINST, etc.
Are you looking for just possible matches, sorted by date, relevevance?

[eluser]metaprinter[/eluser]
@eric see that "Advanced Search" link next to the searchbox? click that, that's what I'm talking about.

@others, thanks for the direction.

@pickupman, i don't think my search is set up right becuase when i perform broad searches for like "glass" i get only 14 results instead of the hundred of glass related products in the catalog.

how do i tell the search to search all fields in all tables my product database (mysql)?

[eluser]pickupman[/eluser]
Here a link for using mysql natural language search.
[url="http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html"]MySQL Docs[/url]. As explained in the article, you need to create a table index on the columns you will be search. MySQL will use the index to create faster results than possibly using LIKE statements. I would run both queries:

Code:

$this->db->or_like('item_name', $keyword);
$this->db->or_like('item_description', $keyword);
$this->db->or_like('item_category', $keyword);
$this->db->get('products');

//vs using something like
$this->db->select('*, MATCH( item_name, item_description, item_category ) AGAINST( '.$keyword.' ) AS relevance');
$this->db->where('relevance >= 0');
$this->db->order_by('relevance', 'DESC');
$this->db->get('products');

Haven't tested but should work. See which one is faster. The second should be if you have created the correct fulltext index.

[eluser]Jelmer[/eluser]
It's been a while since I researched all the options. But MySQL fulltext search (MATCH...AGAINST) has a 50% cut-off for words that occur often which might be a problem for you. The important part is in the last 3 paragraphs of the link of pickupman, the very last one is most clear on how it works and why it might be a problem in smaller datasets:

The 50% threshold has a significant implication when you first try full-text searching to see how it works: If you create a table and insert only one or two rows of text into it, every word in the text occurs in at least 50% of the rows. As a result, no search returns any results. Be sure to insert at least three rows, and preferably many more. Users who need to bypass the 50% limitation can use the boolean search mode; see Section 11.9.2, “Boolean Full-Text Searches”.

[eluser]pickupman[/eluser]
Good point Jelmer. I tried to plug your library above. I haven't used it yet though. I was testing this out on a data set that I had. I was surprised by the results. Using the MATCH...AGAINST table where there was about 100 rows of similar data, yielded nothing because there would be too many duplicates. However, I ran another test on a table of 42k US zipcodes searching by city name. The column is index, and using the LIKE %city_name% returned in .07 secs. Using MATCH(city) AGAINST('city_name') returned in .004 secs. About 40% faster. I guess it depends on your data.

[eluser]Jelmer[/eluser]
@pickupman
Regretably I still haven't tested the English version of it myself. Will probably have a German version tested before ever getting round to that (I'm working on a website with a Dutch and German frontend). I'd love to hear about it if you get to testing the English implementation.

My sense of it is that MATCH..AGAINST/fulltext search will work great on huge datasets and sites with many large articles. But for smaller websites(< 100 pages)/datasets (< 1000 items), the 50% threshold will be a huge problem.
LIKE will probably never be very efficient as I believe it doesn't use an index or anything (like fulltext search does). Which means it needs to do string comparissons on each cell in the columns you're testing against. That can only mean horrible performance, especially on bigger datasets.
Which, in the end, was how I got to the point of writing my own indexer. It's a lot weaker then MATCH..AGAINST but will work great for small to medium datasets (like a small-business website or something like that). I'm not sure if it's the best thing in this case though.

@metaprinter
I'd suggest only allowing productname search (using LIKE) and giving customers selection options within categories which can limit the SELECT output with simple WHERE statements. But if you want your search to cover the entire website and not just the products, then my library might indeed be an option for you.

[eluser]metaprinter[/eluser]
Thanks pickupman and jelmer! great info.