[SOLVED] Table joins
Moderator: General Moderators
Hi,
Do you mean something like:
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 ..
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";Some $title show and others dont ..
Am I anywhere on the right track .
Thanks ..
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Code: Select all
ORDER BY vb3_thread.threadid DESC, vb3_thread.dateline ASCHi,
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:
Who knows .. someone may find it usefull ..
Jimbo
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>';
?>Jimbo