[Resolved] A Simple Forum topic listing

[kcgame]

I have the following table as attached. Please ignore the realm_id column. The only columns of concerned are id, parent_id, child_id, des, name, create_t and author.
parent_id is the primary topic number.
child_id is the reply number (higher means latest post) to that particular topic. 1 means its the first post of the topic.
des is the message itself.
nameis the topic title
create_t is the create time of the topic / reply
author is the creator of the topic / reply

How can i display in a table form accordingly from lastest post to oldest? My displayed table looks like:

Topic | Posts | Last Poster | Last Date Posted

where topic is the topic name
Posts is the number of posts in that topic (count from child_id)
Last poster is the author of the lastest poster of the topic (search for largest child_id)
Last Date Posted is the date created for the post.

I've a general concept but just can't seems to get my code (very complicated and messy:queasy: )working. Think there should be a more organised method to this problem..
Any help / comment is appreciated. Thanks 🙂

[bpat1434]

$query = "SELECT * FROM `table_name` WHERE post_id = '$post_id'";
while($row = mysql_fetch_array(mysql_query($query)))
{
    echo "<tr>";
    echo "<td>".$row['name']."</td>";
    echo "<td>".mysql_num_rows($row($query)), 50)."</td>";
    echo "<td>".$row['author']."<br>".
    $row['create_t']."</td>";
    echo "</tr>";
}

That shoudl get you what you want.

~Brett

[kcgame]

Hi Brett,
Thanks for your prompt reply, but i have some doubts from your code😕
You make a query where post_id = '$post_id' what is this post_id supposed to be?

Maybe i should express myself clearer in the problem.. With reference to my attachment database table, the resulted table should show only two topics, with 'War Suggestions' on top of 'Hello'.

Hope that i didn't confuse anyone:rolleyes:

[kcgame]

Anyone that can help me?🙁

[liquorvicar]

SELECT parent_id,name,MAX(child_id), MAX(create_t) FROM table GROUP BY parent_id ORDER BY create_t

i think that should achieve what you're after.

btw, any reason why you have the topic parent_id and name in your table? why not remove the name and have the name in a separate table?

[bpat1434]

I don't think that's going to get what he wants. This might though:

<?php
$qry = "SELECT * FROM `table_name` GROUP BY parent_id ORDER BY id DESC";

$rslt = mysql_query($qry);

while($row = mysql_fetch_array($rslt))
{
    echo $row['name'].' | '.$row['des'].' | '.$row['create_t'].' | '.$row['Author'].'<br>';
}
?>

Should output:

War Suggestions | Attack who?? | 2005-04-17 15:29:12 | Minister
Hello | Hello lah... | 2005-04-17 15:29:11 | Forgiver

The group by syntax will make as many distinct groups as there need to be. Since I can only see 2 distinct groups of parent_id, then, there will only be two rows returned. You can see this here:
Test Grouping

Hope that helps.

~Brett

[kcgame]

Thanks for the reply, unforunately it didn't work out..
Actually the desired query should:
1. Select all Max child_id in each parent_id.
2. Sort them according to the lastest.

liquorvicar is close to what i want..but the name i get is not the one from MAX(child_id)??

Think this is tougher than what i expected..

[liquorvicar]

not quite sure exactly what you're after.
using my SQL i get:
parent_id,name,MAX(child_id),MAX(create_t)
1,Hello,3,2005-04-17 15:28:11
2,War suggest,1,2005-04-17 15:29:15

what did you want to get?

[bpat1434]

You're making this unnecessarily hard on yourself. By choosing the Group By method, it groups it according to what you tell it. You group it by the child_id column so you don't have more than 1 response for each specific child_id value.

Then, you order by id to make it so that the highest child_id is at the top. You can order it again by date so that the entire list is re-ordered by date.

It seems you're trying to take the hard route.

~Brett

[kcgame]

Originally posted by liquorvicar
not quite sure exactly what you're after.
using my SQL i get:
parent_id,name,MAX(child_id),MAX(create_t)
1,Hello,3,2005-04-17 15:28:11
2,War suggest,1,2005-04-17 15:29:15

what did you want to get?

My apologises to you guys if i'm confusing the whole matter..
The following is what i want to get:

Topic | Posts | Last Poster | Last Date Posted
War suggestions | 1 | Minister | 15:29:12
Hello | 3 | Forgiver | 15:28:11

Sort according to latest.:rolleyes:

liquorvicar, i didn't get the ouput you've gotten..i'll check that again once i'm home tonight. I tried the code you given me by adding as to appear MAX(child_id) as Child . Then i print out Child value..think that won't change the result much..

As for Brett, i'm using Group By to get one response from every parent_id. That one response is MAX(child_id). I think i'll continue to mess up everyone's concept if i keep explaining in words. Please see the table i've included in this post to get the whole picture of my objective.
I appreciate every single help that you guys have contributed. Hope to hear from you soon🙂

[bpat1434]

Actually, I'll yield to Liqour on this one. I got his output as well.

Working Example

~Brett

[liquorvicar]

ok, try sorting the other way (stupid omission on my part from my original post0

SELECT parent_id,name,MAX(child_id), MAX(create_t) FROM table GROUP BY parent_id ORDER BY create_t DESC

note the DESC at the end which sorts create_t in descending order not ascending order as my SQL did originally

[kcgame]

Thanks for the help liquorvicar and bpat1434 have been providing.
I've managed to get the following code from my another database guru that works wonder:

select id
     , p_id
     , c_id
     , Des
     , topic
     , create_t
  from yourtable as foo
 where create_t
     = ( select max(create_t)
           from yourtable 
          where p_id = foo.p_id )
ORDER BY create_t DESC

Hope to learn more from you guys again

[bpat1434]

Only thing I have to say is that it seems a bit redundant running two queries which will get you the same that the one Liquor and I have given you.

~Brett