Loop between dates and show records for each date

Question

select * from myTable where dates between "xxxx-xx-xx" and "yyyy-yy-yy" 

I want the result of this table for each date. When myTable doesn't contain record for that date, each fields for that date will be null.

Answer 1

This needs dynamic date generation and from my previous answer MySQL show count of 0 for dates with no records

Here is what you can do in your case

select 
t1.dates as dates,
t2.id
from
(
  select a.Date as dates 
  from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
  ) a
  where a.Date BETWEEN '2014-01-01' AND '2014-01-10'
)t1
left join
(
  select * from myTable
)t2
on t2.dates = t1.dates

DEMO

Answer 2

$from = '2014-07-01';
$to = '2014-07-06';
$empty_row = array(
    'text' => null
);

$from_timestamp = strtotime($from . ' midnight');
$to_timestamp = strtotime($to . ' midnight');

$dates = range($from_timestamp, $to_timestamp, 86400);

$rows_with_dates = array();

foreach($dates as $date) {
    $date = date('Y-m-d', $date);
    $rows_with_dates[$date] = $empty_row + array('date' => $date);
}

$connection = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASS);
$statement = $connection->prepare("SELECT * FROM `stackoverflow` WHERE `date` BETWEEN '$from' AND '$to'");
$statement->execute();
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

foreach($rows as $row){
    $rows_with_dates[$row['date']] = $row;
}

print_r($rows_with_dates);