Can MYSQL do this...?

[stew dean]

I am very new to mysql so please forgive me if I may be asking
something obvious - I'm also not likely to fully understand the answer
but if I know it can be done and someone could point me in the right
direction I will spend the time to learn what I need to get things
going.

If I create a database can I do the following easily using MYSQL (with
PHP or similair)?

I want to set up a system where a user sets sliders to different
positions to select things. Each item will have a value for each
sliders stored with it and then as the user changes the sliders I want
to be able to check the database to find the closest match to the
users sliders.

In a bit more detail, I have six sliders, each with a value between
one and 20, I want my system to compare the users settings to each
entry in the database and find the least different. Each item would be
scored - so if the user puts in 10 on a slider but the database entry
is 8 or 12 then 2 is added to the score of that item. The lower the
score the closer the match. If all the sliders are the same for an
item as those the user enters the score will be 0. Is there a quick
way for MYSQL to do this or is it a matter of doing the maths in the
application rather than via a simple query?

I hope this makes sense. I'm looking to find the quickest and simplest
way to do search like this through a database of about 5000 items.

Cheers

Stew Dean

[Aggro]

stew dean wrote:

I am very new to mysql so please forgive me if I may be asking
something obvious - I'm also not likely to fully understand the answer
but if I know it can be done and someone could point me in the right
direction I will spend the time to learn what I need to get things
going.

If I create a database can I do the following easily using MYSQL (with
PHP or similair)?

I want to set up a system where a user sets sliders to different
positions to select things. Each item will have a value for each
sliders stored with it and then as the user changes the sliders I want
to be able to check the database to find the closest match to the
users sliders.

In a bit more detail, I have six sliders, each with a value between
one and 20, I want my system to compare the users settings to each
entry in the database and find the least different. Each item would be
scored - so if the user puts in 10 on a slider but the database entry
is 8 or 12 then 2 is added to the score of that item. The lower the
score the closer the match. If all the sliders are the same for an
item as those the user enters the score will be 0. Is there a quick
way for MYSQL to do this or is it a matter of doing the maths in the
application rather than via a simple query?

I hope this makes sense. I'm looking to find the quickest and simplest
way to do search like this through a database of about 5000 items.

So if I understood you correctly.. You would have table like this:

| item |
--------
|id
|slider1
|slider2
|slider3
|slider4
|slider5
|slider6

And you would have 5000 rows in that table. Then user gives you 6 values
that represent the values of slider1-6. Then you will calculate the
difference value for each item and return closest matches.

Difference value will be calculated like this:
If item in database has values:
1,10,5,10,10,10

And user enters values: 1,5,10,15,10,10

You would get difference:
abs(1-1) + abs(5-10) + abs(10-5) + abs(15-10) + abs(10-10) + abs(10-10)
= abs(0) + abs(-5) + abs(5) + abs(10) + abs(0) + abs(0)
= 0 + 5 + 5 + 10 + 0 + 0
= 20

If this is the case, then let's have a look at the example I created:
#################

create table item(id int, value1 int,value2 int,value3 int,value4
int,value5 int, value6 int);

insert into item values(1,1,1,1,1,1,1);
insert into item values(2,10,10,10,10,10,10);
insert into item values(3,10,10,10,10,10,10);
insert into item values(4,11,10,10,10,10,10);

select * from item;
+------+--------+--------+--------+--------+--------+--------+
| id | value1 | value2 | value3 | value4 | value5 | value6 |
+------+--------+--------+--------+--------+--------+--------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 10 | 10 | 10 | 10 | 10 | 10 |
| 3 | 10 | 10 | 10 | 10 | 10 | 10 |
| 4 | 11 | 10 | 10 | 10 | 10 | 10 |
+------+--------+--------+--------+--------+--------+--------+
4 rows in set (0.00 sec)

# So if you search match for these values( 1,1,3,1,1,1 ):

select id,
(abs(value1-1)+abs(value2-1)+abs(value3-3)+abs(value4-1)+abs(value5-1)+abs(value6-1))
as value from item order by value limit 1;

+------+-------+
| id | value |
+------+-------+
| 1 | 2 |
+------+-------+

Please note, that it is possible that several items can have same value,
and this only returns first one. ( You asked only for one. )

Without "limit" the query would return them all, like this:
+------+-------+
| id | value |
+------+-------+
| 1 | 2 |
| 2 | 52 |
| 3 | 52 |
| 4 | 53 |
+------+-------+

However, what comes to speed issues. The speed of this query will
increase as the amount of rows increase in the database, since query
will have to do calculations for each row every time it is executed.
5000 rows? I don't think you will get problems with speed, but better
try it out to be sure.

I am not an expert what comes to mathematics, so I'm not sure if there
is a better way to calculate the difference value. Searching for exact
match would be faster.

[Aggro]

stew dean wrote:

I am very new to mysql so please forgive me if I may be asking
something obvious - I'm also not likely to fully understand the answer
but if I know it can be done and someone could point me in the right
direction I will spend the time to learn what I need to get things
going.

If I create a database can I do the following easily using MYSQL (with
PHP or similair)?

I want to set up a system where a user sets sliders to different
positions to select things. Each item will have a value for each
sliders stored with it and then as the user changes the sliders I want
to be able to check the database to find the closest match to the
users sliders.

In a bit more detail, I have six sliders, each with a value between
one and 20, I want my system to compare the users settings to each
entry in the database and find the least different. Each item would be
scored - so if the user puts in 10 on a slider but the database entry
is 8 or 12 then 2 is added to the score of that item. The lower the
score the closer the match. If all the sliders are the same for an
item as those the user enters the score will be 0. Is there a quick
way for MYSQL to do this or is it a matter of doing the maths in the
application rather than via a simple query?

I hope this makes sense. I'm looking to find the quickest and simplest
way to do search like this through a database of about 5000 items.

So if I understood you correctly.. You would have table like this:

| item |
--------
|id
|slider1
|slider2
|slider3
|slider4
|slider5
|slider6

And you would have 5000 rows in that table. Then user gives you 6 values
that represent the values of slider1-6. Then you will calculate the
difference value for each item and return closest matches.

Difference value will be calculated like this:
If item in database has values:
1,10,5,10,10,10

And user enters values: 1,5,10,15,10,10

You would get difference:
abs(1-1) + abs(5-10) + abs(10-5) + abs(15-10) + abs(10-10) + abs(10-10)
= abs(0) + abs(-5) + abs(5) + abs(10) + abs(0) + abs(0)
= 0 + 5 + 5 + 10 + 0 + 0
= 20

If this is the case, then let's have a look at the example I created:
#################

create table item(id int, value1 int,value2 int,value3 int,value4
int,value5 int, value6 int);

insert into item values(1,1,1,1,1,1,1);
insert into item values(2,10,10,10,10,10,10);
insert into item values(3,10,10,10,10,10,10);
insert into item values(4,11,10,10,10,10,10);

select * from item;
+------+--------+--------+--------+--------+--------+--------+
| id | value1 | value2 | value3 | value4 | value5 | value6 |
+------+--------+--------+--------+--------+--------+--------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 10 | 10 | 10 | 10 | 10 | 10 |
| 3 | 10 | 10 | 10 | 10 | 10 | 10 |
| 4 | 11 | 10 | 10 | 10 | 10 | 10 |
+------+--------+--------+--------+--------+--------+--------+
4 rows in set (0.00 sec)

# So if you search match for these values( 1,1,3,1,1,1 ):

select id,
(abs(value1-1)+abs(value2-1)+abs(value3-3)+abs(value4-1)+abs(value5-1)+abs(value6-1))
as value from item order by value limit 1;

+------+-------+
| id | value |
+------+-------+
| 1 | 2 |
+------+-------+

Please note, that it is possible that several items can have same value,
and this only returns first one. ( You asked only for one. )

Without "limit" the query would return them all, like this:
+------+-------+
| id | value |
+------+-------+
| 1 | 2 |
| 2 | 52 |
| 3 | 52 |
| 4 | 53 |
+------+-------+

However, what comes to speed issues. The speed of this query will
increase as the amount of rows increase in the database, since query
will have to do calculations for each row every time it is executed.
5000 rows? I don't think you will get problems with speed, but better
try it out to be sure.

I am not an expert what comes to mathematics, so I'm not sure if there
is a better way to calculate the difference value. Searching for exact
match would be faster.

[stew dean]

Aggro <sp**********@yahoo.com> wrote in message news:<Cg***************@read3.inet.fi>...

stew dean wrote:

I am very new to mysql so please forgive me if I may be asking
something obvious - I'm also not likely to fully understand the answer
but if I know it can be done and someone could point me in the right
direction I will spend the time to learn what I need to get things
going.

If I create a database can I do the following easily using MYSQL (with
PHP or similair)?

I want to set up a system where a user sets sliders to different
positions to select things. Each item will have a value for each
sliders stored with it and then as the user changes the sliders I want
to be able to check the database to find the closest match to the
users sliders.

In a bit more detail, I have six sliders, each with a value between
one and 20, I want my system to compare the users settings to each
entry in the database and find the least different. Each item would be
scored - so if the user puts in 10 on a slider but the database entry
is 8 or 12 then 2 is added to the score of that item. The lower the
score the closer the match. If all the sliders are the same for an
item as those the user enters the score will be 0. Is there a quick
way for MYSQL to do this or is it a matter of doing the maths in the
application rather than via a simple query?

I hope this makes sense. I'm looking to find the quickest and simplest
way to do search like this through a database of about 5000 items.

So if I understood you correctly.. You would have table like this:

| item |
--------
|id
|slider1
|slider2
|slider3
|slider4
|slider5
|slider6

And you would have 5000 rows in that table. Then user gives you 6 values
that represent the values of slider1-6. Then you will calculate the
difference value for each item and return closest matches.

Difference value will be calculated like this:
If item in database has values:
1,10,5,10,10,10

And user enters values: 1,5,10,15,10,10

You would get difference:
abs(1-1) + abs(5-10) + abs(10-5) + abs(15-10) + abs(10-10) + abs(10-10)
= abs(0) + abs(-5) + abs(5) + abs(10) + abs(0) + abs(0)
= 0 + 5 + 5 + 10 + 0 + 0
= 20

<trim rest of exellent example>

Thanks for the reply Aggro. You answer works in the way I was going to
do it - query all the items, do the maths as about then put the scores
in an table/array and then sort for the matches, although the example
using database specific terms was very helpful. I have yet to use SQL
to do this but my previous perl version got slower and slower the more
items I put in.

I guess I'm wondering if someone out there knows of a quicker way to
do it. I even contemplated prebuilding each combination of slider but
the idea is to eventualy have about eight sliders with a 256
resolution and have them optional.

Perhaps I should be asking this question in a maths group. Anyone have
any recommendations - I get this nagging feeling there must be a
faster way to do this.

Thanks again,

Stew Dean

[stew dean]

Aggro <sp**********@yahoo.com> wrote in message news:<Cg***************@read3.inet.fi>...

stew dean wrote:

I am very new to mysql so please forgive me if I may be asking
something obvious - I'm also not likely to fully understand the answer
but if I know it can be done and someone could point me in the right
direction I will spend the time to learn what I need to get things
going.

If I create a database can I do the following easily using MYSQL (with
PHP or similair)?

I want to set up a system where a user sets sliders to different
positions to select things. Each item will have a value for each
sliders stored with it and then as the user changes the sliders I want
to be able to check the database to find the closest match to the
users sliders.

In a bit more detail, I have six sliders, each with a value between
one and 20, I want my system to compare the users settings to each
entry in the database and find the least different. Each item would be
scored - so if the user puts in 10 on a slider but the database entry
is 8 or 12 then 2 is added to the score of that item. The lower the
score the closer the match. If all the sliders are the same for an
item as those the user enters the score will be 0. Is there a quick
way for MYSQL to do this or is it a matter of doing the maths in the
application rather than via a simple query?

I hope this makes sense. I'm looking to find the quickest and simplest
way to do search like this through a database of about 5000 items.

So if I understood you correctly.. You would have table like this:

| item |
--------
|id
|slider1
|slider2
|slider3
|slider4
|slider5
|slider6

And you would have 5000 rows in that table. Then user gives you 6 values
that represent the values of slider1-6. Then you will calculate the
difference value for each item and return closest matches.

Difference value will be calculated like this:
If item in database has values:
1,10,5,10,10,10

And user enters values: 1,5,10,15,10,10

You would get difference:
abs(1-1) + abs(5-10) + abs(10-5) + abs(15-10) + abs(10-10) + abs(10-10)
= abs(0) + abs(-5) + abs(5) + abs(10) + abs(0) + abs(0)
= 0 + 5 + 5 + 10 + 0 + 0
= 20

<trim rest of exellent example>

Thanks for the reply Aggro. You answer works in the way I was going to
do it - query all the items, do the maths as about then put the scores
in an table/array and then sort for the matches, although the example
using database specific terms was very helpful. I have yet to use SQL
to do this but my previous perl version got slower and slower the more
items I put in.

I guess I'm wondering if someone out there knows of a quicker way to
do it. I even contemplated prebuilding each combination of slider but
the idea is to eventualy have about eight sliders with a 256
resolution and have them optional.

Perhaps I should be asking this question in a maths group. Anyone have
any recommendations - I get this nagging feeling there must be a
faster way to do this.

Thanks again,

Stew Dean