[SOLVED] List 1 record from multiple records linked by date in SQL while loop

[r00ttap]

Maybe I'm having a brain fart here but I can't seem to figure this one out. I have a ridiculous SQL query that is working as I need but the problem lies in how I want the information sorted.

 

Here's the scenario; I have multiple records in two different SQL tables linked by id, messages and dates. There are multiple dates for each message. I want to only list each message once, not for each record I have.

 

My SQL satement looks something like this:

 

SELECT * FROM messages, messages_dates 
WHERE campus = 'Campus' 
AND messages.id = messages_dates.id 
AND CONCAT((SELECT MAX(date) FROM mesaages_dates 
WHERE messages.id = messages_dates.id),' ',end_time) >= NOW() 
ORDER by date, start_time

 

This gives me exactly what I want, all messages that have a date greater than today are listed, after today they disappear. But I believe my problem lies in my PHP code.

 

while($row = mysql_fetch_assoc($query)){
        echo "$row[date] $row[message]" <br />;
}

 

This will list something similar to this:

 

2009-06-19 This is the same message

2009-06-22 This is the same message

2009-06-23 This is the same message

2009-06-25 This is the same message

2009-06-27 This is the same message

2009-06-28 This is the same message

 

I only want one message listed however I still need all the dates so they can be posted in a "pop-up" persay. I ultimately want something like this:

 

2009-06-19 to 2009-09-28 This is the same message [click here for more info]

 

And when you click the info it will list all the dates.

 

My main question is, how do I only list one message per "id" without SQL's "LIMIT 1" and retaining all the dates? Should I go about this a different way?

[flyhoney]

I think GROUP BY is what you are looking for

 

SELECT * FROM messages, messages_dates
WHERE campus = 'Campus'
AND messages.id = messages_dates.id
AND CONCAT((SELECT MAX(date) FROM mesaages_dates
WHERE messages.id = messages_dates.id),' ',end_time) >= NOW()
GROUP BY messages.id
ORDER by date, start_time

[Psycho]

Well, the solution depends on how you plan to implement the pop-up. Are you going to generate the content for the pop-up on-the-fly by passing the message ID and running a new query OR do you want to have the conent for the pop-up preloaded using this query?

 

If you want to generate the code for the pop-up dynamically when the link is clicked, then you want to use a GROUP BY clause on this first query.

 

If you want to preload the content for the popup then stick with your existing query and implement PHP logic to only display each message once.

 

Example:

$current_msg_id = '';

while($row = mysql_fetch_assoc($query))
{
    if ($current_msg_id!==$row['id'])
    {
        echo "$row[date] $row[message]" <br />;
        $current_msg_id = $row['id'];
    }
}