MySQL Forums
Forum List  »  Newbie

Using 4 tables, need a search on all of them, please help :-)
Posted by: Menelaos Megariotis
Date: April 10, 2005 07:15AM

Hello,

First of all, excuse my probably very stupid questions, but I'm really new to MySQL, so... :) Anyway, here's the thing. I run a music magazine with reviews on new CDs. So, I have 4 tables:

1. Reviewers (ID (pr. key), Name and more info)
2. Artists (ID (pr. key), Artist's name, more info)
3. Labels (ID (pr. key), Record Label's name, more info)
4. Reviews (ID (pr. key), Artist's ID, Reviewer's ID, Band's ID and more info)

The problem now: I want to show for example all reviews published on xx month. I can search table Reviews and find them, and then show the results. BUT I won't be able to see the band's name, the label's name etc, but only their ID. So, obviously, I want to then query the other three tables, to receive the missing info, based on the respective ID. How do I do it? It's obviously terrible to query once for each ID, that is once for each row of the result. On the other hand, we have about 1000 reviews every year and we've been running our magazine for 3 years now. It's not the best idea to just query 'select ID, Artist's Name from Labels' and put, say, info by 1,000 artists in an array, just to use 20 records of it... So, what's the best idea here? How can I do this?

Extra, little question: I want the user to be able to choose which column to sort the result by: submission date, artist's name, label's name, Reviewer's name. How do I do this? Is it a good idea to run a query every time the page is loaded and ask for a sorted result FROM the query, or store the first result somewhere (where?) and then sort my php table which contains the result?

Thanks a lot in advance! :-)

Cheers,
Menelaos Megariotis

Options: ReplyQuote


Subject
Written By
Posted
Using 4 tables, need a search on all of them, please help :-)
April 10, 2005 07:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.