Page 1 of 1

need help with phpbb forums... more php problem

Posted: Sun Jul 11, 2004 2:44 pm
by John Cartwright
Okay I know someone is going to say go to their support forums but they are noobs I already tried them :P

Its more of a logic problem than a phpbb problem..

here it is... I'm trying to display all the news topics on my main page... so it acts like news.. and this is the query I'm using... its obviously not correct.. i'll explain later. I'll also give you the structure of each table in a sec.

Code: Select all

<?php
result = @mysql_query("SELECT DISTINCT * FROM phpbb_posts, phpbb_posts_text, phpbb_topics, phpbb_forums WHERE forum_name='news'") or die('Error performing query: '. mysql_error());	
	
?>
At the moment, I have 2 topics and 1 comment inside 1 of the topics.

and this is the result:

It displays each post 3 times in sequence, and then again.
So I get 18 different posts on my main page.

Let me summarize sort of what I need:
I need it to loop through each topic and get the ids and then print them out... making sure not to include the comments in the actual post.

Now let me give you the structure of the db:

phpbb_forums table
forum_id smallint(5) UNSIGNED No 0
cat_id mediumint(8) UNSIGNED No 0
forum_name varchar(150) Yes NULL
forum_desc text Yes NULL
forum_status tinyint(4) No 0
forum_order mediumint(8) UNSIGNED No 1
forum_posts mediumint(8) UNSIGNED No 0
forum_topics mediumint(8) UNSIGNED No 0
forum_last_post_id mediumint(8) UNSIGNED No 0
prune_next int(11) Yes NULL
prune_enable tinyint(1) No 0
auth_view tinyint(2) No 0
auth_read tinyint(2) No 0
auth_post tinyint(2) No 0
auth_reply tinyint(2) No 0
auth_edit tinyint(2) No 0
auth_delete tinyint(2) No 0
auth_sticky tinyint(2) No 0
auth_announce tinyint(2) No 0
auth_vote tinyint(2) No 0
auth_pollcreate tinyint(2) No 0
auth_attachments

phpbb_posts table

post_id mediumint(8) UNSIGNED No auto_increment
topic_id mediumint(8) UNSIGNED No 0
forum_id smallint(5) UNSIGNED No 0
poster_id mediumint(8) No 0
post_time int(11) No 0
poster_ip varchar(8) No
post_username varchar(25) Yes NULL
enable_bbcode tinyint(1) No 1
enable_html tinyint(1) No 0
enable_smilies tinyint(1) No 1
enable_sig tinyint(1) No 1
post_edit_time int(11) Yes NULL
post_edit_count smallint(5) UNSIGNED No 0


phpbb_posts_text table

post_id mediumint(8) UNSIGNED No 0
bbcode_uid varchar(10) No
post_subject varchar(60) Yes NULL
post_text text Yes NULL

phpbb_topics

topic_id mediumint(8) UNSIGNED No auto_increment
forum_id smallint(8) UNSIGNED No 0
topic_title char(60) No
topic_poster mediumint(8) No 0
topic_time int(11) No 0
topic_views mediumint(8) UNSIGNED No 0
topic_replies mediumint(8) UNSIGNED No 0
topic_status tinyint(3) No 0
topic_vote tinyint(1) No 0
topic_type tinyint(3) No 0
topic_first_post_id mediumint(8) UNSIGNED No 0
topic_last_post_id mediumint(8) UNSIGNED No 0
topic_moved_id mediumint(8) UNSIGNED No 0


I know this is a lot to ask but can someone PLEASE help me :)
I will be very thankful and forever in your dept.


feyd | tweaked the display to lose the smilies where we'd like to see the text..

ty feyd

Posted: Sun Jul 11, 2004 3:12 pm
by feyd
btw...

Code: Select all

SELECT  * FROM phpbb_posts a
LEFT JOIN phpbb_posts_text b ON a.post_id = b.post_id
LEFT JOIN phpbb_topics c ON a.topic_id = c.topic_id
LEFT JOIN phpbb_forums d ON a.forum_id = d.forum_id
WHERE d.forum_name =  'news' GROUP BY a.topic_id

Posted: Sun Jul 11, 2004 3:25 pm
by feyd
this may be simpler to use:

Code: Select all

SELECT  `topic_title` `title`, `post_text` `text`, COUNT( a.post_id )-1 AS `num_comments`, `bbcode_uid`, `poster_id`, `post_username` `poster_name`
FROM phpbb_posts a
LEFT  JOIN phpbb_posts_text b ON a.post_id = b.post_id
LEFT  JOIN phpbb_topics c ON a.topic_id = c.topic_id
LEFT  JOIN phpbb_forums d ON a.forum_id = d.forum_id
WHERE d.forum_name =  'The Basics'
GROUP  BY a.topic_id

Posted: Sun Jul 11, 2004 3:29 pm
by John Cartwright
wow thanks feyd u da man :P