CODEWITHSUNDEEP
phpmysql
florw.wat
florw.wat

Reputation: 31

mysql select each day record for the last 7 days

how do you select each day records for the last 7 days? if the dates are not available set it to 0. I tried the below but I am not getting any results.

$stmt = $db->prepare("SELECT DATE(order_date),COUNT(id) as 'total' FROM `orders` WHERE `order_date` BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()GROUP BY DATE(order_date)");
$stmt->execute();
$result = $stmt->get_result();

$total = [];
while($row = $result->fetch_object()){
    $total[] = $row->total;
}
print_r($total);

Upvotes: 1

Views: 289

Answers (1)

florw.wat
florw.wat

Reputation: 31

not the actual solution I wanted but I managed to get the results by wrapping the query in a function and calling it 7 times...

function weeklyData($day){
    $stmt = $db->prepare("SELECT order_date from orders where DATE(FROM_UNIXTIME(order_date)) = CURDATE() - INTERVAL ? DAY");
    $stmt->bind_param('s', $day);
    $stmt->execute();
    $stmt->store_result();
    return $stmt->num_rows;
}
$total = [];
for($x = 1; $x <= 7; $x++){
    $total[] = weeklyData($x);
}
print_r($total);

Upvotes: 1

Related Questions