Jump to content


Photo

Manipulizing PHPBB...


  • Please log in to reply
10 replies to this topic

#1 joels

joels
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 07 October 2006 - 05:37 PM

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:

  • Table phpbb_posts 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 phpbb_posts_text 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?

_________________________________________________________________________________

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!

#2 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 07 October 2006 - 05:39 PM

If you goto the main website of the forum there meny member submitted hacks try ok.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#3 joels

joels
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 07 October 2006 - 06:27 PM

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.

#4 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 07 October 2006 - 06:33 PM

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.


Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#5 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 07 October 2006 - 06:37 PM

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!

#6 joels

joels
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 07 October 2006 - 07:17 PM

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 http://www.complexityclan.com/. 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.

#7 mtylerb

mtylerb
  • Members
  • PipPipPip
  • Advanced Member
  • 69 posts
  • LocationEdmonton, AB, Canada

Posted 07 October 2006 - 08:13 PM

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

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

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

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.

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

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:

<?php

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

?>

That should give you the last post in the specified topic.
I may be useless, but I'll try anyway!

The name is pronounced Em Tyler Bee,
But just Tyler will do nicely!

#8 joels

joels
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 07 October 2006 - 09:48 PM


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:

<?php

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

?>

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

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 .

#9 joels

joels
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 08 October 2006 - 01:52 AM

bump...

#10 joels

joels
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 08 October 2006 - 06:45 AM

Anybody?...

#11 thirdpersonmatt

thirdpersonmatt
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 26 October 2006 - 05:56 AM

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:

$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>";

}

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.

your pal,
Matt




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users