mysql between dates function - Printable Version

mysql between dates function - Printable Version

+- 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 between dates function (/showthread.php?tid=3748)

mysql between dates function - El Forum - 10-19-2007

[eluser]megabyte[/eluser]
OK, I've succesfully done this, until I figured out it only works if your current date can allow a 10 days to be subtracted.

How do you do this so it wouldnt matter what date it was?

here's what I got, which is limited if its the first of the 10th of the month or less.

Code:
function _show_web_stats()

    {

    $this->db->select('UserId, date_format(Date,\'%Y-%m-%d\') as Date, count(Date) as Visitors, SUM(Hits) as NoOfHits');

    $this->db->groupby("Date"); 

    $this->db->where('UserId', $this->userid);

    $then = date('Y-m-d', now());

    $then = explode('-', $then);

    $then[2] = $then[2] - 15;

    $past = implode('-', $then);

    $this->db->where("Date between '$past' and 'Date' ");

    $this->db->orderby("Date", "asc"); 

    //returns the query string

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

    return $query;

    }

I need help, lol. I've searched the net and hit a learning curve here.

mysql between dates function - El Forum - 10-20-2007

[eluser]Jim OHalloran[/eluser]
The strtotime function in PHP is your friend here. Try replacing this...

Code:
$then = date('Y-m-d', now());

    $then = explode('-', $then);

    $then[2] = $then[2] - 15;

    $past = implode('-', $then);

... with this....

Code:
$past = strtotime('-15 days');

strtotime is incredibly flexible, and can do a lot more than just adding/subtracting days from a date. Check out the PHP documentation for strtotime for more information.

Unix timestamps are just an integer representing the number of seconds since midnight, 1 January 1970. This makes date/time manipulation appear easier, because you can just add/subtract the necessary number of seconds, e.g.:

Code:
$seconds_in_a_day = 60 * 60 * 24;

    $past = now() - ($secs_in_a_day * 15);

The downside of that is that it won't account for daylight savings time changes, whereas strtotime does so correctly.

Quote:I need help, lol. I've searched the net and hit a learning curve here.

That's what the community is here for Smile

Jim.

mysql between dates function - El Forum - 10-22-2007

[eluser]megabyte[/eluser]
Thanks a lot Jim, that function is going to help me out a lot.