Page 2 of 2

Posted: Sun Mar 20, 2005 5:35 pm
by feyd
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.

Posted: Sun Mar 20, 2005 6:15 pm
by Jim_Bo
Hi,

Do you mean something like:

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";
Thats seems to show the post from 1, 2, 3 ... rather than 28,27,25 ..

Some $title show and others dont .. :(

Am I anywhere on the right track .

Thanks ..

Posted: Sun Mar 20, 2005 6:24 pm
by feyd

Code: Select all

ORDER BY vb3_thread.threadid DESC, vb3_thread.dateline ASC

Posted: Mon Mar 21, 2005 5:19 pm
by Jim_Bo
Hi,

Ok I have it all sorted .. I will post all the code here .. Maybe others can use it ..

This code is used to shows the #5 most recent threads ( Poster, Topic, Views, Replys )

Below each thread it shows the most recent post ( Poster, Reply ) from the vBulletin forum.

This also assumes you are using the standard table prefix <b>vb3_</b> If not you will need to edit the $sql query to suit ..

Code:

Code: 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>';

?>
Who knows .. someone may find it usefull ..

Jimbo