CODEWITHSUNDEEP
mysqlsqldatetimelimit
Magix
Magix

Reputation: 5369

mySQL - limit on datetime

I have a mySQL table containing matchs and their dates (in a DateTime column).

Is it possible to get all matches from day X to X+n, and then all matches from day X+n to day X+2n...?

I would use that to "limit" my results: If I'm on page 1, I get all matches from day 1 to day 5 (for example), on page 2, I get all matches from day 6 to day 10 etc.

I guess I can't use the LIMIT clause because it only limits rows I get (based on a number of rows I want and not on a number of days)

Upvotes: 1

Views: 10456

Answers (2)

hansmei
hansmei

Reputation: 701

You should use the BETWEEN function in MySQL:

$start = 0;
$end = 5;

SELECT *
FROM table
WHERE day 
  BETWEEN DATE(NOW() + INTERVAL $start DAY)
  AND DATE(NOW() + INTERVAL $end DAY)

Adjust the $start and $end variables as you wish using the page number.

Upvotes: 1

chresse
chresse

Reputation: 5817

you can do it with the DATE_ADD function

SELECT * 
FROM table 
WHERE date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-add

Edit:

If you want to use it for pagination you can do it with the following:

$page = 0; //receive it i.e with $_GET, but be aware of sql injection
$intervalInDays = 30;
//create db connection...
$sql = "
SELECT * 
FROM table 
WHERE date 
    BETWEEN DATE_ADD(DATE('your startdate'), INTERVAL (".$page * $interval.") DAY) 
    AND DATE_ADD(DATE('your startdate'), INTERVAL (".($page+1) * $interval.") DAY)";
//query and fetch...

Upvotes: 3

Related Questions