CODEWITHSUNDEEP
phpmysql
CnR
CnR

Reputation: 68

How to Find Similar Rows with Numeric Fields in MySQL?

I have a database which stores song informations.

The informations are:

Loudness of song which is between -100 AND 100,
Energy of song which is between 0 AND 1,
Tempo of song which is between 0 AND 500,
Danceability of song which is between 0 AND 1

I need to find two songs similarity.

For Example:

A song has informations like

loudness: -9.121000000000000
energy: 0.439501478057000
tempo: 133.929000000000000
danceability: 0.686523448220000

How can I find songs similar to this song.

Upvotes: 1

Views: 182

Answers (3)

khael
khael

Reputation: 2610

I think you want value/values closer to the values you have already have... searching it I found this question:

Select closest numerical value with MySQL query

that I think you might find interesting. So basing on what was written there, we could do something like:

$query = <<<query
(
select   *
from     songs
where    loudness >= $given_loudness
AND
energy >= $given_energy
AND
tempo >= $given_tempo
AND
danceability >= $given_danceability
order by id asc
limit 1
)
union
(
select   *
from     songs
where    loudness >= $given_loudness
AND
energy >= $given_energy
AND
tempo >= $given_tempo
AND
danceability >= $given_danceability
order by id desc
limit 1
)
query;

$res = mysql_query($query);

I have considered that you have an id in the songs table

Upvotes: 0

user894932
user894932

Reputation:

a number of possibilities on this broad question.

you could select all songs that have the same loudness or the same enery or tempo or danceability

or indeed a combination of each.

you could select all songs that are similar value within a range of value + or - x%

you could output the whole table to screen and use a tablesorter plugin to order your output on various columns.

Upvotes: 0

Brad
Brad

Reputation: 163438

MySQL has a nice BETWEEN operator:

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between

So you could do something like this:

SELECT * FROM songs WHERE loudness BETWEEN -9.121-1 AND -9.121+1;

Just add all the conditions you want, and sort by whichever columns are important to you.

Upvotes: 1

Related Questions