• ronnyr
  • Junior Member
  • Offline
  • Registered: 2008-03-30
  • Posts: 5

Topic: newsitem on my site ?

Hi everyone,

I have tried the newsplugins and am hapy with those, accept, they all work on a while loop with one or more forums.
I do not wish to display all the news on the frontpage, but only the title, date etc.. with a link to a new page wich in turn displays the newsitem itself.

but now I came to a stagering halt, because i got a little confused from al the joins and wheres in the query's :S

My question is...
If i have a topicid, how can I display the topic title, date and message in the new page ?


With regards,

Ronny Roethof
www.dcthoekje.nl

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: newsitem on my site ?

An SQL query won't work?

  • ronnyr
  • Junior Member
  • Offline
  • Registered: 2008-03-30
  • Posts: 5

Re: newsitem on my site ?

Am trying a few query's but I took a look at the query's from the newsplugins and got confused and dazzled by the joins, and mixes of wheres in the querys..

it looks like its going to work smile but it just startled me with my limited experience with mysql :S

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: newsitem on my site ?

So, given a topic ID, you want a topic's subject, last posted time, and the contents of the last post?

  • ronnyr
  • Junior Member
  • Offline
  • Registered: 2008-03-30
  • Posts: 5

Re: newsitem on my site ?

actually no wink
the content of the first post smile (the content of the newsitem itself)

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: newsitem on my site ?

Oh. That's simple in 1.3 (the version currently in beta), not so simple in 1.2 (since in 1.3 we record the first post id in its own column).
For 1.2:

select t.subject, t.posted, p.message from prefixtopics as t inner join prefixposts as p on (p.topic_id=t.id and p.posted=t.posted) where t.id=#

Where prefix is your database prefix and # is the topic ID.

  • ronnyr
  • Junior Member
  • Offline
  • Registered: 2008-03-30
  • Posts: 5

Re: newsitem on my site ?

ok I know it's crappy PHP, but it works for now smile

$nieuwsid = $_GET['nieuwsid'];

$result = $db->query('
    SELECT 
    * FROM '.$db->prefix.'posts 
    WHERE topic_id = '.$nieuwsid.'
    ORDER BY id DESC LIMIT 1
    ') or error('Unable to fetch posts', __FILE__, __LINE__, $db->error());



if ($db->num_rows($result)) {
    $cur_post = $db->fetch_assoc($result);
    $cur_post['message'] = parse_message($cur_post['message'], $cur_post['hide_smilies']);

    $result = $db->query("SELECT * FROM ".$db->prefix."topics WHERE id = ".$nieuwsid."")  or error('Unable to fetch topics', __FILE__, __LINE__, $db->error());
    $cur_topic = $db->fetch_assoc($result);

    $tpl->newBlock( "NIEUWS" );
    
    $tpl->assign( "NIEUWS_TITLE", pun_htmlspecialchars($cur_topic['subject']) );
    $tpl->assign( "NIEUWS_VIEWS", $cur_topic['num_views'] );
    $tpl->assign( "NIEUWS_DATUM", format_time($cur_post['posted']) );
    $tpl->assign( "BERICHT", $cur_post['message'] );
    
    $tpl->gotoBlock( "_ROOT" );
} else {
    $tpl->newBlock( "ERROR" );
    $tpl->assign("ERROR", "Er is geen nieuwsitem gevonden.");
    $tpl->gotoBlock( "_ROOT" );
}
  • ronnyr
  • Junior Member
  • Offline
  • Registered: 2008-03-30
  • Posts: 5

Re: newsitem on my site ?

aah cool your code is much cleaner smile thx alot smile

9 Reply by n00b

  • n00b
  • Member
  • Offline
  • Registered: 2008-04-09
  • Posts: 15

Re: newsitem on my site ?

ASnd where would u insert this?