Page 1 of 1

CODE for latest topics on external page

Posted: Sat Aug 07, 2010 1:04 pm
by Mary17
Hey there,

So I've been looking for this for a long time, and finally could make it work, with a code I found on http://www.hawkee.com/snippet/5344/ here it is:

Code: Select all


<?php
    // How Many Topics you want to display?
    $topicnumber = 5;
    // Change this to your phpBB path
    $urlPath = "/phpbb";
 
    // Database Configuration (Where your phpBB config.php file is located)
    include 'phpbb/config.php';
 
    $table_topics = $table_prefix. "topics";
    $table_forums = $table_prefix. "forums";
    $table_posts = $table_prefix. "posts";
    $table_users = $table_prefix. "users";
    $link = mysql_connect("$dbhost", "$dbuser", "$dbpasswd") or die("Could not connect");
    mysql_select_db("$dbname") or die("Could not select database");
 
    $query = "SELECT t.topic_id, t.topic_title, t.topic_last_post_id, t.forum_id, p.post_id, p.poster_id, p.post_time, u.user_id, u.username
    FROM $table_topics t, $table_forums f, $table_posts p, $table_users u
    WHERE t.topic_id = p.topic_id AND
    f.forum_id = t.forum_id AND
    t.forum_id != 4 AND
    t.topic_status <> 2 AND
    p.post_id = t.topic_last_post_id AND
    p.poster_id = u.user_id
    ORDER BY p.post_id DESC LIMIT $topicnumber";
    $result = mysql_query($query) or die("Query failed");									
 
    print "<table cellpadding='3' cellSpacing='2' width='640'>";
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
 
    echo  "<tr valign='top'><td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\"><font color=\"#FFCC00\"><b><a href=\"$urlPath/viewtopic.php?f=$row[forum_id]&t=$row[topic_id]&p=$row[post_id]#p$row[post_id]\" TARGET=\"_blank\">" .
    $row["topic_title"] .
    "</a></td></font></b><td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\"><font color=\"#C0C0C0\"> by: <a href=\"$urlPath/memberlist.php?mode=viewprofile&u=$row[user_id]\" TARGET=\"_blank\">" .
    $row["username"] .
    "</td><td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\"><font color=\"#C0C0C0\">" .
    date('F j, Y, g:i a', $row["post_time"]) .
    "</td></tr></font>";
    }
    print "</table>";
    mysql_free_result($result);
    mysql_close($link);
    ?>

I thought to share it with so many others who are looking for it. It's extremly useful to apply. I use frontpage, I just created a new layer and copied this code in there, and it worked. Some images didn't appear but it was just a matter of adjusting "it's adress" (like on my documents...).


I have a doubt, which is:
On the page http://7cr9.com/2.php there are shown the latest topics, authors, and the date it was published.

I wanted to show only the topic title, and not the author or date. Could you tell me which part of the code should I delete or change to do so?


Thanks in advance,
Mary

Re: CODE for latest topics on external page

Posted: Sat Aug 07, 2010 1:39 pm
by Sicle
Try replacing:

Code: Select all

echo  "<tr valign='top'><td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\"><font color=\"#FFCC00\"><b><a href=\"$urlPath/viewtopic.php?f=$row[forum_id]&t=$row[topic_id]&p=$row[post_id]#p$row[post_id]\" TARGET=\"_blank\">" .
    $row["topic_title"] .
    "</a></td></font></b><td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\"><font color=\"#C0C0C0\"> by: <a href=\"$urlPath/memberlist.php?mode=viewprofile&u=$row[user_id]\" TARGET=\"_blank\">" .
    $row["username"] .
    "</td><td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\"><font color=\"#C0C0C0\">" .
    date('F j, Y, g:i a', $row["post_time"]) .
    "</td></tr></font>"; 
With:

Code: Select all

echo  "<tr valign='top'><td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\"><font color=\"#FFCC00\"><b><a href=\"$urlPath/viewtopic.php?f=$row[forum_id]&t=$row[topic_id]&p=$row[post_id]#p$row[post_id]\" TARGET=\"_blank\">" . $row["topic_title"] . "</a></b></font></td></tr>"; 

Re: CODE for latest topics on external page

Posted: Sat Aug 07, 2010 5:22 pm
by Mary17
Hey thanks for the answer.

So I did the changes you suggested and the code look like this now:

Code: Select all

 <?php
        // How Many Topics you want to display?
        $topicnumber = 5;
        // Change this to your phpBB path
        $urlPath = "/phpbb";

        // Database Configuration (Where your phpBB config.php file is located)
        include 'phpbb/config.php';

        $table_topics = $table_prefix. "topics";
        $table_forums = $table_prefix. "forums";
        $table_posts = $table_prefix. "posts";
        $table_users = $table_prefix. "users";
        $link = mysql_connect("$dbhost", "$dbuser", "$dbpasswd") or die("Could not connect");
        mysql_select_db("$dbname") or die("Could not select database");

        $query = "SELECT t.topic_id, t.topic_title, t.topic_last_post_id, t.forum_id, p.post_id, p.poster_id, p.post_time, u.user_id, u.username
        FROM $table_topics t, $table_forums f, $table_posts p, $table_users u
        WHERE t.topic_id = p.topic_id AND
        f.forum_id = t.forum_id AND
        t.forum_id != 4 AND
        t.topic_status <> 2 AND
        p.post_id = t.topic_last_post_id AND
        p.poster_id = u.user_id
        ORDER BY p.post_id DESC LIMIT $topicnumber";
        $result = mysql_query($query) or die("Query failed");                           

        print "<table cellpadding='3' cellSpacing='2' width='640'>";
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

       echo  "<tr valign='top'><td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\"><font color=\"#FFCC00\"><b><a href=\"$urlPath/viewtopic.php?f=$row[forum_id]&t=$row[topic_id]&p=$row[post_id]#p$row[post_id]\" TARGET=\"_blank\">" . $row["topic_title"] . "</a></b></font></td></tr>"; 
        }
        print "</table>";
        mysql_free_result($result);
        mysql_close($link);
        ?>
The problem is that now the last topics thing doesn´t work anymore, it gives the error: Could not connect as you can see in http://7cr9.com/2.php .

Thanks,
Mary

Re: CODE for latest topics on external page

Posted: Sat Aug 07, 2010 5:29 pm
by Mary17
Mary17 wrote:Hey thanks for the answer.

So I did the changes you suggested and the code look like this now:

Code: Select all

 <?php
        // How Many Topics you want to display?
        $topicnumber = 5;
        // Change this to your phpBB path
        $urlPath = "/phpbb";

        // Database Configuration (Where your phpBB config.php file is located)
        include 'phpbb/config.php';

        $table_topics = $table_prefix. "topics";
        $table_forums = $table_prefix. "forums";
        $table_posts = $table_prefix. "posts";
        $table_users = $table_prefix. "users";
        $link = mysql_connect("$dbhost", "$dbuser", "$dbpasswd") or die("Could not connect");
        mysql_select_db("$dbname") or die("Could not select database");

        $query = "SELECT t.topic_id, t.topic_title, t.topic_last_post_id, t.forum_id, p.post_id, p.poster_id, p.post_time, u.user_id, u.username
        FROM $table_topics t, $table_forums f, $table_posts p, $table_users u
        WHERE t.topic_id = p.topic_id AND
        f.forum_id = t.forum_id AND
        t.forum_id != 4 AND
        t.topic_status <> 2 AND
        p.post_id = t.topic_last_post_id AND
        p.poster_id = u.user_id
        ORDER BY p.post_id DESC LIMIT $topicnumber";
        $result = mysql_query($query) or die("Query failed");                           

        print "<table cellpadding='3' cellSpacing='2' width='640'>";
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

       echo  "<tr valign='top'><td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\"><font color=\"#FFCC00\"><b><a href=\"$urlPath/viewtopic.php?f=$row[forum_id]&t=$row[topic_id]&p=$row[post_id]#p$row[post_id]\" TARGET=\"_blank\">" . $row["topic_title"] . "</a></b></font></td></tr>"; 
        }
        print "</table>";
        mysql_free_result($result);
        mysql_close($link);
        ?>
The problem is that now the last topics thing doesn´t work anymore, it gives the error: Could not connect as you can see in http://7cr9.com/2.php .

Thanks,
Mary

Sorry I copied the wrong code. I copied the one which I hadn't changed the path to phpbb , which in my case wasn't "/phpbb"; but "/forum".

Thank you, for your tip Sicle, it worked just fine ;)

PS: For the ones you are going to use this code, it is a basic thing to do, but still do not forget to change the path to where your forum phpbb is, on

Code: Select all

       $urlPath = "/phpbb";
// Database Configuration (Where your phpBB config.php file is located)
        include 'phpbb/config.php';

Re: CODE for latest topics on external page

Posted: Wed Sep 01, 2010 11:52 am
by Mary17
Hey I have another doubt about this code:

Code: Select all

     <?php
            // How Many Topics you want to display?
            $topicnumber = 5;
            // Change this to your phpBB path
            $urlPath = "/phpbb";

            // Database Configuration (Where your phpBB config.php file is located)
            include 'phpbb/config.php';

            $table_topics = $table_prefix. "topics";
            $table_forums = $table_prefix. "forums";
            $table_posts = $table_prefix. "posts";
            $table_users = $table_prefix. "users";
            $link = mysql_connect("$dbhost", "$dbuser", "$dbpasswd") or die("Could not connect");
            mysql_select_db("$dbname") or die("Could not select database");

            $query = "SELECT t.topic_id, t.topic_title, t.topic_last_post_id, t.forum_id, p.post_id, p.poster_id, p.post_time, u.user_id, u.username
            FROM $table_topics t, $table_forums f, $table_posts p, $table_users u
            WHERE t.topic_id = p.topic_id AND
            f.forum_id = t.forum_id AND
            t.forum_id != 4 AND
            t.topic_status <> 2 AND
            p.post_id = t.topic_last_post_id AND
            p.poster_id = u.user_id
            ORDER BY p.post_id DESC LIMIT $topicnumber";
            $result = mysql_query($query) or die("Query failed");                           

            print "<table cellpadding='3' cellSpacing='2' width='640'>";
            while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

           echo  "<tr valign='top'><td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\"><font color=\"#FFCC00\"><b><a href=\"$urlPath/viewtopic.php?f=$row[forum_id]&t=$row[topic_id]&p=$row[post_id]#p$row[post_id]\" TARGET=\"_blank\">" . $row["topic_title"] . "</a></b></font></td></tr>";
            }
            print "</table>";
            mysql_free_result($result);
            mysql_close($link);
            ?>
The text is in the colour blue, is it possible, that instead the text can be white?

Do you have any suggestions to change the codee to do so?

Thanks in advanace,
Mary

Re: CODE for latest topics on external page

Posted: Wed Sep 01, 2010 1:18 pm
by Drummer
Where is it blue? The only color I see in your code is FFCC00 and it cannot be blue (blue is ~0000FF). It must be your CSS file.
Why didn't you use the phpbb functions/classes (aka integrate)? and why are you joining 4 tables?

Re: CODE for latest topics on external page

Posted: Wed Sep 01, 2010 3:24 pm
by Mary17
Hey thanks for teh answer.

I use this code because it is the only I know that works on frontpage, since it is with that program that I manage my website.

You can see here an experiment of waht I'm talking about: http://7cr9.com/exp1.php

There you'll see that the text is blue and I wnated it white...?


About what you told me: functions/classes (aka integrate) I dont know what that is, or how it works, can you explain?

Thanks ,
Mary

Re: CODE for latest topics on external page

Posted: Thu Sep 02, 2010 12:12 pm
by Drummer
I mean the phpbb "API". It's (usually) much easier to manipulate the phpbb data if you use the same functions/classes as phpbb itself.

http://www.phpbb.com/kb/article/add-a-n ... -to-phpbb/

Most of phpbb functions are in <phpbb_root_path>/includes/.

Regarding the blue, this is the default color for links.
Add it to your header:

Code: Select all

<style>
	a { color: #FFFFFF }
</style>
And about your query... I dont know if performance is a problem for your you, but this query is many times slower and "heavier" than the necessary. Let me try to show you why:
You select u.user_id. This is redundant as you're selecting p.poster_id and your where clause imposes they must be the same. u.username is also the same as t.topic_last_poster_name. The users table is thus unnecessary.
You select t.topic_last_post_id and p.post_id which must always be the same as you impose it on your where clause. This where clause (p.post_id = t.topic_last_post_id) makes the posts table unneeded at al, as the other two fields you select from it (p.poster_id and p.post_time) must be the same as t.topic_last_poster_id and topic_last_post_time.
Then as well.
And... you don't even use the forums table, so why are you joining it?

Topics table has all you need. It's designed to avoid this kind of join or extra queries to get the first and last post info.

Re: CODE for latest topics on external page

Posted: Sun Sep 05, 2010 5:33 pm
by Mary17
Hey there
Thanks for your answer=)

I would like to have a more simple way to do this, but it happens that I dont know much of phpbb and I think I couldn't do this on my own. It took my for ages to get to do this one, and it worked finally!

But if you guide me through it, maybe I'll give it a ty.

About the code you gave me to the links not to be blue, it didn't work. Now, my heade code looks like this:

Code: Select all

<head>
<meta http-equiv="Content-Language" content="pt">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<link rel="File-List" href="Home_ficheiros/filelist.xml">

<title>7CR7, site de fãs do Cristiano Ronaldo, CR7  Cristiano Ronaldo fansite</title>
<!--[if !mso]>
<style>
   a { color: #FFFFFF }
</style>
<style>
v\:*         { behavior: url(#default#VML) }
o\:*         { behavior: url(#default#VML) }
.shape       { behavior: url(#default#VML) }
</style>
<![endif]-->
<script language="JavaScript">
<!--
function FP_goToURL(url) {//v1.0
 window.location=url;
}
// -->
</script>
<!--[if gte mso 9]>
<xml><o:shapedefaults v:ext="edit" spidmax="1027"/>
</xml><![endif]-->
</head>
Do you see any error?

Thanks,
Mary