calculate total number of days in specied dates

Question

i have one table with two columns as shown in picture

table columns names are (MAXDATE,AMOUNT).

if you see we have

   first date range (from current date to 20-jan-2010)
   second date range from 20-jan-2010 to 30-jan-2010 
   3rd range is from 20-jan-2010 to 31-jan-2010.

at the execution of page user enter the start and end date.

for example, if user put

      start date: 18-jan-2010
      end date: 23-jan-2010

then he has 2 dates in first options and 3 dates in second options.

what i want to calculate in sql

1. how many days in first range (if any this depends on supplied dates from user)
2. how many days in 2nd range  (if any this depends on supplied dates from user)
3. how many days in 3rd range  (if any this depends on supplied dates from user)

Thanks

Answer 1

Here is an example how to calculate days.

http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html

Answer 2

// Get the dates from the table
$sql = 'SELECT `MAXDATE` FROM `table1` ORDER BY `MAXDATE`';
$result = mysql_query($sql);

// User input
$start_date = strtotime($_POST['start']);
$end_date = strtotime($_POST['end']);

// Array to hold the ranges
$ranges = array();

while($row = mysql_fetch_assoc($result))
{
    $date = strtotime($row['MAXDATE']);
    $num_days = $end_date > $date ? $date - $start_date : $end_date - $start_date;

    if($num_days > 0)
    {
        $ranges[] = $num_days / (24 * 60 * 60);
        $start_date = $date;
    }
    else
    {
        $ranges[] = 0;
    }
}

Answer 3

You can do all of this in MySQL:

SELECT DATEDIFF(NOW(), max_date) as days;
SELECT DATEDIFF(date2, date1) FROM
    (SELECT max_date as date1 FROM table1 LIMIT 1) as s1
    JOIN 
    (SELECT max_date as date2 FROM table1 LIMIT 1 OFFSET 1) as s2;
//etc.