CODEWITHSUNDEEP
phpmysqlposts
funkstar33
funkstar33

Reputation: 3

Selecting the last 5 posts from topics?

Hi am trying to select last five topics that posts was made. The query i have i thought would have worked but am really stuck on this one if anyone can help me out please. $query = yasDB_select("SELECT * FROM forumposts INNER JOIN forumtopics ON forumposts.id=forumtopics.id ORDER by forumposts.date DESC limit 5");

Upvotes: 0

Views: 81

Answers (1)

Mihály Petrényi
Mihály Petrényi

Reputation: 26

I recognised a few problems with your query.

Proper solution:

SELECT DISTINCT(forumtopics.name)
FROM forumtopics LEFT JOIN forumposts ON forumposts.topicid = forumtopics.id
ORDER BY forumposts.id DESC LIMIT 5;

If you are interested in more complex data about topics you can do the following:

SELECT * FROM (SELECT DISTINCT(forumtopics.id) FROM forumtopics LEFT
JOIN forumposts  ON forumposts.topicid = forumtopics.id ) topics  LEFT
JOIN forumtopics ON topics.id = forumtopics.id ORDER BY topics.id DESC
LIMIT 5 ;

I used the following test schema:

CREATE TABLE forumposts (   id int,   topicid int,   content text ); 
CREATE TABLE forumtopics (   id int,   name varchar(200) );
INSERT INTO forumtopics SET id = 1, name = "1st topic"; INSERT INTO
forumtopics SET id = 2, name = "2nd topic"; INSERT INTO forumtopics
SET id = 3, name = "3rd topic"; INSERT INTO forumtopics SET id = 4,
name = "4th topic"; INSERT INTO forumtopics SET id = 5, name = "5th
topic"; INSERT INTO forumtopics SET id = 6, name = "6th topic";

INSERT INTO forumposts SET id = 1, topicid=1, content = "1st post";
INSERT INTO forumposts SET id = 2, topicid=3, content = "2nd post";
INSERT INTO forumposts SET id = 3, topicid=3, content = "3rd post";
INSERT INTO forumposts SET id = 4, topicid=4, content = "4th post";
INSERT INTO forumposts SET id = 5, topicid=5, content = "5th post";
INSERT INTO forumposts SET id = 6, topicid=6, content = "6th post";
INSERT INTO forumposts SET id = 7, topicid=6, content = "7th post";

You can test the queries on http://sqlfiddle.com.

Upvotes: 1

Related Questions