Last X topics (and no posts)

All files from this thread

File Name	File Size		Posted by	Date
latestthreads.php	4.2 KB	open | download	Panu	03/09/2005	Read message
latestthreads.php	4.2 KB	open | download	Panu	03/10/2005	Read message
latestthreads_051029.zip	2.5 KB	open | download	Panu	09/29/2005	Read message
latestthreads-2007-05-01.zip	2.7 KB	open | download	Panu	01/05/2007	Read message

Hi,

I have search on the forums but it seems like the is no answer to my question :

how do I show the last X active topics on an other page of my website?

it has to be a simple MySQL query, but I can't find witch.

Thx
Clément

I have a solution but it's not a good solution, let's see : the problem is to take into the phorum_mesages table a great number of last threads, then to add their id to a string, then to check if their id is not yet in this string (this is, the thread has always been added to our list of active threads) and then it's easy

But the problems are :
1) it's not good because if the last 100 posts are in the same thread, well our script will not see the last X threads.
2) the script is sad, I think someone could write it shorter and better than me!

Lets the script and u will understand ;-) PHP is more clear than my english!
(for those who want to see it "correctly", I post it in my next post!

-------------------------------------
<?
//We take the 100 last active threads
$req = "SELECT thread FROM phorum_messages ORDER BY datestamp DESC LIMIT 100";

$res = mysql_query($req,$lien);

//We are going to put in an array juste **ONE TIME** each active thread
while($row = mysql_fetch_array($res))
{
//Check if the thread is not already present in the array
if(!ereg("_".$row[thread]."_",$liste_ecrite))
{
//If it is not, then we add it to the list
$liste_threads_actifs[] = $row[thread];
//And we add it to the string that contains the threads ids, for example : thread number one is _1_ in this string
$liste_ecrite .= "_" . $row[thread] . "_";
}
}

for($i=0;$i<count($liste_threads_actifs);$i++)
{
//We select the subjects of the last active threads
$req = "SELECT subject FROM phorum_messages WHERE thread=";
$req .= $liste_threads_actifs[$i];
$req .= " AND parent_id=0";

//We select the total number of post of each active thread
$req2 = "SELECT COUNT(message_id) FROM phorum_messages WHERE thread=";
$req2 .= $liste_threads_actifs[$i];

//Nothing to say ;-)
$res = mysql_query($req,$lien);
$res2 = mysql_query($req2,$lien);

//We put in an array the subjects of the threads
while($row = mysql_fetch_array($res))
{
$titre_topics[] = $row[subject];
}

//We put in another array the total number of posts in each thread
//Note : the two arrays have the same keys, this is, $tittre_topics[1] is the title of the thread witch has $nbposts_topics[1] posts!
while($row = mysql_fetch_array($res2))
{
$nbposts_topics[] = $row["COUNT(message_id)"];
}
}

//Displaying the results
$j = 0; //This is not util
$number_of_threads_to_display = 10; //Limit on the screen to X threads (remember that the MySQL query was "LIMIT 100" and we don't want so much threads!!!

for($i=0;$i<count($titre_topics);$i++)
{
if($j<$number_of_threads_to_display)
{
echo $titre_topics[$i], " [", $nbposts_topics[$i], "]<br>";
$j += 1;
}
}

?>
-------------------------------------

Here is the script

I' va forgotten something : then we have to create a link to those topics. I have made the next part of the script to do this : the principe is :

1. Get for example the 15 latest posts of ONE topic
2. Count them (if it's a new topic there are just 1 or two posts)
3. Create a URL that sounds phorum/read.php/forum_id,oldest_message_from_the_15,most_recent_message_of_this_topic#msg-most_recent_message_of_this_topic

It's quite easy.

If someone wants my script, please tell me.

i need a short script / template
that returns the newest topic + thread
of every forum with link to the forum,
thread and profile.



Edited 1 time(s). Last edit at 03/09/2005 12:54PM by michaelborchers.

This version shouldn't be as hard on the DB as Cléments.

It loads configurable amount of last threads, finds the last post, takes the author, adte and body, truncates/formats them and puts the data into an array.

I assign the arrays to smarty template after that but you can do whatever suits your page.

I have used the same base script to display posts differently over time, but I have now settled for this one.

Note I made some changes "blindly" mainly variablename translations so if it doesn't work, let me know or fix it yourself ;)


edit: removed buggy attachment

---
-=[ Panu ]=-



Edited 1 time(s). Last edit at 03/09/2005 09:29AM by Panu.

from the script in the sql-query:
statust = 2

I guess that typo *needs* to be fixed ;).

---

Thomas Seifert

Hmm, yes :) Thanks Thomas.

Attached is corrected version. I fixed another error too. I'll test this later on more throughly (ie I put it on my own server). It's basic stuff so if there's something else I'm sure one is able to fix/extend/change this by themselves too.

---
-=[ Panu ]=-

Hhhmm, I get nothing Panu.

Sergej

------------------------------------------
^AU^ Assassins United
[www.assassinsunited.com]
------------------------------------------

sergej,

did you read this in the comments there?
// this scrip needs to be run in phorum directory. I include needed phorum stuff elsewhere so I can use $PHORUM globals
// replace them with hardcoded values if you don't include common.php

---

Thomas Seifert