Topic: How do MySQL queries in PunBB work?

Hello everybody,

Recently I decided to add some modification to my Punbb installation, but I had to face the fact that I don't have even a tiniest idea of how SQL queries are working in PunBB.

So far I tried everything I knew on this query which is supposed to bring first post of a topic created in a specific forum and display it as a news entry. However all I get is that it displays all posts made in that forum.

<?php
define('FORUM_ROOT', 'forum/');
require FORUM_ROOT.'include/common.php';

define('RECENT_POSTS_SHOW_POST', true); // Set to false to show topic subject only

$query = array(
    'SELECT'    => 'p.id, p.message, t.subject, t.poster, t.num_replies, t.posted, p.poster_id, t.num_replies',
    'FROM'        => 'topics AS t',
    'WHERE'        => 't.forum_id = 2 AND t.sticky = 0',
    'JOINS'        => array(
        array(
            'LEFT JOIN'        => 'posts AS p',
            'ON'            => 'p.topic_id = t.id'
        )
    ),
    'ORDER BY'    => 't.posted DESC',
    'LIMIT'        => '0,10'
);
 
$result = $forum_db->query_build($query) or error(__FILE__, __LINE__);
 
$recent_posts = array();
while ($cur_post = $forum_db->fetch_assoc($result))
    $recent_posts[] = $cur_post;
 
// Print out posts
if (!empty($recent_posts))
{
    foreach($recent_posts as $cur_post)
    {
        $message = str_replace("\n", "<br />", $cur_post['message']);
        $date = date("j  M  Y", $cur_post['posted']);
        
        echo '
        <h2>'.$cur_post['subject'].'</h2>
        <p class="info">Written by <a href="'. FORUM_ROOT .'profile.php?id='.$cur_post['poster_id'].'" 
        title="'.$cur_post['poster'].'\'s profile">'.$cur_post['poster'].'</a> on '.$date.' | 
        <a href="'. FORUM_ROOT .'viewtopic.php?id='.$cur_post['id'].'" title="View Replies" >'.$cur_post['num_replies'].' Comments</a></p>
        <p>'.$message.'</p>';
        
    }
}
 else { echo '<p>We are sorry but no news were found in the database, if you believe this is an error, please notify the administrator.</p>'; }
?>

Could somebody explain it please? I tried searching though the WiKi but there seems to be no articles about it.
Thanks a lot in advance! big_smile

Errare humanum est

Re: How do MySQL queries in PunBB work?

have a look at http://fluxbb.org/wiki/v1.3:developing_ … #databases

Sorry. Unactive due to personal life.

Re: How do MySQL queries in PunBB work?

I have the same question. Is there any way to change the code that John_S posted in a way that the latest new topics from a specific forum will be displayed as news.

Re: How do MySQL queries in PunBB work?

Anyone? I think there are more people who need a function like this for a website.

5

Re: How do MySQL queries in PunBB work?

What's the problem? Building a real query is hardly any more than concatecating all keys and values into one string.

Re: How do MySQL queries in PunBB work?

Right now we have only one article in wiki, related with DB query.
John_S, this query will return the first post of every forum topic with forum_id = 10:

 $query = array(
    'SELECT'    =>    'p.id, p.message, t.subject, t.poster, t.num_replies, t.posted, p.poster_id, t.num_replies',
    'FROM'        =>    'posts AS p',
    'JOINS'        => array(
        array(
            'LEFT JOIN'    =>    'topics AS t',
            'ON'        =>    'p.topic_id = t.id'
        )
    ),
    'WHERE'        => 'p.id = t.first_post_id AND t.forum_id = 10',
    'ORDER BY'    => 't.posted DESC'
);    

7

Re: How do MySQL queries in PunBB work?

pepak wrote:

What's the problem? Building a real query is hardly any more than concatecating all keys and values into one string.

Just because you may be familiar with php and SQL does not mean that everyone is.

Re: How do MySQL queries in PunBB work?

Thank you very much Slavok!

But I have two questions:

- How do I limit the numbers of topics displayed?

'LIMIT'    => '0,10'

The LIMITline doesn't work.

- And the line

<a href="'. FORUM_ROOT .'viewtopic.php?id='.$cur_post['id'].'" title="View Replies" >'.$cur_post['num_replies'].' Comments</a>

links to the post id not to the topic id. How can I fix that?

9

Re: How do MySQL queries in PunBB work?

MattF wrote:
pepak wrote:

What's the problem? Building a real query is hardly any more than concatecating all keys and values into one string.

Just because you may be familiar with php and SQL does not mean that everyone is.

Well, I guess - but really, if someone is unfamiliar with both PHP and SQL, how can he ever hope to make meaningful changes to the code?

10

Re: How do MySQL queries in PunBB work?

VincentBroccoli wrote:
<a href="'. FORUM_ROOT .'viewtopic.php?id='.$cur_post['id'].'" title="View Replies" >'.$cur_post['num_replies'].' Comments</a>

links to the post id not to the topic id. How can I fix that?

Are you sure? It sure looks like a topic link.
Maybe (MAYBE!) it should be $cur_post['topic_id']?

11

Re: How do MySQL queries in PunBB work?

pepak wrote:

Well, I guess - but really, if someone is unfamiliar with both PHP and SQL, how can he ever hope to make meaningful changes to the code?

No-one is born a programmer.

12

Re: How do MySQL queries in PunBB work?

VincentBroccoli wrote:

- And the line

<a href="'. FORUM_ROOT .'viewtopic.php?id='.$cur_post['id'].'" title="View Replies" >'.$cur_post['num_replies'].' Comments</a>

links to the post id not to the topic id. How can I fix that?

Modify 'SELECT' key in array:

    'SELECT'    =>    'p.id, p.message, t.subject, t.poster, t.num_replies, t.posted, p.poster_id, t.num_replies, t.id AS tid',

And link will look like this:

<a href="'. FORUM_ROOT .'viewtopic.php?id='.$cur_post['tid'].'" title="View Replies" >'.$cur_post['num_replies'].' Comments</a>

Code

'ORDER BY'    => 't.posted DESC',
'LIMIT'            => '0,10' 

work fine for me to limit the numbers of topics.

Re: How do MySQL queries in PunBB work?

Oke, that code works great! Thanks!!

One question though: How can I limit the number of characters that are displayed, because at the moment the complete first post is showing on the frontpage.

14

Re: How do MySQL queries in PunBB work?

To limit the length of a message use this code:

define('MAX_MESSAGE_LENGTH', 100);
...
<p>'.substr($message, 0, MAX_MESSAGE_LENGTH).'</p>';

Re: How do MySQL queries in PunBB work?

I think it's better to use the function utf8_substr, not substr. utf8_substr is defined in the forum core.