Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
0 votes
2.3k views
in Q2A Core by
retagged by
How to find next question id under a category or tag?

Current question id and tag name or category name will be passed through URL. Is it possible to get next and previous question ids from these parameters?
by
edited by
Anyone can help me on this?
by
It may help other beginners, so sharing..
====
SELECT @cid := c.categoryid FROM qa_categories c
WHERE c.title = 'SQL Server Interview Questions';

SELECT @nqid := IFNULL(p.postid,0) FROM qa_posts p
WHERE p.type='Q' and p.postid > 1446
AND p.categoryid = @cid
ORDER BY postid LIMIT 1;

SELECT @pqid := IFNULL(p.postid,0) FROM qa_posts p
WHERE p.type='Q' and p.postid < 1446
AND p.categoryid = @cid
ORDER BY postid DESC LIMIT 1;

SELECT @cid, @pqid, @nqid;
===========

SELECT @wid := wordid
FROM qa_words
WHERE word = 'tag1';

SELECT @nqid := IFNULL(p.postid,0) FROM qa_posts p
join qa_posttags pt
on p.postid = pt.postid
WHERE p.type='Q' and p.postid > 1446
AND pt.wordid = @wid
ORDER BY p.postid LIMIT 1;

SELECT @pqid := IFNULL(p.postid,0) FROM qa_posts p
join qa_posttags pt
on p.postid = pt.postid
WHERE p.type='Q' and p.postid < 1446
AND pt.wordid = @wid
ORDER BY p.postid DESC LIMIT 1;

SELECT @wid, @pqid, @nqid;
===========

1 Answer

+1 vote
by
Well the following query will work:

SELECT * FROM qa_posts
WHERE type='Q' and postid > [id]
ORDER BY postid LIMIT 1

where [id] is the ID of the current post e.g. 2594 for this one. That gets the next question; for the previous one, use < instead of > and put "ORDER BY postid DESC"

Since you've done some core hacking before I'll assume you're able to make use of that. If not I'm sure gidgreen will be able to provide more details, there are some functions in the code you can use to run queries and such.
by
Hi DisgruntledGoat, thank you..
Is it possible to get next question of particular category or tag?
by
I got the tables.. thanks.. :)
by
Thank you DisgruntledGoat!

I have finally implemented this in live..
http://www.prepare4interview.com/?qa_theme=p
by
Oops, Could you share how u did it...

My site hardly needs it...
by
I am using same query in custom theme to get next id but it [id] not working. any idea?
...