+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL: Get last forum post date (/showthread.php?tid=5648)
MySQL: Get last forum post date - El Forum - 01-29-2008
[eluser]codex[/eluser]
If you've ever made a forum you might know the answer.
I've got a 'forum_posts' table which is setup like this:
post_id
post_cat_id
post_parent_id = in case it's a reply the parent is the post_id, else it's 0
post_subject
post_date
I need to get the last post date as well. If the post has replies, the last date is the last replydate, else it's the original post_date. But since I'm selecting on post_parent_id = 0 it's impossible to get replydates. Can it be done with a smart query (subselect or having etc) or do you have to have a seperate query for this to work?
My model
Code:
function get_forum_posts($category_id)
{
$this->db->select('*');
$this->db->from('forum_posts');
$this->db->where('post_cat_id', $category_id);
$this->db->where('post_parent_id', 0);
$this->db->groupby('post_id');
$this->db->orderby('post_date', 'desc');
$query = $this->db->get();
if ($query->num_rows() > 0) {
return $query->result();
}
else {
return FALSE;
}
}MySQL: Get last forum post date - El Forum - 01-29-2008
[eluser]mironcho[/eluser]
One approach is to use such sub select (sorry - query is not translated into CI AR)
Code:
SELECT
f.*,
(SELECT post_date FROM forum_posts WHERE post_id = f.post_id OR post_parent_id = f.post_id ORDER BY post_date DESC LIMIT 1) AS last_post_date
FROM
forum_posts AS f
WHERE
post_parent_id = '0'
AND post_cat_id = $category_id
ORDER BY
last_post_date DESCOther, faster, solution is to use additional field in your table - last_post_date. Just update it in "master post" whenever there is a change in it's thread.
Hope this helps.
Miro
MySQL: Get last forum post date - El Forum - 01-29-2008
[eluser]codex[/eluser]
Actually, the second approach is not bad at all. Now why didn't I think of that
Thanks!
[quote author="mironcho" date="1201667182"]One approach is to use such sub select (sorry - query is not translated to CI AR)
Code:
SELECT
f.*,
(SELECT post_date FROM forum_posts WHERE post_id = f.post_id OR post_parent_id = f.post_id ORDER BY post_date DESC LIMIT 1) AS last_post_date
FROM
forum_posts AS f
WHERE
post_parent_id = '0'
AND post_cat_id = $category_id
ORDER BY
last_post_date DESCOther, faster, solution is to use additional field in your table - last_post_date. Just update it in "master post" whenever there is a change in it's thread.
Hope this helps.
Miro[/quote]
MySQL: Get last forum post date - El Forum - 01-29-2008
[eluser]elvix[/eluser]
I have a copy of BBpress around for precisely this purpose.
BBpress is a pretty lightweight forum (making it easy to find examples you're looking for), and comes from the Wordpress crew.
BBpress uses last_post_date, if I remember correctly.