For ages I have been meaning to add some sort of search to mycomparer. Well, we’re live! I spent may four hours total on it over two nights, and implemented the following features
- walk through each word in the query and search categories and += matching categories
- walk through each word in the query and check against upcs
- walk through each word in the query and check against affiliate ids, like searching by asin
- good old full text search via mysql
I did the first three the first night, and started the full text search. Here’s what I had to do.
- Create a table
- CREATE TABLE sh_product_my_text (
product_id INT NOT NULL,
FOREIGN KEY (product_id) REFERENCES sh_product(id) ON DELETE CASCADE,
my_text TEXT NOT NULL,
FULLTEXT(my_text),
timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=’MYISAM’;
- CREATE TABLE sh_product_my_text (
- then in my shopping db population process, I populated the table with some product stuff
- I change a query like “computers netbook -wireless” to “+computers +netbook -wireless”
- the above ends up in ? for
- SELECT product_id FROM sh_product_my_text WHERE MATCH(my_text) AGAINST(? IN BOOLEAN MODE) limit 20;
- also added–key_buffer_size=1024M to my mysql config. pretty terrible before this change, pretty good after
Course, I implemented it as a service and tie into the service via my Template::Plugin::WebService with code that looks like
[% USE web_service = WebService %]
[% search_ref = web_service.webservice_call(‘/api/shop/search’, form) %]
Can’t tell you how cool I think that is. If I decide to serve straight from flex or something, then it is pretty well no code change.
And that’s about it. Give it a go.
Enjoy!
Earl