Page 1 of 1

MySQL Query with Count and Date Sort...

Posted: Wed Nov 24, 2004 8:47 am
by newphpuser
I am trying to figure out how to get information out of my database in the most efficient way possible (the fewest queries, etc). Here is my very simple database structure:

reviewid smallint(4) auto_increment unique value
userid varchar(15)
eventname varchar(70)
reviewdate varchar(20)
numberscore smallint(3)
shortreview varchar(100)

The purpose of the database is to store ‘reviews’ of various community events from multiple users. One user may submit reviews of several events and each event may have many reviews. Below is a description of what each field represents:

reviewid = unique number that identifies each record
userid = number representing the person who submitted the review
eventname = name of the event being reviewed
reviewdate = time stamp equaling the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT). This is provided by the php Date() function.
numberscore = rating of event, a number from 1 to 10
shortreview = short phrase describing the event

I have a ‘menu’ set up in the following format:

EVENT | # OF REVIEWS | MOST RECENT REVIEW
event1 | 3 | date of most recent review shown here
event2 | 8 | date of most recent review shown here
event3 | 2 | date of most recent review shown here

The idea is to have the user click the name of the event in the menu above and all of the reviews posted for that particular event would be show. My problem is, to save space, I need to show in my menu only one line per event name. And for the name of each event in the menu, I want to extract only the most recent review date for that event while also counting the number of reviews for that particular event (that way, I can sort the entire list, ordered by the most recent review received, number of reviews received, etc). Is there a clever way to do this?

Thanks!