Display the events for the current month first then the rest last desc

Question

I have this controller which displays my events by event_date the date format is like 'Y-m-d'

Question Is there away that I could display the events for this month first in the list then the rest of the events after?

Model

public function getevents($filter = array()) {
    $this->db->limit($filter['limit'], $filter['start']);
    $this->db->order_by($filter['order'], $filter['sort']);
    $query = $this->db->get('event');
    return $query->result_array();
}

Controller

<?php

class Events extends Admin_Controller {

    private $error = array();

    public function __construct() {
        parent::__construct();
        $this->load->model('admin/event/events_model');
        $this->load->library("pagination");
    }

    public function index() {

        $this->getlist();
    }

    public function getlist() {

        $this->document->set_title('Events');

        $data['heading_title'] = 'Events';

        $data['breadcrumbs'] = array();

        $data['breadcrumbs'][] = array(
            'text' => 'Dashboard',
            'active' => '',
            'href' => site_url('admin/dashboard')
        );

        $data['breadcrumbs'][] = array(
            'text' => 'Events',
            'active' => 'active',
            'href' => site_url('admin/events')
        );

        if ($this->input->get('order')) {
            $order = $this->input->get('order'); 
        } else {
            $order = 'event_date';
        }

        if ($this->input->get('sort')) {
            $sort = $this->input->get('sort'); 
        } else {
            $sort = 'desc';
        }

        if ($this->input->get('limit')) {
            $limit = $this->input->get('limit'); 
        } else {
            $limit = 5;
        }

        if ($this->uri->segment(3)) {
            $page = $this->uri->segment(3); 
        } else {
            $page = 0;
        }

        $config["base_url"] = base_url('admin/events');
        $config["total_rows"] = $this->events_model->total_count();
        $config["per_page"] = $limit;
        $config["uri_segment"] = 3;
        $config['use_page_numbers'] = FALSE;

        $config['full_tag_open']    = '<nav><ul class="pagination">';
        $config['full_tag_close']   = '</ul></nav>';
        $config['num_tag_open']     = '<li class="page-item"><span class="page-link">';
        $config['num_tag_close']    = '</span></li>';
        $config['cur_tag_open']     = '<li class="page-item active"><span class="page-link">';
        $config['cur_tag_close']    = '<span class="sr-only">(current)</span></span></li>';
        $config['next_tag_open']    = '<li class="page-item"><span class="page-link">';
        $config['next_tagl_close']  = '<span aria-hidden="true">&raquo;</span></span></li>';
        $config['prev_tag_open']    = '<li class="page-item"><span class="page-link">';
        $config['prev_tagl_close']  = '</span></li>';
        $config['first_tag_open']   = '<li class="page-item"><span class="page-link">';
        $config['first_tagl_close'] = '</span></li>';
        $config['last_tag_open']    = '<li class="page-item"><span class="page-link">';
        $config['last_tagl_close']  = '</span></li>';

        $this->pagination->initialize($config);

        $filter = array(
            'order' => $order,
            'sort' => $sort,
            'limit' => $limit,
            'start' => $page 
        );

        $data['events'] = array();

        $events = $this->events_model->getevents($filter);
        foreach ($events as $event) {

            $button = array(
                'type' => 'button',
                'class' => 'btn btn-danger',
                'content' => '<i class="fa fa-trash" aria-hidden="true"></i> Remove',
                'data-toggle' => 'model',
                'data-target' => '#event_model',
                'data-id' => $event['event_id']
            );

            $data['events'][] = array(
                'event_title' => $event['event_title'],
                'event_date' => date('l dS F Y', strtotime($event['event_date'])),
                'href' => anchor('admin/events/update/' . $event['event_id'], 'Edit', array('class' => 'btn btn-primary')),
                'delete' => form_button($button)
            );
        }

        $data["pagination_links"] = $this->pagination->create_links();

        $data['header'] = Modules::run('admin/common/header/index');
        $data['column_left'] = Modules::run('admin/common/column_left/index');
        $data['footer'] = Modules::run('admin/common/footer/index');
        $data['topnavbar'] = Modules::run('admin/common/topnavbar/index');

        $this->load->view('template/event/getlist', $data);
    }
}

So do you basically want to take your array - rip out the entries for the current month and append the leftovers on the end? — TimBrownlaw, Nov 03 '17 at 09:06
Not sure what the "rest" is...So if you have events for August, September, October, November, December and the current month is November. What do you want to do with the rest? What is the "rest"? Just December or do you also want the events from past dates? — TimBrownlaw, Nov 05 '17 at 11:03
please show how you want your view output (this month and "rest"), maybe a mock-up image or similar. thanks — Vickel, Nov 05 '17 at 17:54
check this i hope it helps you for you code way :) https://stackoverflow.com/a/17073469/7246270 — Mantas Stanislovaitis, Nov 05 '17 at 21:10
the question does not require MySQL specific assistance, removed that tag — Paul Maxwell, Nov 08 '17 at 00:10

score 1 · Accepted Answer · answered Nov 09 '17 at 15:59

uasort()

You don't need dedicated year and month table columns for this. Using native MySQL and PHP functions on date format, you can achieve almost any real world example algorithm. Here is what you should do to avoid unnecessary or overwhelming DB calls.

I assume that in this line

$events = $this->events_model->getevents($filter);

you are getting $events that could be something like this

$events = [
    0 => [
        'event_id' => 1,
        'event_date' => '2017-11-16',
    ],
    1 => [
        'event_id' => 2,
        'event_date' => '2017-02-22',
    ],
    2 => [
        'event_id' => 3,
        'event_date' => '2017-12-14',
    ],
    3 => [
        'event_id' => 4,
        'event_date' => '2017-11-16',
    ],
    4 => [
        'event_id' => 5,
        'event_date' => '2017-03-01',
    ],
    5 => [
        'event_id' => 6,
        'event_date' => '2017-12-02',
    ],
];

I didn't represent all fields but few needed for understanding principle

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Welcome extends CI_Controller
{

    public function index()
    {
        $events = [
            0 => [
                'event_id' => 1,
                'event_date' => '2017-11-16',
            ],
            1 => [
                'event_id' => 2,
                'event_date' => '2017-02-22',
            ],
            2 => [
                'event_id' => 3,
                'event_date' => '2017-12-14',
            ],
            3 => [
                'event_id' => 4,
                'event_date' => '2017-11-16',
            ],
            4 => [
                'event_id' => 5,
                'event_date' => '2017-03-01',
            ],
            5 => [
                'event_id' => 6,
                'event_date' => '2017-12-02',
            ],
        ];

        var_dump($events);

        // looping through an array cursor is moving forward 
        // so useing uasort, latest elements will be sorted closer to beginning
        // inversing array before uasort, rest of array keeps 
        // the order that is returned from db
        $events = array_reverse($events, $bool = false);// check yourself if you need true flag here

        try {
            uasort($events, array('self', 'cmp'));
        } catch (\Exception $e) {
            echo $e->getMessage();
        }

        var_dump($events);
    }

    public static function cmp($a)
    {
        $am = date('m', strtotime($a['event_date']));

        if ($am == date('m')) {
            return -1;
        }

        return 1;
    }

}

This should be the way of how you can sort elements by using child key in multidimensional array, and it is way better to use PHP for array operations than having multiple calls to database.

Also, PHP shows really great strength in work over arrays so you should consider using those functions and classes before making more DB calls than you actually need.

Edit:

Just got better resulst with this one:

var_dump($events);

$newArray = [];
foreach($events as $k => $v) {
    if (date('m', strtotime($v['event_date'])) == date('m')) {
        $newArray[] = $v;
        unset($events[$k]);
    }
}

var_dump(array_merge($newArray, $events));
exit;

Thanks just tested it now works fine with your edit. +1 and accepted answer it was a great answer to me — , Nov 11 '17 at 05:40
Thank you. Also, check [this answer](https://stackoverflow.com/questions/3550942/can-you-add-an-if-statement-in-order-by) and play around of how to get correct order from database. Then you can test what time and what resource is used for each method to choose appropriate one wisely. — Tpojka, Nov 11 '17 at 11:13

score 0 · Answer 2 · answered Nov 06 '17 at 06:08

0

You could add $this->db->where('event_date >=', date('Y-m-01')); to your getevents() method, I guess.

answered Nov 06 '17 at 06:08

Brad

1,880
1
12
18

score 0 · Answer 3 · answered Nov 07 '17 at 07:23

0

try replacing the following line of code:

    $this->db->order_by($filter['order'], $filter['sort']);

by the following line:

    $this->db->order_by("event_date", "desc");

And check if your event_date format is ok.

answered Nov 07 '17 at 07:23

Kevin

65
12

@RickJames because it was all ready declared in the controller please check the controller code had no effect what so ever, I have found another way any way – Nov 07 '17 at 20:15

score 0 · Answer 4 · 2017-11-08T00:00:53.310

Solution

I think just creating two arrays was the better option and then merge them. I had to create another column for a separate year and month

By adding the where() worked and or_where() then using array_merge

public function getevents($filter = array()) {

    $data1 = array();
    $data2 = array();

    $this->db->where('month =', date('m'));
    $this->db->order_by($filter['order'], $filter['sort']);
    $current_month_query = $this->db->get('event');

    foreach ($current_month_query->result_array() as $result1) {

        $data1[] = array(
            'event_title' => $result1['event_title'],
            'event_description' => $result1['event_description'],
            'event_date' => date('l dS F Y', strtotime($result1['event_date'])),
        );
    }

    $this->db->where('month >', date('m'));
    $this->db->or_where('month <', date('m'));
    $this->db->order_by($filter['order'], $filter['sort']);
    $other_query = $this->db->get('event');

    foreach ($other_query->result_array() as $result2) {

        $data2[] = array(
            'event_title' => $result2['event_title'],
            'event_description' => $result2['event_description'],
            'event_date' => date('l dS F Y', strtotime($result2['event_date'])),
        );
    }

    return array_merge($data1, $data2);
}

Technically you don't need both arrays... You can simply declare `$data` as an array, and in both of your `foreach` loop, append to the `$data` variable. Saves you the `array_merge` step. — Chin Leung, Nov 10 '17 at 20:02

Display the events for the current month first then the rest last desc

4 Answers4

Solution