Manipulizing PHPBB...

[joels]

Hi, I was wondering if someone could tell me how you specify a certain topic with it's text if its in two seperate tables on my database (phpBB). I have a section of my forums designated for admins only to post recent news, and I want the newest 5 topics to come up on my homepage as news tables, but I dont know the mysql code to associate the topic id with post text because it is on two seperate tables. So, here's the deal:

[list]
[*]Table [b]phpbb_posts[/b] has the fields post_id, topic_id, forum_id, poster_id, post_time, poster_ip, post_username, enable_bbcode, enable_html, enable_smilies, enable_sig, post_edit_time, post_edit_count, and post_attachment.
[*]Table [b]phpbb_posts_text[/b] has fields post_id, bbcode_uid, post_subject, and post_text.
[*]I want to have all topics in forum_id 1 to display on my homepage ordered by descending but without its associated replies. What is the mysql code to link this information?
[/list]

_________________________________________________________________________________

Structure for homepage news table:

post_subject (the post's subject)
post_text (the post text submitted)
post_comments (how many replies)

_________________________________________________________________________________

So how would I do this as far as MySQL coding goes? Let me know if you need more information. Thanks!

[redarrow]

If you goto the main website of the forum there meny member submitted hacks try ok.

[joels]

Trust me, I've already looked up and down the PHPBB hacks and mods...too complicated. I want a basic code...you know how phpbb can get.

[redarrow]

what do you want to display the comments or the link to comments or both.

i get it know are you trying to display example the new posts from the forum on your index page.

so emample if a user posts a new post to the forum the link and some text of the post is on the index page of your website.

[printf]

Do you have a db scheme, I'm to lazy to go and get one, if you upload a empty db scheme, I'll give you the query to do it.!

me!

[joels]

Redarrow, it's similar to what you said, but I want it to display the whole post text of every topic in forum_id 1. A good example I found of what I want is at [url=http://www.complexityclan.com/]http://www.complexityclan.com/[/url]. Scroll down to the news part of the homepage. You can see that he uses the newest 3 topics from the Complexity Clan News area on the forums.  Thats exactly what I want. The scheme I am using is mysql.

[mtylerb]

[code]SELECT * FROM phpbb_topics WHERE forum_id = 1 ORDER BY topic_id DESC LIMIT 0,5;[/code]

That would give you the last 5 topics in forum_id 1 with the newest topic at the top.

[code]SELECT * FROM phpbb_topics WHERE forum_id != 1 ORDER BY topic_id DESC LIMIT 0,5;[/code]

That would give you the last 5 topics in every forum except the one with forum_id 1.  Again, the newest topic is at the top.  You could use this to eliminate the admin forum.

If you want the newest topics at the bottom, change DESC to ASC.  You could add further forums to read or eliminate with AND.

[code]SELECT * FROM phpbb_topics WHERE forum_id != 1 AND forum_id != 4 ORDER BY topic_id DESC LIMIT 0,5;[/code]

That would give you the last 5 topics in every forum except the one with forum_id 1 AND 4.

The topic titles are stored in there, so there's no need to jump from one table to another.  You aren't posting the text from each individual post?  Just the topic title and a link to that topic (that's all the complexityclan.com site is doing).

If you did want to post text from each post, then you'd save the topic_id to a php variable (in this case $somevar) and use something like:

[code]<?php

$sql = mysql_query('SELECT * FROM phpbb_post_text WHERE topic_id = "' . $somvar . '" ORDER BY post_id DESC LIMIT 0,1;');

?>[/code]

That should give you the last post in the specified topic.

[joels]

[quote author=mtylerb link=topic=110815.msg448654#msg448654 date=1160252026]

If you did want to post text from each post, then you'd save the topic_id to a php variable (in this case $somevar) and use something like:

[code]<?php

$sql = mysql_query('SELECT * FROM phpbb_post_text WHERE topic_id = "' . $somvar . '" ORDER BY post_id DESC LIMIT 0,1;');

?>[/code]

That should give you the last post in the specified topic.
[/quote]
the field 'topic_id' doesnt exist in the table 'phpbb_posts_text' - only post_id - thats why i get so confused. And if you look almost all the way to the bottom left, you will see the latest 3 news topics and their associated texts on complexityclan.com .

[joels]

bump...

[joels]

Anybody?...

[thirdpersonmatt]

Well I don't know if I'm understanding this fully or not but shouldn't you just be able to query to phpbb_posts table to get the most recent topic ids and post ids then use those to display the posts?

Would look something like this:

[code]
$topic_query = mysql_query('SELECT post_id, topic_id FROM phpbb_posts ORDER BY post_id DESC LIMIT 0,3');

while($topic_row = mysql_fetch_array('$topic_query'){

  $current_pid = $topic_row['post_id'];
  $post_query = mysql_query('SELECT * FROM phpbb_posts_text WHERE post_id = $current_pid');
     
     
    $post_info = mysql_fetch_object($post_query);

      echo "<p>$post_info->post_subject</p>";
      echo "<p>$post_info->post_text</p>";

}
[/code]

Then you could use whatever way you want to display the information. You'd have to decide how you want to set up the tables to output the data and everything like that. Anyway, I hope that code makes sense and works, it's late, and my PHP knowledge is kind of limited.