Page 1 of 1

Differentiating between the first post and a reply in phpbb3

Posted: Sat Jun 21, 2008 4:01 am
by eulogy
Hello!

I have another thread, here, but this is a different subject so I decided to make a new one.

Basically what I'm trying to do is make an index.php where the first post in every topic in a phpbb3 forum is printed.

I messed up and didn't realize that this would post replies as well, but I still want them enabled in the forum.

So, I need a way to only print the first post in a topic. I keep getting ideas, or trying to put how I personally can look at the database and tell if a post is the first one or not into code, but it's just too confusing. Ex: "When there's 2 or more rows with the same topic_id's, the lowest post_id is going to be the first post."

Also, just for clarification, post_id increments when a new post AND topic is made, topic_id increments only when a new topic is made.

I'm sure there's a way to not even query the replies... any ideas?

Here is my code;

Code: Select all

<?PHP
//Info for connection to the mysql database
    $user_name = "root";
    $password = "";
    $database = "phpbb3";
    $server = "127.0.0.1";
 
//Command to connect to the server and select the database.
    mysql_connect($server, $user_name, $password);
    mysql_select_db ($database);
 
// Querying the database for news posts, then sorting it into an array for printing.
    $SQL = 'SELECT forum_id, post_time, poster_id, post_subject, post_text, post_id, topic_id FROM phpbb_posts ORDER BY topic_id DESC';
    $result = mysql_query($SQL) or die(mysql_error());
    $db_field = 'mysql_fetch_assoc($result)';
    
// Setting the default timezone for timestamps in news posts.
    date_default_timezone_set("EST");
 
 
// Actually printing the data in the specified fields
 
 
    while ($db_field = mysql_fetch_assoc($result))
    if ($db_field['forum_id'] == 4)
 
    {
 
 
    if ($db_field['poster_id'] == 2)
        ($postername = "eulogy1337");
 
    if ($db_field['poster_id'] == 53)
        ($postername = "Jersh");
 
//Top row, subject of post here
    print "<table border=1><center>";
    print "<tr><td>";
    print $db_field['post_subject'];
    print "</td></tr>";
    print "<tr><td>";
 
// Middle row, left cell
 
    // Hour:minutes | am/pm | timezone.  ex: 12:30 am EST
    print date('g:i a T', $db_field['post_time']) . " - ";
 
    // the poster's name
    print $postername;
    print "</td>";
 
 
// Middle row, end leftmost cell, beginning right cell
    print "<td>";
 
    // 3-letter day, Month, Date, year.  ex: Sat June 21, 2008
    print date('D F j, Y', $db_field['post_time']);
 
    print "</td></tr>";
// Ending middle row, beginning bottom cell
    print "<tr><td>";
    print $db_field['post_text'];
    print "</td></tr></table><br><br>";
    print "</center>";
    }
 
?>

To get a better idea, here's a picture of the database. I've circled the 'reply' entries in red; the ones that need to not be printed.

I'm new to PHP and I don't quite know how I can refer to different rows when trying to print a specific row, in order to see if it should be printed or not...

When you take post_id and subtract topic_id, the replies will always be a higher difference than the first post for that topic_id.
The only issue with me trying to use this statement in an if function is that I don't know how to compare it to other rows in the database.

If this gets too complicated I'll hide the forum that news are taken out of, and make a copy of all the posts where normal visitors can comment..

Re: Differentiating between the first post and a reply in phpbb3

Posted: Sat Jun 21, 2008 4:08 am
by Kieran Huggins
maybe something like:

Code: Select all

SELECT topic_id, MIN(post_id) AS post_id FROM posts GROUP BY topic_id

Re: Differentiating between the first post and a reply in phpbb3

Posted: Sat Jun 21, 2008 2:10 pm
by eulogy
That worked! Thank you!

Would anyone mind explaining why though?

Re: Differentiating between the first post and a reply in phpbb3

Posted: Sat Jun 21, 2008 2:16 pm
by Zoxive
eulogy wrote:That worked! Thank you!

Would anyone mind explaining why though?
If you look at the query..

Code: Select all

 SELECT topic_id, MIN(post_id) AS post_id FROM posts GROUP BY topic_id
and Dissect it.. post_ids are auto_incremented, so by using GROUP BY along with the MIN function to get the smallest post_id, you will get the 1st post.

Re: Differentiating between the first post and a reply in phpbb3

Posted: Sat Jun 21, 2008 6:31 pm
by Kieran Huggins
GROUP BY was something that took me a while to really understand too, it's hard to visualize. I'll try to explain:

GROUP BY is only really useful when using another function, like MIN() or MAX() - it changes the scope of those functions the rows you're grouping. So if we select all the topic_id and post_id colums, the out put would look like this:

Code: Select all

topic_id          post_id
1                 1
1                 2
1                 3
2                 4
2                 5
2                 6
if we just applied the MIN() to post_id, it would always return the smallest post_id, like so:

Code: Select all

topic_id          post_id
1                 1
1                 1
1                 1
2                 1
2                 1
2                 1
however, if we then grouped by the topic_id, then it would treat each group of rows with the same topic_id as a set, applying MIN() only to the rows in each set, returning one row for each set (or "group", really):

Code: Select all

topic_id          post_id
1                 1
2                 4
so in the query you're running, it groups rows into sets that share the same topic_id (into forum threads) and looks for the smallest post_id in each set.

Does that makes sense?

Here's a list of functions that are affected by grouping:
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html

Re: Differentiating between the first post and a reply in phpbb3

Posted: Sat Jun 21, 2008 6:41 pm
by califdon
Great explanation of grouping, Kieran!

Re: Differentiating between the first post and a reply in phpbb3

Posted: Sat Jun 21, 2008 8:07 pm
by eulogy
Thank you Kieran! I understand it now.

Re: Differentiating between the first post and a reply in phpbb3

Posted: Sun Jun 22, 2008 2:40 am
by Kieran Huggins
wicked 8)

maybe I'll take a crack at JOINS someday - they can also be quite confusing, but are totally rad.