Displaying a random post

[KingAdora]

Is there a mod which can do this?

So that at the top right of the forum I can have something like this:

"<text from a random post>"
-<username>
-<thread name>

I made one myself, but it instead of the pages loading in less than a second, they took about 2 seconds to load, so I turned it off.

I used the global_start hook as anything else seemed to be processed too late.

Actually, if I post the code maybe someone will be able to help:



<?php

$Qrs = $db->query_first('
SELECT pagetext, username, p.dateline, t.title, p.postid
FROM post p
INNER JOIN thread t on t.threadid = p.threadid
ORDER BY rand()
LIMIT 1
');

$Qtext = $Qrs['pagetext'];
$Qusername = $Qrs['username'];
$Qdate = $Qrs['dateline'];
$Qthreadtitle = $Qrs['title'];
$QqPostid = $Qrs['postid'];


//setup
$Qurl = "http://forum.website.co.uk/showthread.php?p=$QpostID#post$QpostID";
$Qdate = date("jS M y", $Qdate);
$Qtext = trim($Qtext);


//replacements
$Qtext = str_replace('[B}', '<b>', $Qtext);
$Qtext = str_replace('[/B]', '</b>', $Qtext);
$Qtext = str_replace('', '<i>', $Qtext);
$Qtext = str_replace('', '</i>', $Qtext);
$Qtext = str_replace('', '<u>', $Qtext);
$Qtext = str_replace('', '</u>', $Qtext);
$Qtext = str_replace('', '<a href="', $Qtext);
$Qtext = str_replace('', '">Image</a>', $Qtext);


//remove everything before [/quote] if it exists
$QcutQuote = "[/QUOTE]";
$QquotePos = stripos($Qtext, $QcutQuote);

if($QquotePos !== false) {
$QstartFrom = $QquotePos + strlen($QcutQuote);
$Qtext = substr($Qtext, $QstartFrom);
}

//make url
$Qoutput = "$Qtext<br/><br/>- $Qusername<br/>\"<a href=\"$Qurl\">$Qthreadtitle</a>\"";

$Qquotes = $Qoutput;

?>



$Qquotes is in the top right advert template, and the script is started on global_start

[Opserty]

Maybe use the datastore to store the post then get a scheduled task to select a new post every 5 minutes or so. You may find it quick to select pre-parsed posts from the postparsed table (if you have post caching on).

Instead of doing multple str_replace()s you can put the search and replace values into seperate arrays. E.g.


$search = array('', '', ....);
$replace = array('<b>', '</b>', ....);
str_replace($search, $replace, $Qtext);

[KingAdora]

Thanks for that! Do you (or others) have any more ideas to make it faster/more efficient?

[Farcaster]

I could think of a couple ways that might make your query faster without caching. By ordering on rand(), you are forcing a table scan of the entire post and thread tables, because that is the only way mySQL can use to determine which row will be ordered first.

So, the first option I see is to limit the posts by the dateline field. After all, do you really want a random post from any time in your board's history? You might limit it instead to a certain number of days in the past.

The second option, is to take advantage of MySQL's super fast row counting on MYISAM tables. You'd use two queries to get your result. The first would do a count of the rows. In PHP, you'd then randomly determine which one you wanted to use. Then execute a second query that used LIMIT $randomRow, 1. That would look something like this:

// get post count
$posts = $vbulletin->db->query_first("SELECT count(*) total_posts FROM ".TABLE_PREFIX."post");

// determine a random offset point. Offset starts at 0, so subtract 1 from result
$randomRow = rand(1,$posts['total_posts']) - 1;

// Retreive the row
$Qrs = $db->query_first("
SELECT pagetext, username, p.dateline, t.title, p.postid
FROM ".TABLE_PREFIX."post p
INNER JOIN ".TABLE_PREFIX."thread t on t.threadid = p.threadid
LIMIT $randomRow, 1
");

// The rest of your code.....

(Note I just wrote this up in notepad, I didn't test it. But, I think this should work for you.)

[KingAdora]

Wow thanks, that SQL hint really made it faster! Never would have thought 2 queries would better than one!

I also changed this: SELECT count(*) total_posts....
To this: SELECT count(postid) total_posts....

Not sure if using a field rather than * is any 'better'?
If you have any more gems let me know :D

[KingAdora]

I had a horrible thought just now so I typed some html code <blink>hi</blink> into a post, and set the query just to look at that post...

To my horror it was all parsed as html! So if someone had typed some nasty html into a post and waited (and refreshed for several hours) it quite possibly would have done something?

I assume if I use:
$text = htmlentities($rs['pagetext']);

That will be safe?

[MoT3rror]

Or if you have vbulletin global file already included, you can just use the vbulletin BBcode parser.

https://vborg.vbsupport.ru/showthread.php?t=82693&highlight=bbcode

[KingAdora]

Ahh good tip that, and it means I can get rid of my search replace arrays, because this parser does it all for me!

Keep it simple..