Posted: Sun Mar 20, 2005 5:35 pm
order by theadid and post time, this may fix it.. what is likely happening is it's grouping by the first post it encounters, which may very well be a reply post, which may not have a title.
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
Code: Select all
$sql = "SELECT * FROM vb3_thread LEFT JOIN vb3_post ON vb3_thread.threadid = vb3_post.threadid WHERE vb3_thread.lastposter = vb3_post.username GROUP BY vb3_thread.threadid ORDER BY vb3_thread.dateline AND vb3_thread.threadid DESC LIMIT 5";Code: Select all
ORDER BY vb3_thread.threadid DESC, vb3_thread.dateline ASCCode: Select all
<head>
<style type="text/css">
.txt {
font-family: verdana;
font-size: 11px;
}
</style>
</head>
<?php
$dbhost = "localhost" // Change to suit your database connection details
$dbuser = "#####"; // Change to suit your database connection details
$dbpasswd = "#####"; // Change to suit your database connection details
$database = "#####"; // Change to suit your database connection details
$heaading = "#5D6976"; // Change hex to suit your color needs for heading
$color1 = "#999999"; // Change hex to suit your color needs for alternating rows
$color2 = "#CCCCCC"; // Change hex to suit your color needs for alternating rows
$threads = "5"; // Change to display how many recent threads you want to display
// Dont alter below this line!
$connection = mysql_pconnect("$dbhost","$dbuser","$dbpasswd")
or die ("Couldn't connect to server.");
$db = mysql_select_db("$database", $connection)
or die("Couldn't select database.");
echo "<table width=\"98%\" border=\"0\" align=\"center\" cellpadding=\"5\" cellspacing=\"1\">
<tr>
<td width=\"35%\" bgcolor=\"$heaading\"><div align=\"left\"><font class=\"txt\" color=\"#CCCCCC\"><b>Posted By</b></font></div></td>
<td width=\"10%\" bgcolor=\"$heaading\"><div align=\"left\"><font class=\"txt\" color=\"#CCCCCC\"><b>Topic</b></font></div></td>
<td width=\"10%\" bgcolor=\"$heaading\"><div align=\"center\"><font class=\"txt\" color=\"#CCCCCC\"><b>Views</b></font></div></td>
<td width=\"5%\" bgcolor=\"$heaading\"><div align=\"center\"><font class=\"txt\" color=\"#CCCCCC\"><b>Replys</b></font></div></td>
</tr>";
$sql = "select t.postusername as postername
, t.title
, t.views
, t.replycount
, p1.username as lastposter
, p1.pagetext
, p1.dateline
from vb3_thread as t
inner
join vb3_post as p1
on t.threadid = p1.threadid
inner
join vb3_post as p2
on t.threadid = p2.threadid
group
by t.postusername
, t.title
, t.views
, t.replycount
, p1.username
, p1.pagetext
, p1.dateline
having p1.dateline
= max(p2.dateline)
order
by t.dateline desc
limit $threads";
$result = mysql_query($sql) or die ("Query failed");
while ($row = mysql_fetch_array($result)) {
$title = $row['title'];
$replycount = $row['replycount'];
$postername = $row['postername'];
$lastposter = $row['lastposter'];
$views = $row['views'];
$pagetext = $row['pagetext'];
$row_color = ($row_count % 2) ? $color1 : $color2;
echo "<tr valign=\"top\">
<td bgcolor=\"$row_color\"><div align=\"left\"><font class=\"txt\">$postername<br><br><b>Last Poster</b><br><br>$lastposter</font><br></div></td>
<td width=\"74%\" bgcolor=\"$row_color\"><div align=\"left\"><font class=\"txt\">$title<br><br><b>Posted</b><br><br>$pagetext</font></div></td>
<td width=\"10%\" bgcolor=\"$row_color\"><div align=\"center\"><font class=\"txt\">$views</font></div></td>
<td bgcolor=\"$row_color\"><div align=\"center\"><font class=\"txt\">$replycount</font></div></td>
</tr>";
$row_count++;
}
echo '</table><br>';
?>