[SOLVED] Table joins

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

[SOLVED] Table joins

Post 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 ..
hongco
Forum Contributor
Posts: 186
Joined: Sun Feb 20, 2005 2:49 pm

Post 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
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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
hongco
Forum Contributor
Posts: 186
Joined: Sun Feb 20, 2005 2:49 pm

Post by hongco »

ORDER BY vb3_thread.threadid DESC

"DESC" = descending (latest thread shows first)
"ASC" = ascending (earlier thread shows first)
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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
hongco
Forum Contributor
Posts: 186
Joined: Sun Feb 20, 2005 2:49 pm

Post 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
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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 ..
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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 ..
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

doesnt get any better than this ;)

http://dev.mysql.com/doc/mysql/en/join.html
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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 ..
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post by phpScott »

Im not sure about the spelling but it looks like you have

Code: Select all

$threadid = $row['threadid'];
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.
hongco
Forum Contributor
Posts: 186
Joined: Sun Feb 20, 2005 2:49 pm

Post by hongco »

hi Jimbo,
sorry, I couldn't reply to your post because of my cable connection failure
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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 ..
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

GROUP BY thread_id ?
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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
Post Reply