Hi
I am trying to achieve a list of topics that have most recently had posts made in them using a PHP add on to an article in Joomla. To look something like the forum list on this homepage:
Aberdeen Snooker website
So far I can get a list of the latest posts but not per topic as I want. I have got to the following:
New Website
Using the following code:
Code:
<?php
$user_name = "xxx";
$password = "xxx";
$database = "xxx";
$server = localhost;
$db_handle = mysql_connect($server, $user_name, $password);
$db_found = mysql_select_db($database, $db_handle);
if ($db_found) {
$SQL = "SELECT abs_kunena_messages.catid, abs_kunena_messages.thread, abs_kunena_messages.subject, abs_kunena_messages.id, abs_kunena_messages.hits, abs_kunena_messages.time, abs_kunena_categories.name FROM abs_kunena_messages,abs_kunena_categories where abs_kunena_messages.catid = abs_kunena_categories.id order by abs_kunena_messages.time DESC limit 10";
$result = mysql_query($SQL);
echo '<table><tr><td><b>Forum</b></td><td><b>Topic</b></td><td><b>Replies</b></td><td><b>Views</b></td><td><b>Last Post</b></td></tr>';
while ($row = mysql_fetch_assoc($result))
{
echo '<tr><td><a href="index.php/forum/' . $row['catid'] . '">';echo $row['name'];echo'</a></td>';
echo'<td><a href="index.php/forum/' . $row['catid'] . '/' . $row['thread'] . '#' . $row['id'] . '">';echo $row['subject'];echo'</a></td>';
echo'<td>';
$thread_temp = $row[thread];
$sql3 = "SELECT id FROM abs_kunena_messages WHERE thread = $thread_temp";
$result3 = mysql_query($sql3);
$num_rows = mysql_num_rows($result3);
$num_rows = $num_rows - 1;
echo $num_rows;
mysql_free_result($result3);
echo'</td>';
echo'<td>';
$thread_temp = $row[thread];
$sql2 = "SELECT hits FROM abs_kunena_messages WHERE thread = $thread_temp AND parent = 0";
$result2 = mysql_query($sql2);
$row2 = mysql_fetch_assoc($result2);
echo $row2[hits];
mysql_free_result($result2);
echo'</td>';
echo'<td>';echo date("d-m-Y, H:m", $row['time'] );echo'</td></tr>';
}
echo '</table>';
mysql_close($db_handle);
}
else {
print "Database NOT Found ";
mysql_close($db_handle);
}
?>
Because of the way Kunena keeps topics and posts in the same table I am not sure how to produce the right query to give the list per topic as in the original site and not per post.
ANy help much appreciated...
