Member Avatar for David_139 
    $query = "SELECT  SUM(amount) FROM income_daily";
    $sum = mysqli_fetch_row(mysqli_query($con, $query));
     echo "Total Income : ". "¢" .$sum[0];

     echo '<br>';
    echo '-';
    echo '<br>';

     $query1 = "SELECT  SUM(amount) FROM expenses_daily";
    $sum = mysqli_fetch_row(mysqli_query($con, $query));
    echo "Total Expenses : ". "¢" .$sum[0];
  • 2 Contributors
  • 1 Reply
  • 465 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by alan.davies

Dani AI

Generated

Two quick points before a safe example: , your second mysqli call re-uses $query instead of $query1 (so you run the same query twice). is right that a single-query approach can work, but for clarity and easier error-handling it’s often simplest to fetch each SUM with a prepared statement and subtract in PHP — that also avoids SQL injection when you pass a date.

Here’s a compact, safe pattern (mysqli, PHP 7+). It returns 0 when there are no matching rows and works with or without a date filter.

<?php
// $mysqli = new mysqli(...); // existing connection
// $date = '2018-08-17' or null

function getSum(mysqli $m, string $sql, ?string $date = null) {
    $stmt = $m->prepare($sql);
    if ($date !== null) $stmt->bind_param('s', $date);
    $stmt->execute();
    $stmt->bind_result($sum);
    $stmt->fetch();
    $stmt->close();
    return $sum ?? 0; // treat NULL as zero
}

// overall totals
$incomeSql  = "SELECT SUM(amount) FROM income_daily";
$expenseSql = "SELECT SUM(amount) FROM expenses_daily";

// per-date totals (if you want to filter by date)
// $incomeSql  = "SELECT SUM(amount) FROM income_daily WHERE income_date = ?";
// $expenseSql = "SELECT SUM(amount) FROM expenses_daily WHERE expenses_date = ?";

$income   = getSum($mysqli, $incomeSql, isset($date) ? $date : null);
$expenses = getSum($mysqli, $expenseSql, isset($date) ? $date : null);
$net      = (float)$income - (float)$expenses;

echo 'Total Income: '.number_format($income,2)."\n";
echo 'Total Expenses: '.number_format($expenses,2)."\n";
echo 'Net: '.number_format($net,2)."\n";

Extra tips: make sure amount is DECIMAL (not FLOAT), index date columns if you query by date frequently, validate any variable table/column names before inserting them into SQL, and check $mysqli->error / enable mysqli exceptions during development so silent failures are visible.

Member Avatar for alan.davies

There are a few ways you could do this, depending on your needs. Here's a botch-job, but gives a single value based on a date:

SELECT (SELECT COALESCE(SUM(di.`amount`),0) FROM `daily_income` di WHERE di.`income_date` = ?) - (SELECT COALESCE(SUM(de.`amount`),0) FROM `daily_expenses` de WHERE de.`expenses_date` = ?) AS daily_difference

The question marks are anonymous placeholders for use with PDO or mysqli prepared statements - so you will need to bind these parameters (date). But if you want a nice one-liner with date data you have sanitized: 

echo $mysqli->query("SELECT (SELECT COALESCE(SUM(di.`amount`),0) FROM `daily_income` di WHERE di.`income_date` = '$date') - (SELECT COALESCE(SUM(de.`amount`),0) FROM `daily_expenses` de WHERE de.`expenses_date` = '$date') AS daily_difference")->fetch_object()->daily_difference;

Alternatively of course, you could just retrieve both table data sums individually and subtract using php!

BTW - the "coalesce" may be required as SUM gives NULL if no values exist for it - and therefore the difference will also be NULL, regardless of whether the other SUM value is not null.

Edited by alan.davies
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.