My project is a database of locations, searchable by multiple keywords. When a user searches the database a selection list of possible locations appears. A location will appear in the list once for each photo of that location. If the user selects an item from the list they are shown detailed information about the location and ALL of the photos.
In the selection list I want to rank the locations by relevancy (how many of the chosen keywords did it match) and then by the quality of the photograph (subjective ranking: 1 - 5). PROBLEM: I can easily order the locations by relevancy (using GROUP BY) or the photos by quality, but how can I order the photos in the same order (relevancy) that the locations are ordered? To be able to extract the relevancy (using COUNT) I need to GROUP the data - but then won't be able to display the photos individually. I have three main tables: locations, photos, keywords The keywords-locations relationship is many-to-many, so there is a 'linking' table kw_link. Seems that creating a temporary table (of the GROUPed data) may be the answer, but I'm hoping there's a more straightforward approach and I've never worked with a temp table. If that IS the best route, how could I approach it to avoid conflicts between multiple users hitting at the same time? I'm using PHP as a front end, but could go to Perl if there's a reason to. Any suggestions would be most welcome. Thank you. Nelson -- =========================================================== Nelson Goforth Lighting for Moving Pictures 01.303.322.5042 http://www.earthnet.net/~ngoforth/film --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
