Sum data from different table in a week from bill

Question

I can SUM data from different table for today, this month with this code.

For example SUM data for today:

include 'config_connect.php';
$today=date('d');
$thismonth=date('m');
$thisyear=date('Y');

  $sqlx2="SELECT (SUM(total)-SUM(payout)) AS data FROM paid WHERE bill IN (SELECT bill FROM transactionin) AND paydate LIKE '$thisyear-$thismonth-$today'";
  $resultx2=mysqli_query($conn,$sqlx2);
  $row=mysqli_fetch_assoc($resultx2);
  $data43=$row['data'];

echo $data43;

how to SUM data in a week? Thank you.

try to find fixed code to sum data in a week.

Answer 1

Assuming the paydate column in bayar is a DATE or DATETIME data type to work with date comparisons correctly. Then make the query more "generic" so it can be used for a day, a week or a month, you can filter by a date range in all cases. e.g: for a single day use operators >= and < instead of "like"

$today = date('Y-m-d');

$sql = "SELECT (SUM(total)-SUM(payout)) AS data 
        FROM bayar 
        WHERE bill IN (SELECT bill FROM transactionin) 
        AND paydate >= '$today' AND paydate < '$today + INTERVAL 1 DAY'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$data = $row['data'];

echo $data;

Then it is a matter of allowing both dates of the range to be parameters e.g:

$startDay = date('Y-m-d', strtotime('2023-01-01'));
$endDay = date('Y-m-d', strtotime('2023-02-01'));

$sql = "SELECT (SUM(total)-SUM(payout)) AS data 
        FROM bayar 
        WHERE bill IN (SELECT bill FROM transactionin) 
        AND paydate >= '$startDay' AND paydate < '$endDay'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$data = $row['data'];

echo $data;

Note: Using >= with < operators works reliably and predictable if the "end day" is actually the day after the wanted last day of the range. e.g. to get all of January the start date is 2023-01-01 and you get all data >= that point up to 2023-02-01, but not including 2023-02-01. i.e. you get all of January and nothing at all from February.