Page 1 of 1

How to perform a best match search with SQL Lite?

Posted: Sat Mar 07, 2009 6:53 am
by mustaine85
Hi,
I have a html page of selection boxes which contain features of a product, and I have a database that contains a selection of product packages that contain different features.
What I'd like to know is how can I search the database based on the product features the customer chooses and return which package best suits their needs?
I know how connect to the database, perform queries and send and recieve data, but I can't work out how to do a best match search based on many fields.

Any help is greatly appreciated :)

Re: How to perform a best match search with SQL Lite?

Posted: Sat Mar 07, 2009 8:53 am
by VladSun
If you want to use text search, take a look at full text search feature http://dev.mysql.com/doc/refman/5.1/en/ ... earch.html
If you have a separate table for features related to products, then you could use INNER JOIN on product/feature tables and ORDER BY COUNT(feature.id)

EDIT: Oops, its SQL lite, not MySQL

Re: How to perform a best match search with SQL Lite?

Posted: Wed Mar 11, 2009 12:35 pm
by mustaine85
I've got theories on how to achieve what I want to do but I can't code them.
I have four packages with different features, and there is a page where the customer selects what features they want
with a group of selection boxes, the results are then posted to the php script.
Could I then do some kind of loop with php to compare each required feature to each field
of the different packages, increment a counter (one for each package) when a feature matches and then return
the package that has the majority of features that the customer wants?
My package table is set out like this:

Package Feature1 Feature2 etc.
Level 1 no yes
Level 2 no no
Level 3 yes yes
Level 4 yes no

If I could get any help with this it would be greatly appreciated, I understand what I'm doing but because I'm new to scripting
it's hard to get something to do what I want it to do.