How to perform a best match search with SQL Lite?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mustaine85
Forum Newbie
Posts: 2
Joined: Sat Mar 07, 2009 6:36 am

How to perform a best match search with SQL Lite?

Post 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 :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
mustaine85
Forum Newbie
Posts: 2
Joined: Sat Mar 07, 2009 6:36 am

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

Post 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.
Post Reply