Help with comparing

Billfred

I hope someone out there can give me some help with this. It's driving me crazy...

I want to compare one row against the others in a table. The aim of the project is to create a recommended list (if you like this then you'll love this.)

There are a number of t-shirts in the db and they fall into one or more categories. The categories table contains the ID of the t-shirt followed by a list of category names that are marked either '1' or '0' depending on whether the t-shirt falls into that category. eg. TshirtID=1, Cat1 = 1, Cat2 = 0, Cat3 = 0, Cat4 = 1 etc.

There are 23 categories in all.

What I want to do is to take one tshirt and compare it to the next and get a score of how well its categories compare; add this score to an array. Then compare to the next shirt and add to the array. etc etc Then sort the array to get the top five most relavent tshirts. You all still awake?

I then want to move onto the next shirt in the db and do exactly the same. Until by the end I have all my shirts, each of them associated with their five most relevant shirts.

I guess my question is how the hell do I do this?

Sorry for the long question. Someone please help.

Jack_Slocum

Actually this would be more easily accomplished if your data model was set up correctly (normalized). You data has a one to many relationship, and so it should have a table to link shirts to categories. For Example:

tshirt

tshirt_id
description

tshirt_to_category

tshirt_id
category_id

Then each row in the tshirt_to_category table would represent a link (one tshirt can have multiple rows, depending on how many categories it falls in).

With this setup you could set up some hideous query that would do the comparisons you need.

G'luck,

Jack

dombey

I guess i do not know if you need help with the coding or the logic of the comparison, but here is an idea for both:

I looked at the problem and the first thing that jumps out at me is that the structure of the selected catagories is open to a bitwize interpretation ie if T-Shirt A has a zero in catagory1, a one in catagory2, a one in catagory3, and a zero in catagory4 its catagories are 0110. If the next Tshirt has a one in catagory1, a one in catagory2, a zero in catagory3, and a zero in catagory4 its catagories are 1100. If you want to find the similarities between these two tshirts you want to know were they are both one and were they are both zero (so we think xor!).
0110 xor 1100 = 1010
so were ever the xor product is zero is a point of similarity so these two tshirts have a similarity of 2.
So throw this in an associative array named tshirt one with the name, value pairs being the id of the compared tshirt and the number of similarities(zereos in the xox product). Sort by highest number and grab the top five - or even just keep five in the array by putting the first five in the array and then sorting and then compare incoming against the lowest value and either rplace or discard.

if this is something that you are doing on a regular basis it might be a good idea to have a database table with the tshirt associated with its top five friends. If changes were made(ie tshirts added, or catagories) the table could be cleard and the script run again to asses similarities, sort and insert.

I hope this is not too confusing or tangeled up, it was just the first hing that came to mind.