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");
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.