Multiple queries on the same table

mattpointblank

Hey everyone.

I've written a PHP system to manage a Review website.

My displayreview.php file queries the Reviews table:

displayreview.php?id=67

The query here then looks up all the fields for ID 67, simple stuff.

My problem is, I wanna do a second query on the Reviews table underneath for related items (say, if there are reviews of the same band/record label.)

I want the query to select * from Reviews where, say, Record_Label=$Record_Label (as already defined in the first query), so that the results would display all other reviews for records released on Review 67's record label. I don't know if I could actually do this in a query anyway, but my main problem is how would I display these values? $BandName etc are already defined from the initial query, so how would I display any alternate ones for the second query?

Matt

thorpe

for starters... you should be able to do this in one query using joins (you might want to google sql joins). can we see your db structure?

mattpointblank

I'm already using joins as I have a Staff table and a 'author' column in the Reviews table that are linked together so I can display the Author and Email address of the author in the review.

My db structure:

Table: Reviews
id
Band_Name
Record_Name
Review
Author
Score
Cover_Image
Record_Label
Band_Site_URL

Table: Staff
id
Staff_Name
Staff_Email

mattpointblank

Anyone?

konsu

what is your query?

mattpointblank

I explained it all above. Re-read it, it's not so much a broken query as a problem in using the values the query creates.

konsu

as someonel else said, you can do it in a single query. and they suggested joins. you said that you are using joins, that is why i wanted to see the query. basically i would join the reviews with itself on whatever fields are related.

Jasp182

This is where a wrapper class for your db queries would come in handy. Instead of iterating through your results to build your html table (I'm only making an assumption that you're doing it that way...please tell me if otherwise), you could stuff your results in an array. Then you'd have the variable for reuse, and you could iterate the array to build your html.

For example...SELECT * FROM reviews WHERE record_label = $results['record_label']