Need help with a MySQL query that I'm running on my vB-database...

[Faruk]

This is the query I'm currently running:

SELECT t.threadid, t.title, t.replycount, t.postusername, t.dateline, t.sticky, p.pagetext FROM thread as t LEFT JOIN post as p ON (t.threadid = p.threadid) WHERE t.forumid=5 ORDER BY t.sticky DESC, t.dateline DESC LIMIT 15;

Notes: t.forumid=5 points to my News & Announcements forum, and is currently set by a variable (for calling the portal-script from another page, so that I can simply change the variable in the calling page), as well as the LIMIT 15 -> also set by a variable.

However, that's not the issue here. If you run this on your vbulletin database you will see that it takes the threadid etc.etc. from the newest threads in that forum, with Sticky threads first and then sorted on thread CREATION dates (not lastpost date). However, what I want to achieve here, is to ONLY get the FIRST post of every thread, and NOT all the posts of the threads and THEN have it move on to the next thread. So basically, the 15 results I need should be 15 threads, their first post's content listed in pagetext, and that's it.

Help would be greatly appreciated!


Also, if anyone knows how I can parse the pagetext for BBcode without including the ENTIRE functions-set of vBulletin, that'd be great. :)

Oh, final request (this one is probably easier) - I currently have my portal-script perform a strlen() function on the PAGETEXT result from the above query, and I'm wondering if it's tweakable (the entire strlen() function) so that it will cut off and add "..." only after whole words, not in the Middle of a word (i.e. that it cuts off at the next space after xxx characters). Is that possible?

Thanks in advance for the help!

[Mark Hensler]

I'm not that familiar with vB's tables, so I'll leave that for someone else.

As for the string thing.. try something like this:

// how many characters?
$offset = 100;

// cut them off
$text = substr($text, 0, strpos($text, ' ', $offset)-1) . '...';

[Faruk]

Thanks, but that does the same as what I currently have. It'll cut it off at exactly 'x' amount of characters, regardless of whether it's in the middle of a word or not.. :(