Page 1 of 2
[SOLVED] Table joins
Posted: Fri Mar 18, 2005 8:41 pm
by Jim_Bo
Hi,
I have grabed the 5 newest threads from vbulletin .. But I also what to call back the latest post to each thread .. Which I guess a table join is needed .. from what I have read something like the following should work:
Code: Select all
$sql = "SELECT * FROM vb3_thread, vb3_post WHERE vb3_tread.threadid = vb3_post.threadid ORDER BY threadid desc LIMIT 5";
But I get a failed query .. Am I anywhere near on the right track?
Thanks ..
Posted: Fri Mar 18, 2005 8:57 pm
by hongco
you spelled vb3_tread.threadid wrong; should've been vb3_thread
also Order by threadid -- > which table does threadid belong to? either: vb_thread.threadid or the other
Posted: Fri Mar 18, 2005 9:17 pm
by Jim_Bo
Hi,
Yer I found the spelling error .. The "threadid" is in both tables .. I used vb3_thread.threadid and the query worked ..
Except that the posts dont seem to be in order with the threads .. Is there a formality .. so I get the thread then get the most resent post to line up with the correct thread ... which is within a loop to get the latest 5 of each ..
Also it is set within alternaring rows ..
Thanks
Posted: Fri Mar 18, 2005 9:19 pm
by hongco
ORDER BY vb3_thread.threadid DESC
"DESC" = descending (latest thread shows first)
"ASC" = ascending (earlier thread shows first)
Posted: Fri Mar 18, 2005 9:37 pm
by Jim_Bo
Hi,
Yer I know that much .. Its more the fact that now its not listing the newest 5 threads .. under each thread the newest post ..
With the above code .. its not displaying it in the way im trying to acheive .. It pulls back a mess ..
Thanks
Posted: Fri Mar 18, 2005 9:39 pm
by hongco
can you post up here the structures of the two tables, and what need to be querried and shown up on your page please?
Thanks
Posted: Fri Mar 18, 2005 9:58 pm
by Jim_Bo
Hi,
Heres the table structures and the data im trying to pull out ..
vb3_thread
threadid title firstpostid lastpost forumid pollid open replycount postusername postuserid lastposter dateline views iconid notes visible sticky votenum votetotal attach similar
vb3_post
postid threadid parentid username userid title dateline pagetext allowsmilie showsignature ipaddress iconid visible attach
Im trying to display the 5 most recent threads with the most recent post underneath each thread..
"vb3_thread"
postusername title views replycount
"vb3_post"
username pagetext
Thanks ..
Posted: Sat Mar 19, 2005 5:47 pm
by Jim_Bo
Hi,
Could someone please put me onto a good tutorial that explains table joins in a simplified manner ..
I am able to pull out the 5 most recent threads .. no problems .. But no matter what I try .. I cant get the most recent post under each thread .. I just keep getting jumbled data echoed back ..
Thanks ..
Posted: Sat Mar 19, 2005 6:11 pm
by John Cartwright
Posted: Sun Mar 20, 2005 5:07 am
by Jim_Bo
Hi,
Thanks for the link ..
I have nearly got it all sorted .. but for some reason only the title wont display .. Here is what I have it maybe a typo .. But I not sure why all the data echoes back fine except $title .. ?
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 ORDER BY vb3_post.threadid DESC LIMIT 5";
$result = mysql_query($sql) or die ("Query failed");
while ($row = mysql_fetch_array($result)) {
$threadid = $row['threadid'];
$title = $row['title'];
$replycount = $row['replycount'];
$postusername = $row['postusername'];
$lastposter = $row['lastposter'];
$dateline = $row['dateline'];
$views = $row['views'];
$username = $row['username'];
$pagetext = $row['pagetext'];
$threadid = $row['threadid'];
Thanks ..
Posted: Sun Mar 20, 2005 6:55 am
by phpScott
Im not sure about the spelling but it looks like you have
twice once on line 5 the other on line 14.
Also maybe it is just the cut paste that did it but you missing the final } in the posted script.
Posted: Sun Mar 20, 2005 10:35 am
by hongco
hi Jimbo,
sorry, I couldn't reply to your post because of my cable connection failure
Posted: Sun Mar 20, 2005 2:54 pm
by Jim_Bo
Hi,
Yea 2x threadid doesnt make a difference .. also the closing tag is further down the script ..
I Guess I have it all wrong .. I see that now someone posted a few times in the same thread .. the limit 5 bought back threads 26, 27, 27, 27, 27 .. But last night before someone posted a few posts in thread 27 I was seeing threads 26, 27, 28, 29,30, with views, replys, poster, lastposter, newest reply for each thread ...
Strange ..
Could someone please help me with the correct query to acheive what I am after ..
Thanks ..
Posted: Sun Mar 20, 2005 5:05 pm
by feyd
GROUP BY thread_id ?
Posted: Sun Mar 20, 2005 5:26 pm
by Jim_Bo
Hi,
Yea that put it back into order .. ie threads 25,26,27,28,29 ..
For some reason the variable $title is been missed out totally ..
Here is a explination of what I am trying to acheive .. Im close with what I have in the query .. But its not quite right ..
I am trying to pull out the 5 most recent threads from vbulletin and the lastest post from each thread .. within a loop .. ie display x5:
Posted by: Topic: Views: Replys:
$postername $title $views $replycount
Last Poster:
$lastposter $pagetext
Here is the structure from the two tables I am trying to join:
vb3_thread
threadid | title | firstpostid | lastpost | forumid | pollid | open | replycount | postusername | postuserid | lastposter | dateline | views | iconid | notes visible | sticky | votenum | votetotal | attach | similar
vb3_post
postid | threadid | parentid | username | userid | title | dateline | pagetext allowsmilie | showsignature | ipaddress | iconid | visible | attach
The query I have so far is:
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.threadid DESC LIMIT 5";
$result = mysql_query($sql) or die ("Query failed");
while ($row = mysql_fetch_array($result)) {
$threadid = $row['threadid'];
$title = $row['title'];
$replycount = $row['replycount'];
$postusername = $row['postusername'];
$lastposter = $row['lastposter'];
$dateline = $row['dateline'];
$views = $row['views'];
$username = $row['username'];
$pagetext = $row['pagetext'];
Any help is greatly appreciated ..
Thanks