I'm building a fairly straight forward site for a project. Its a movie/film archive with MySQL as the database.
The pages are long clickable lists of films, separated into genres, with a little bit of info beneath each describing the films. The links lead to individual pages of the films which provide more information.
There is also a search engine feature (FULL TEXT search) which goes through the MySQL tables to retrieve films.
I've got to a point where I'm out of my depth and need some help. I'm looking for someone to do the following:
Create a tag/related content system. I'd like to have related films show up but am unsure what is the best way to go about this. I've already made all the tags for the films so i just need a way to relate them on the individual pages. E.g. on one individual page it might be 'The Terminator' which holds some information on the film. The tag system would show a list of films on the right that have similar tags.
Also on each individual page, in the information part, there is a title 'tags' and next to it I'd like the tags for that particular film showing as links that would lead to a list of films with that particular tag.
I'm unsure if it would be faster to give tags their own table and connect them to the films in another table. At the moment i have them all in the same table.
The pages are basically long lists so I need them paginated. This includes on the search results and the tag results pages as well. Showing a total of 12 results per page. Something similar to youtube or google with a next button and say pages 1,2,3,4,5 or 8,9,10,11,12 etc. with previous button showing after clicking on page 2 onwards and on the last page to have no next button.
I'd also like the full text search to have an additional feature. When something is searched and results appear, id like two optional buttons that order the results into 'most relevant' (which is what it shows automatically anyway) and date added (the most recently added film on the MySQL database). If you think any of the code for the search could be better optimised that would be great too!
The main focus is speed and efficiency i.e obtaining information from MySQL as fast and smoothly as possible. I'm mainly interested in the code and what is the best way to go about these problems i have.
Hope this is enough information for you. Thanks for your time!