Display result by date and or reply date

On my topics list I can display the latest topics by there date_created.

But lets say if there has been a topic created example two days ago with no reply and then some one reply's to it. How to make sure that topic shows at the list.

I have attached a image of the database table

PHP Code:

<?php

class Topic_model extends CI_Model 
{
    public function __construct()
    {
        parent::__construct();
    }

    public function get_active_topics($fid) 
    {

        $data = array();

        $this->db->where('forum_id', $fid);
        $this->db->order_by('date_created', 'desc');
        $query1 = $this->db->get('topics');
            
        foreach ($query1->result_array() as $forum_result)
        {
            $this->db->where('topic_id', $forum_result['topic_id']);
            $this->db->where('reply_id', '0');
            $this->db->order_by('date_created', 'desc');
            $query2 = $this->db->get('topics');

            foreach ($query2->result_array() as $topic_result) 
            {
                $reply = $this->get_reply($topic_result['topic_id']);

                $date1 = date('D M d', $topic_result['date_created']) . ' , ' . date('Y H:i:s a', $topic_result['date_created']);

                $date2 = date('D M d', $reply['date_created']) . ' , ' . date('Y H:i:s a', $reply['date_created']);

                $data[] = array(
                    'title' => $topic_result['title'],
                    'latest_post' => isset($reply['username']) ? $reply['username'] :  $topic_result['user_id'],
                    'latest_post_time' => !isset($reply) ? $date2 : $date1, 
                );

            }

        }

        return $data;

    }

    public function get_reply($reply_id)
    {
        $this->db->select('t.*, u.username');
        $this->db->from('topics t');
        $this->db->join('user u', 'u.user_id = t.user_id');
        $this->db->where('t.reply_id', $reply_id);
        $this->db->order_by('date_created', 'desc');
        $this->db->limit(1);
        $query = $this->db->get();


        if ($query->num_rows() > 0) {
         return $query->row_array();
         }

         return false;
    }
} 


Attached Files

Thumbnail(s)
   

  Topic_model.php (Size: 1.58 KB / Downloads: 62)

There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!

I would have two tables, one for topics and one for replies.

Topics would have a column called 'topic_last_updated' or similar. When a topic is created, the 'topic_last_updated' field is set to be the same as the 'topic_created' date. Whenever a reply is posted in that topic, the topics 'topic_last_updated' field gets set to the date time of the reply.

Then you only have to order_by the 'topic_last_updated' field.

I might even add a 'topic_last_reply_id' so a simple join brings out the last reply details too, so if there is a reply, you can show a snippet of the last reply or the username etc. Also a 'topic_reply_count' that increments for each topic added. Plus 'topic_viewed_count' too. Also a 'topic_visible_to_public' Boolean so admins can disable topics, plus a 'topic_deleted' Boolean for soft deletes, plus probably many more. Anything you need to know about the topic should not rely on further queries. I know you can count replies and add it to the SQL query result set, count views (if you have that data table somewhere) etc, but I do believe in simplicity. How easy would it now be to show topics sorted on the most popular, trending, the highest rated, the most replies etc.

Running queries based on a foreach of the results of another query is always (at least it seems to me) going to be problematic. If you have 100 topics, you are running 100 subsequent queries just to sort them.

Hope that helps,

Paul.

I would have two tables, one for topics and one for replies.

Topics would have a column called 'topic_last_updated' or similar. When a topic is created, the 'topic_last_updated' field is set to be the same as the 'topic_created' date. Whenever a reply is posted in that topic, the topics 'topic_last_updated' field gets set to the date time of the reply.

Then you only have to order_by the 'topic_last_updated' field.

I might even add a 'topic_last_reply_id' so a simple join brings out the last reply details too, so if there is a reply, you can show a snippet of the last reply or the username etc. Also a 'topic_reply_count' that increments for each topic added. Plus 'topic_viewed_count' too. Also a 'topic_visible_to_public' Boolean so admins can disable topics, plus a 'topic_deleted' Boolean for soft deletes, plus probably many more. Anything you need to know about the topic should not rely on further queries. I know you can count replies and add it to the SQL query result set, count views (if you have that data table somewhere) etc, but I do believe in simplicity. How easy would it now be to show topics sorted on the most popular, trending, the highest rated, the most replies etc.

Running queries based on a foreach of the results of another query is always (at least it seems to me) going to be problematic. If you have 100 topics, you are running 100 subsequent queries just to sort them.

Hope that helps,

Paul.

Thanks for reply. I now have worked out how mybb and phpbb does theres I need to create some more tables and columns and few other thing but have the idea now.

Thanks again all ways good to get advice from you.

There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!