Groups and grouping

A few days ago I had looked at how to group the information in a way that meant that things could be related in different ways in the DB. My plan was that if you are on the page for a film, it can drill out all the info I have on it over and above the general profile page info stored.

At the moment Films can be added to lists, so I added in a part on the profile page for this to show up. So if a fil is tagged in any list(s) then it shows at the bottom of the page and allows you to go directly to the list.

On top of that I wanted to show any other films that may be in that series. For example if you selected Police Academy, if there are any more films from that series in the DB I wanted them to show up. This was an easy add but it makes the next part a bit more problematic.

Finally on the page I want to show recommended films that are similar to the one your viewing. Now this sounds simple (trust me it wasnt). There are different ways of doing this and at the moment I have 1 method implemented but im still not sure it is the best yet. Ideally I want to make the side as dynamic as possible. So when I want to add a new film, I just fill in the related profile info and the DB and php take care of the rest once its up (when suggesting other films). Now I could manually tag recommended films to each entry but that is labor intensive esp if you want to add a few films each time. But it would mean you get the same recommendations and they woudl be relevant.

Another way would be to use the genres that the films been tag with to pull back results. Now this would mean not being in control of what films show up (that's good and bad at the same time as it could show films totally unrelated). Going that method we could randomly select from the tagged genres and search or we could set up top genre choice and those genres are the ones used to search against.

At present i have went with the random selection of the genres that the films been tagged with. That way I am not having to re-do work (inputting genres again for a film after doing it in the first place). The top genres was a good idea but it meant extra work on/with the db when viewing a profile page which already has a fair bit of work going on on it.

The actual sql for this took about an hour and a half to get working as i wanted as it contains 2 selects, a group by, a count, a rand() an in and a not in. I have set it up so that it disregards films that only show up once in the returned results and give prominence more to the films that show up more. Was tricky but not as tricky as the other days SQL work.

Also still no ideas on the design front yet for how the site will look. But I have come up with an idea on how to do the film list page (a-z) index of all the films.