Subset-sum problem in PHP with MySQL

Question

following Problem:

I have a MySQL database with songs in it. The database has the following structure:

id INT(11)(PRIMARY)
title VARCHAR(255)
album VARCHAR(255)
track INT(11)
duration INT(11)

The user should be able to enter a specific time into a php form and the php function should give him a list of all possible combinations of songs which add up to the given time ±X min.

So if the user wants to listen to 1 hour of music ±5 minutes he would enter 60 minutes and 5 minutes of threshold into the form and would recieve all possible song sets which add up to a total of 55 to 65 minutes. It should not print out duplicates.

I already found several approaches to this problem but they did only give me back the durations which add up to X and not the song names etc. So my problem is how to solve this so that it gives me back the IDs of the songs which add up to the desired time or print out the list with the corresponding song names.

This seems to be one of the best answers I found, but I am not able to adapt it to my database.

Duration should not be TIME but a non signed integer probably storing the seconds length of each song. — Elzo Valugi, Sep 14 '11 at 12:42
You are right! I've changed TIME to INT and added the durations in seconds. — DcBexter, Sep 14 '11 at 12:49

score 0 · Answer 1 · answered Sep 14 '11 at 15:10

0

What you are describing is a Knapsack Problem. When I was in college, we used Dynamic Programming to attack problems like this.

The brute-force methods (just try every combination until one (or more) works is a complexity O(n!), or factorial-length problem - extremely lengthy to iterate through and calculate!

If your times for tracks are stored as an int (in seconds seems the easiest math to me), then your knapsack size is 3300-3900 (3600 seconds == 1 hour).

Is your goal to always return the first set that matches, or to always return a random set?

_{^{Note - by bounding your sack size, you greatly expand the number of possible answers.}}

answered Sep 14 '11 at 15:10

warren

28,486
19
80
115

My goal is to return every possible combination of songs that matches, not including duplicates. – DcBexter Sep 14 '11 at 15:37
@DcBexter - then realize that once you exceed a trivial number of tracks (say, 50), the computation time is going to become prohibitive. – warren Sep 14 '11 at 15:42
I am aware of this problem, that's one of the reasons why i like NikiC's approach in the post linked in my question above. But at this moment complexity is not a problem. – DcBexter Sep 14 '11 at 15:49

Subset-sum problem in PHP with MySQL

1 Answers1

Linked