upper limit for active records in codeigniter

Question

I have this function in my model the purpose of it is to get the stats of the past 30 days starting from the actual day, it was working fine before i reach 30th day then I found out it is counting from the oldest date, so I flipped the order_by from "ase" to "desc" but it seems still going back and counting before the oldest day and not giving me that data I want, so I'm wondering if there any way using codeigniter and give a starting point for the "limit" which should be the actual date.

function graph_data($id_person)
{
    $this->db->limit(30);   // get data for the last 30 days including the curent day

    $this->db->where('personid',$id_person);
    $this->db->order_by('date', 'ase');
    $query = $this->db->get('stats');   

    foreach($query-> result_array() as $row)
    {
     $data[] = $row;
    }    
    return $data;
}

Please refer to this page: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html — hjpotter92, Mar 18 '12 at 03:09
Did you mean to use `ASC`? It's not `ase`... What data type is `date`? — Wesley Murch, Mar 18 '12 at 03:29
No I mean ase, and the type of the date is just DATE in the table — Kamel Labiad, Mar 18 '12 at 03:46

score 1 · Answer 1 · edited May 23 '17 at 12:01

1

limit returns in your case the last 30 rows. It has nothing to do with dates, unless you only add a row each day.

try using mysql Between

SELECT  DATE_FORMAT(create_date, '%m/%d/%Y')
FROM    mytable
WHERE   create_date BETWEEN SYSDATE() - INTERVAL 30 DAY AND SYSDATE()
ORDER BY create_date ASC

Source -- https://stackoverflow.com/a/2041619/337055

edited May 23 '17 at 12:01

Community

1
1

answered Mar 18 '12 at 04:21

Philip

4,592
2
20
28

is it necessary to put DATE_FORMAT? – Kamel Labiad Mar 18 '12 at 19:35

score 1 · Accepted Answer · answered Mar 18 '12 at 22:14

I finally found the solution by giving a range or days, 29 days before the actual day without using BETWEEN and that works fine

<?php
function graph_data($id_person)
{   
$today = date('Y-m-d');
$lastdate = date('Y-m-d', strtotime('-29 days', strtotime($today)));// 30 days ago

$this->db->where('personid',$id_person);
$this->db->where("date <= '$today'");
$this->db->where("date >= '$lastdate'");
$this->db->order_by('date', 'ase');

$query = $this->db->get('stats');   

$data = array();

foreach($query-> result_array() as $row)
{
 $data[] = $row;
}    

return $data;

}

upper limit for active records in codeigniter

2 Answers2