• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
mysql between dates function

#1
[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.

#2
[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.

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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.