PunBB Forums — How do MySQL queries in PunBB work?

Re: How do MySQL queries in PunBB work?

null@example.com (Parpalak) — Wed, 15 Apr 2009 08:12:48 +0000

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

Re: How do MySQL queries in PunBB work?

null@example.com (Slavok) — Tue, 14 Apr 2009 14:24:38 +0000

To limit the length of a message use this code:

define('MAX_MESSAGE_LENGTH', 100);
...
'.substr($message, 0, MAX_MESSAGE_LENGTH).'
';

Re: How do MySQL queries in PunBB work?

null@example.com (VincentBroccoli) — Sun, 12 Apr 2009 18:41:24 +0000

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.

Re: How do MySQL queries in PunBB work?

null@example.com (Slavok) — Wed, 25 Mar 2009 09:12:17 +0000

VincentBroccoli wrote:

- And the line
'.$cur_post['num_replies'].' Comments
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:

'.$cur_post['num_replies'].' Comments

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?

null@example.com (MattF) — Tue, 24 Mar 2009 22:17:16 +0000

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.

Re: How do MySQL queries in PunBB work?

null@example.com (pepak) — Tue, 24 Mar 2009 19:56:52 +0000

VincentBroccoli wrote:

'.$cur_post['num_replies'].' Comments
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']?

Re: How do MySQL queries in PunBB work?

null@example.com (pepak) — Tue, 24 Mar 2009 19:53:30 +0000

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?

Re: How do MySQL queries in PunBB work?

null@example.com (VincentBroccoli) — Tue, 24 Mar 2009 13:25:38 +0000

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

'.$cur_post['num_replies'].' Comments

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

Re: How do MySQL queries in PunBB work?

null@example.com (MattF) — Tue, 24 Mar 2009 11:22:54 +0000

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?

null@example.com (Slavok) — Tue, 24 Mar 2009 09:05:56 +0000

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'
);

Re: How do MySQL queries in PunBB work?

null@example.com (pepak) — Tue, 24 Mar 2009 05:45:07 +0000

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?

null@example.com (VincentBroccoli) — Mon, 23 Mar 2009 22:35:07 +0000

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

Re: How do MySQL queries in PunBB work?

null@example.com (VincentBroccoli) — Sat, 21 Mar 2009 00:10:50 +0000

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?

null@example.com (Utchin) — Thu, 19 Feb 2009 18:20:49 +0000

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

How do MySQL queries in PunBB work?

null@example.com (John_S) — Thu, 19 Feb 2009 17:14:41 +0000

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.

 '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", "
", $cur_post['message']);
        $date = date("j  M  Y", $cur_post['posted']);
        
        echo '
        '.$cur_post['subject'].'
        Written by '.$cur_post['poster'].' on '.$date.' | 
        '.$cur_post['num_replies'].' Comments
        '.$message.'';
        
    }
}
 else { echo 'We are sorry but no news were found in the database, if you believe this is an error, please notify the administrator.'; }
?>

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!