Add a block with 1 random post from a specific forum

[NR43]

Your Portal Version: 1.0.5
Your phpBB Type: Standard phpBB3
MODs installed: Yes
Your knowledge: Beginner
Boardlink: http://www.kunstschildersforum.be

PHP Version: 3.0.7-PL1
MySQL Version: 5.0.51a

What have you done before the problem was there?
I got the idea as described below.

What have you already tryed to solve the problem?
Created a mySQL query to fetch a random post from a random topic within a specific forum, in this case forum_id=43
Added a placeholder custom block on the portal called "MEESTERlijke woorden..."
Added a html file that will be used as template for the output.
Created a quotepost.php file and placed it in the portal/includes directory.

Description and Message
First of all, thanks a million for this awesome portal application.
It works like a charm on my forum.

I'm currently in the process of customizing the portal to my needs.
Goal: I want to display a random post from a random topic from within a specific forum, namely the forum with forum_id=43.
Each topic in this forum contains posts with a quote from a certain artist. The goal is to randomly post a quote on the portal in the block "MEESTERlijke Woorden..."

This SQL query I was able to create and test in mySQLAdmin:

Code: Select all

SELECT * FROM `phpbb_posts` WHERE `forum_id`=43 AND `topic_id`>=(SELECT FLOOR(MAX(`topic_id`) * RAND()) FROM `phpbb_topics`) ORDER BY RAND() LIMIT 1;

How could I implement this in my custom block?

The html file for the output looks like this:

Code: Select all

{$C_BLOCK_H_L}MEESTERlijke Woorden...{$C_BLOCK_H_R}
<table class="tablebg" cellspacing="1" width="100%">
   <tr>
		<td class="cat" style="font-weight: bold">
		Post title comes here (= title random post from random topic from forum MEESTERlijke Woorden)
		</td>
   </tr>
   <tr class="row1">
      <td>
		Random quote comes here (= content random post from random topic from forum MEESTERlijke Woorden)
		
		</td>
	</tr>
	<tr class="row2">
		<td>
			<p class="gensmall">Ken je een citaat van een Meester Kunstschilder die je altijd bijgebleven is en die je graag zou toegevoegd zien aan de lijst met citaten? Stuur dan een <a href="http://www.schildersforum.org/forum/ucp.php?i=pm&mode=compose&u=2">pm naar de forumbeheerder</a> met vermelding van de kunstschilder in kwestie en zijn wijze woorden.</p>
      </td>
   </tr>
</table>
{$C_BLOCK_F_L}{$C_BLOCK_F_R}

I understand that I need a separate php file to put the SQL query in, but how do I get the output of my sql query in my custom block?
My current quotepost.php looks like this:

Code: Select all

<?php
		//get a random topic from forum "MEESTERlijke Woorden"
		$randomquotepost = "SELECT * FROM `phpbb_posts` WHERE `forum_id`=43 AND `topic_id`>=(SELECT FLOOR(MAX(`topic_id`) * RAND()) FROM `phpbb_topics`) ORDER BY RAND() LIMIT 1";
		echo $randomquotepost;
?>

This is obviously not working.
I've read the posts on phpbb.com about getting stuff on external pages etc, but my php knowledge is far too limited for it to make any sense.

Also, when I compare the structure of my html file with eg news.html, I notice the news.html file calls variables like {L_POSTED} etc.
Perhaps it's best to do this similarly? If so, where and how do I define my variable(s)? Is this for the news block done in news.php? If I base my quotepost.php on news.php, which parts are redundant and which parts should I defo not forget to include?
Can you please point me into the right direction?

Thanks in advance for your help![/i]

[NR43]

Anyone, please?
I'm not making any progress on this