grouping and displaying

robsgaming

i've coded my own discussion boards. there is a table called MESSAGES in which all messages are posted of course. what i'm trying to do is build a script that will show what topics a user is active in...

the table messages includes topicsubject which has a value for every entry

i want it to group the messages by topsubject, and show the very last post (msgtime)

here is the code i have now:


<?
echo "<table>";

mysql_select_db("rgboards",$db);
$query = "SELECT * FROM messages WHERE msgposter = '$loginuser' ORDER BY topicsubject";
$result = MYSQL_QUERY($query);

$i=0;
while ($row = mysql_fetch_array ($result)) { 
extract($row);

$area=$topicsubject;
if ($TempArea <> $topicsubject) {


print "
<tr><td width=\"230\" bgcolor=#FBC42C><B>$topicsubject</B></td><td>$msgtime</td></tr>

";
$TempArea = $area ;
}

$i++;

}

print "</table>";

?>

bascially whats its doing is grouping all the topic subjects together, so it doesn't display more than one if that makes sense.

i want to sort them by the msg time in descending order

however, if i put order by msgtime, it will mess up and show more than one instance of the topic subject

hopefully this makes sense what i'm trying to do
i can never explain stuff good

altexis

I see what you 're up to....

If I were you I would make a seperate table with the topics and keep the table you are talking about as a child table keeping all the chat data. I would also store the latest update date/time in the topics table... so this way the sorting you are looking for would be 100% straight forward 🙂

josgood

What you are trying to do is mutually exclusive.

You are trying to ask the database questions it can't answer. lets say you have the following
Post User Subject TimeStamp
Post1 User1 Subject1 12:30
Post2 User1 Subject1 12:35
Post1 User1 Subject2 14:50
Post2 User1 Subject2 17:35
Post1 User1 Subject3 18:30
Post1 User2 Subject1 12:37
Post2 User2 Subject1 12:45
Post1 User2 Subject2 14:57
Post2 User2 Subject2 17:55
Post1 User2 Subject3 18:56

you want to output that

User1 is active in Subject1 Subject2 and Subject3 and you want to order his active subjects by when he was active in them but you only want to list the most recent post per subject. So you would need to query by user then order them by subject and then by timestamp and out put them into a temporyary table which you query and order by subject and then descending timestamp and then you take the first record and trash the dupe subjects with different time stamps after it till you get to the next subject

so you would query the table for records where user=$loginname in this case lets say user1 and output that to a temp table or a static table however you want to do it(you could use a temp table named after their sesssionID) and you would end up with a temp table like this
Post User Subject TimeStamp
Post1 User1 Subject1 12:30
Post2 User1 Subject1 12:35
Post1 User1 Subject2 14:50
Post2 User1 Subject2 17:35
Post1 User1 Subject3 18:30

then you query it and order by subject and descending timestamp and get out put like this

Post User Subject TimeStamp
Post2 User1 Subject1 12:35
Post1 User1 Subject1 12:30
Post2 User1 Subject2 17:35
Post1 User1 Subject2 14:50
Post1 User1 Subject3 18:30

the Post1 and Post2 and so on is the post text so it isnt usefull to order by

after you get that out put you use your php code to keep the first result for subject one and trash the rest just like you have it set up now and then so on for all the rest of the subjects

this is the only way i see to do it right now but there may be a better way and there may also be a way to do it all in one query without using another table
you could also set up a new field to represent a users most recent post in a subject as in when you write out there post update all other fields where user=username and subject=subject set recent=no and then put there newest post in with recent=yes and then when you query it you just have it return only a users recent post for a subject (where user=username and recent=yes and that should also work although i havnt tried it)

hope this helps
Jeff

altexis

just figured out an idea you could use straight away! 🙂

i will assume that your table has a field holding the date of each post called FDate

rewrite your query like this:
$query = "SELECT topicssubject, MAX(FDate) FROM messages WHERE msgposter = '$loginuser' GROUP BY topicsubject";

this will give you a list with distinct topics and the latest date for each one. you can then sort the dates and easily echo the results 🙂

robsgaming

worked perfect, thanks 🙂

http://www.robsgaming.com/testing/activeposts.php?loginuser=garciaowens

is an example