Page 1 of 4

PHP News Script

Posted: Fri May 23, 2003 10:02 am
by Gappa
I would like to create the news on my site based from what is posted in one of the forums on my site. An example of the output i am looking for is here: http://www.war3.com (this information is drawn from the database in reality i know, but the forum which yeilds this information is http://www.war3.com/forum/forumdisplay. ... forumid=29)

I know very little about doing this is some one could give me the base code to just adjust slightly according to my database etc, that would be great.. as would any help...

oh i am running phpBB2 2.0.4 if that information is needed...

cheers

Posted: Fri May 23, 2003 6:56 pm
by evilcoder
firstly, no-one here is going to make your the code. This is a help forum, we HELP with code that has already been attempted.

Posted: Sat May 24, 2003 12:50 am
by Gappa
ok thats fair enough... this is what i have (minus the changes I need to make to make it access the correct database, eg password)

index.php

Code: Select all

<?include_once "proteusBB.php"?>
<table border=1 width="80%"><tr>
    <td width="60%"><H1>BLA</H1>
    <td><table border=1>
        <tr><td><?titles()?>
        <tr><td><?titles()?>
    </table>
</table>
view.php

Code: Select all

<?include_once "proteusBB.php"?>
<table border=1 widht="80%"><tr>
    <td width="60%"><?display()?>
    <td><table border=1>
        <tr><td><?titles()?>
        <tr><td><?titles()?>
    </table>
</table>
proteusBB.php

Code: Select all

<?php

    $host="localhost";
    $user="root";
    $pass="";    
    $db="phpbb1";                       //  mysql database for phpBB
    $prefix="phpbb_";                    //    phpBB table prefix
    $forums&#1111;]=2;                        //    id of a forum to be monitored.
    $forums&#1111;]=1;                    //    how many is needed
    $limit=5;                        //    how many newest posts to show in view.php
    $viewer="view.php";                //    url do file that shows the selected topic

function init() &#123;
    global $host, $user, $pass, $db;
    mysql_connect($host, $user, $pass) or die("ze#1");
    mysql_select_db($db) or die("ze#2");
&#125;

function titles() &#123;
    global $prefix, $forums, $forum1, $limit, $viewer, $_GET;
    init();
    $r=mysql_query("select * from &#123;$prefix&#125;posts p, &#123;$prefix&#125;posts_text t where p.post_id=t.post_id "
    ."and p.forum_id=&#123;$forums&#1111;0+$forum1++]&#125; order by p.post_time desc limit ".($limit+$_GET&#1111;p]*100));
    while ($a=mysql_fetch_array($r)) echo "<a href='&#123;$viewer&#125;?p=&#123;$a&#1111;post_id]&#125;'> * &#123;$a&#1111;post_subject]&#125;</a><br>";
&#125;

function display() &#123;
    global $prefix, $_GET;
    init();
    $r=mysql_query("select * from &#123;$prefix&#125;posts_text where post_id=&#123;$_GET&#1111;p]&#125;");
    while (@$a=mysql_fetch_array($r)) echo "<h1>&#123;$a&#1111;post_subject]&#125;</h1><hr>&#123;$a&#1111;post_text]&#125;";
&#125;
    
?>
Ok yeah, I think its kind of on the right track, I prolly dont need to do it in three different files, but I'm not sure reall.. gah (in over my head here)

Posted: Sat May 24, 2003 7:00 am
by patrikG
And where is your problem?

Posted: Sun May 25, 2003 3:57 am
by Gappa
Well this is what I'm getting as a news page from that script...

http://www.hostultra.com/~Gappa/index.php

hardly looks right eh?

Posted: Mon May 26, 2003 8:06 am
by Gappa
Just a friendly *bump*

I'm still hungry for help on this, please dont hurt me :D

Posted: Mon May 26, 2003 9:18 am
by volka
you might either pull the data directly from the database (<prefix_>posts_text) or build a mod for phpBB

<prefix_>posts_text has the structure

Code: Select all

CREATE TABLE phpbb_posts_text (
  post_id mediumint(8) unsigned NOT NULL default '0',
  bbcode_uid varchar(10) NOT NULL default '',
  post_subject varchar(60) default NULL,
  post_text text,
  PRIMARY KEY  (post_id)
) TYPE=MyISAM;
so if you query e.g. the first 10 records ORDER BY post_id DESC it should work as you want... (at least I think so ;) )

Posted: Mon May 26, 2003 9:56 am
by Gappa
Ahh yes cheers..

but wont this just give me the last 10 posts (as in topics and there replies?) and it would be from all of my forums rather than just a selected one.

so im struggling to get the desired output and its hurting my poor poor brain.

(order of ten posts sorted by post_id) <- not sure.

post_subject "posted by" bbcode_uid @ time(hmm idea how to get that from just using that one table?I guess its not possible)

post_text


Have that repeat like 10 times i guess with out including replies...

I think im on the right track.. but its just not coming together.

Posted: Mon May 26, 2003 10:11 am
by volka
then you need the <prefix_>posts table, too
query all fields you want from both tables right joined on posts.post_id=posts_text.post_id where the forum_id of the posts-record is equal to the forum-id you want.

Posted: Mon May 26, 2003 10:16 am
by Gappa
Thanks for the help! YOur a champ.. but Im still not quite there... will what you just told me disclude replies to topics (in other words just having the original topic starters post?)

Posted: Mon May 26, 2003 1:35 pm
by volka
nope...
anything else? ;-)
if not, the table
CREATE TABLE phpbb_topics (
topic_id mediumint(8) unsigned NOT NULL auto_increment,
forum_id smallint(8) unsigned NOT NULL default '0',
topic_title char(60) NOT NULL default '',
topic_poster mediumint(8) NOT NULL default '0',
topic_time int(11) NOT NULL default '0',
topic_views mediumint(8) unsigned NOT NULL default '0',
topic_replies mediumint(8) unsigned NOT NULL default '0',
topic_status tinyint(3) NOT NULL default '0',
topic_vote tinyint(1) NOT NULL default '0',
topic_type tinyint(3) NOT NULL default '0',
topic_first_post_id mediumint(8) unsigned NOT NULL default '0',
topic_last_post_id mediumint(8) unsigned NOT NULL default '0',
topic_moved_id mediumint(8) unsigned NOT NULL default '0',
PRIMARY KEY (topic_id),
KEY forum_id (forum_id),
KEY topic_moved_id (topic_moved_id),
KEY topic_status (topic_status),
KEY topic_type (topic_type)
) TYPE=MyISAM;
can help you

Posted: Mon May 26, 2003 8:03 pm
by Gappa

Code: Select all

<HTML> 
<?php 
$db = mysql_connect("localhost", "****", "****"); 
mysql_select_db("Gappa",$db); 
$result = mysql_query("SELECT * FROM phpbb_topics",$db); 
echo "<TABLE>"; 
echo"<TR><TD><B>Latest News:</B></TR>";
while($myrow = mysql_fetch_array($result)) 
&#123; 
echo "<TR><TD><b><FONT FACE=Arial SIZE=-1 COLOR=#FF9900
>"; 
echo $myrow&#1111;"topic_title"];
echo "<FONT FACE=Arial COLOR=#666666 SIZE=-2></b> posted by ";
echo $myrow&#1111;"topic_poster"];
echo " @ ";
echo $myrow&#1111;"topic_time"];
echo "<TR><TD>"; 
echo "<TR><TD><FONT FACE=Arail SIZE=-1>"; 
echo "  "; 
echo "<TR><TD>";  
echo "<TR><TD>"; 
echo "<TR><TD>"; 
&#125; 
echo "</TABLE>"; 
?> 
</HTML>
Ok this is what I got going now... need to limit it to only look at forum_id=6 not sure how and topic_time and topic_poster just output a number (as in the user's id number).

And the more vital thing is getting the actual text of the post for the topic, not just the title. (NOTE: I dont want the replies, just the topic and its post body)..

any ideas?

Posted: Mon May 26, 2003 8:21 pm
by volka
you want the fields
topic_title, topic_time, post_text and username
from the tables
phpbb_topics, phpbb_posts_text and phpbb_users
and want it to be joined on the following conditions
topic_first_post_id corresponds to post_id
topic_poster refers to the user_id

I suggest two right joins (which might be inefficient; after all I'm not that good at sql ;) )
http://www.mysql.com/doc/en/JOIN.html

Posted: Mon May 26, 2003 9:59 pm
by Gappa
gah loooks like i'm gonna have to go read the php fpor couple of... hours... days.. weeks haha.... I know nothing bout linking table and conditional statements and linking in php...

Posted: Mon May 26, 2003 10:48 pm
by volka
it's not about php but sql.
Although copy&paste code isn't very popular here, I make an exception ;)

Code: Select all

<table border="1"><?php

$dbConn = ... ; // mysql-connect code here

$query = 'SELECT topic_title, topic_time, post_text, username FROM phpbb_topics RIGHT JOIN phpbb_posts_text ON topic_first_post_id=post_id RIGHT JOIN phpbb_users ON topic_poster=user_id WHERE forum_id=6';
$result = mysql_query($query, $dbConn) or die ($query .': '.mysql_error());
while($row = mysql_fetch_assoc($result))
{
	echo '<tr>';
	foreach($row as $field)
		echo '<td>', $field, '</td>';
	echo '</tr>';
}

?></table>
Check wether phpbb_ is really the prefix of your tables.

But I urge you to try to understand the query. Maybe you'll find a better one. Start at http://www.mysql.com/doc/en/JOIN.html