bob_the _builder Posted June 2, 2007 Share Posted June 2, 2007 Hi, I am writing a basic forum. I am struging to grab the last poster for display in the forum index. My table layout is: CREATE TABLE headings ( headingid int(5) NOT NULL auto_increment, heading varchar(50) NOT NULL, description varchar(255) NOT NULL, PRIMARY KEY (headingid) ); CREATE TABLE topics ( topicid int(5) NOT NULL auto_increment, headingid int(5) NOT NULL, userid int(5) NOT NULL, views int(5) NOT NULL, topic varchar(50) NOT NULL, PRIMARY KEY (topicid) ); CREATE TABLE posts ( postid int(5) NOT NULL auto_increment, topicid int(5) NOT NULL, userid int(5) NOT NULL, Post text NOT NULL, added datetime NOT NULL, viewed int(5) NOT NULL, PRIMARY KEY (postid) ); On the page where the headings are displayed I am trying to show the last poster for each heading. I have tried with no luck, here is what I have at the moment which is no where near correct: $sql = mysql_query("SELECT u.first_name, u.first_name, p.added FROM topics as t LEFT JOIN posts as p ON t.topicid = p.topicid LEFT JOIN users as u ON u.userid = p.userid LEFT JOIN headings as h ON h.headingid = t.headingid WHERE t.headingid = h.headingid ORDER BY t.topicid DESC LIMIT 1") or die (mysql_error()); while ($row1 = mysql_fetch_array($sql)) { $sql = mysql_query("SELECT * FROM headings"); while ($row = mysql_fetch_array($sql)) { Thanks Quote Link to comment Share on other sites More sharing options...
bubblegum.anarchy Posted June 2, 2007 Share Posted June 2, 2007 Can anyone confirm this wacky notion: SELECT headings.headingid , headings.heading , topics.topic , posts.userid , posts.added , posts.post FROM headings LEFT JOIN topics ON headings.headingid = topics.headingid LEFT JOIN posts ON topics.topicid = posts.topicid AND concat(posts.topicid, posts.added) IN ( SELECT concat(topicid, max(added)) AS most_recent FROM posts GROUP BY topicid ) GROUP BY headings.headingid Quote Link to comment Share on other sites More sharing options...
bob_the _builder Posted June 4, 2007 Author Share Posted June 4, 2007 Hi, Thanks, that code generates a query error. Im not sure if I should be using one query or 2? Is it explained well enough for what I am trying to acheive? Cheers Quote Link to comment Share on other sites More sharing options...
bubblegum.anarchy Posted June 4, 2007 Share Posted June 4, 2007 Thanks, that code generates a query error. Well, that is fantastic. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.