Tech Support Forum banner
Status
Not open for further replies.
1 - 2 of 2 Posts

·
Registered
Joined
·
100 Posts
Discussion Starter · #1 · (Edited)
hello there. i am trying to implement a recommendation algorithm (using a complex query) similar to amazon's item-to-item for a small database of students and modules.
to understand exactly what i need to, here is the structure of my db:

Modules(moduleID, title)
Students(studentID, name)
Selections(studentID, moduleID)

every student can select some modules which are inserted into the Selections table. i believe it's very simple to understand it.
now this is what i want to do. i'm implementing a website in php where a student logs in (his studentID gets stored in a session variable) and then, when he clicks on a module to add it to his options, the moduleID gets passed through the url, and retrieved by a GET method.

in the next page now, the pair (studentID and moduleID) is inserted into the database and furthermore, i want to implement here the recommendation algorithm in order to choose more modules. By using the moduleID (let's say 1010) just inserted, i want the query to search through the Selections table and find all the OTHER modules that were selected by OTHER students (apart from the student currently logged in) who have ALSO selected the moduleID 1010. These other modules, i want them sorted in descending order according to their COUNT/popularity. And this is the recommendation algorithm:)

I hope I haven't confused you, a lot! I have tried several combinations of nested select statements, always getting syntax errors..
this is approximately the query that I was testing (php code):

Code:
SELECT moduleID, COUNT(moduleID) FROM Selections WHERE Selections.studentID EXISTS (SELECT studentID FROM Selections WHERE  Selections.moduleID = '{$_GET['addition']}' AND (Selections.studentID != '{$_SESSION['studentID']}')) GROUP BY moduleID ORDER BY COUNT(moduleID) DESC;
I got lost, however, somewhere!

I hope someone can help me, I know it's not very difficult but I just don't seem to get it right!


Thanks anyway,

Kyris
 

·
Registered
Joined
·
100 Posts
Discussion Starter · #2 ·
Re: SQL Help (recommendation algorithm)

update: i have managed to solve this problem by myself. for those interested in the select statement, here it is:

Code:
SELECT Modules.moduleID, title, COUNT(Selections.moduleID) FROM Modules, Selections WHERE Modules.moduleID = Selections.moduleID AND Selections.moduleID != '{$_GET['addition']}' AND Selections.studentID != '{$_SESSION['studentID']}' AND Selections.studentID IN (SELECT studentID FROM Selections WHERE moduleID = '{$_GET['addition']}') GROUP BY moduleID ORDER BY COUNT(Modules.moduleID) DESC, Modules.moduleID ASC;
thanks guys...
 
1 - 2 of 2 Posts
Status
Not open for further replies.
Top