How to get all posts from same day but posted in previous years?-CodePudding

I'm trying to get SELECT FROM all posts that were posted on this day, but previous years. Basically like a history on this day thing.

Here is the code I'm trying to build on:

  public function get_on_this_day_events($lang_id, $limit)
    {
        $sql = "SELECT * FROM (" . $this->query_string() . " AND posts.lang_id = ? WHERE posts.created_at >= CURRENT_DATE ORDER BY posts.created_at DESC LIMIT ?) AS table_posts";
        $query = $this->db->query($sql, array(clean_number($lang_id), clean_number($limit)));
        return $query->result();
    }

CodePudding user response：

for same day in the previous year you can use this condition created_at = DATE_SUB(NOW(),INTERVAL 1 YEAR)

you code after edits :

public function get_on_this_day_events($lang_id, $limit)
        {
            $sql = "SELECT * FROM (" . $this->query_string() . " AND posts.lang_id = ? WHERE posts.created_at = DATE_SUB(NOW(),INTERVAL 1 YEAR)  ORDER BY posts.created_at DESC LIMIT ?) AS table_posts";

            $query = $this->db->query($sql, array(clean_number($lang_id), clean_number($limit)));

            return $query->result();
        }

if you want to retrieve all the previous years

you can try change the codition to day(NOW()) = day(created_at) and Month(NOW()) = Month(created_at) and year(NOW()) != year(created_at)

which means the same day and month but not the year

you code after edits :

public function get_on_this_day_events($lang_id, $limit)
        {
            $sql = "SELECT * FROM (" . $this->query_string() . " 

             WHERE  posts.lang_id = ? AND 
             day(NOW()) = day(posts.created_at) 
             and Month(NOW()) = Month(posts.created_at) 
             and year(NOW()) != year(posts.created_at)

             ORDER BY posts.created_at DESC LIMIT ?) AS table_posts";

                $query = $this->db->query($sql, array(clean_number($lang_id), clean_number($limit)));

                return $query->result();
        }

CodePudding user response：

This was solved, here is the code

public function get_on_this_day_events($lang_id, $limit)
    {
        $sql = "select * from posts where 
                                    (extract(day from posts.created_at) = extract(day from current_timestamp()) 
                                    and extract(month from posts.created_at) = extract(month from current_timestamp())
                                    and extract(year from posts.created_at) < extract(year from current_timestamp())) 
                                    ";
        $query = $this->db->query($sql, array(clean_number($lang_id), clean_number($limit)));
        return $query->result();
    }