Generate date from sunday except the existing value, in 1 Week Range

This is the result of my query right now with data getting 1 result only.

This is my query:

$merid=20;

SELECT SUM(orders.total) as weektotal , DAYNAME(orders.payment_date) as dayname, 
orders.*, customers.first_name, customers.last_name, customers.contact, customers.email, 
addresses.address, addresses.remarks FROM orders 
INNER JOIN customers ON customers.id = orders.member_id 
INNER JOIN add1 ON add1.custom_id = customers.id 
WHERE orders.status = 'Complete'  AND orders.seller_id ='$merid' 
AND orders.payment_date >= NOW() + INTERVAL -7 DAY 
GROUP BY DATE(orders.payment_date) ORDER BY orders.id ASC

I am trying to populate to get the dates the full week beginning Sunday except the existing Tuesday .

Can someone give guidelines on where to begin? Is the logical process can be done here in SQL or do I need to create the logic using PHP?

Answer

You can use a recursive subquery to generate the datename you want:

with recursive dates as (
      select date('2020-12-20') as dte 
      union all
      select dte + interval 1 day
      from dates
      where dte < '2020-12-26'
     )
select d.dte, dayname(d.dte), q.id, 
       coalesce(q.weektotal, 0) as weektotal
from dates d left join
     (<your query here>) q
     on q.date_time >= d.dte and
        q.date_time < d.dte + interval 1 day;