How to efficiently SUM and SUBTRACT from two conditionally linked tables using PHP and MySQL

Question

Is there a more efficient solution for the below query. I have tried to research the topic to the best of my ability but it's difficult to know what to actually search for...

$tenant_balance = 0;

$total_charge_amount_query = mysqli_query($con, "
    SELECT  tenant_charge_id, tenant_charge_total_amount
        FROM  accounts_tenant_charge
        WHERE  tenant_charge_tenancy_id='{$tenancy_details['tenancy_id']}'"
     ) or die(mysql_error());  

while($total_charge_amount_row = mysqli_fetch_array( $total_charge_amount_query )) {

    $tenant_balance = $tenant_balance +  $total_charge_amount_row['tenant_charge_total_amount'];

    $total_payment_amount_query = mysqli_query($con, "
        SELECT  tenant_charge_payment_amount
            FROM  accounts_tenant_charge_payment
            WHERE  tenant_charge_payment_tenant_charge_id =
                    '{$total_charge_amount_row['tenant_charge_id']}"
                 ) or die(mysql_error());

    while($total_payment_amount_row = mysqli_fetch_array( $total_payment_amount_query )) {

        $tenant_balance = $tenant_balance - $total_payment_amount_row['tenant_charge_payment_amount'];

    }
}

echo '£' . number_format($tenant_balance, 2, '.', ',');

I have added the database table structure and some data below.

Table #1

-- phpMyAdmin SQL Dump
-- version 4.0.10.7
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 16, 2015 at 01:50 PM
-- Server version: 5.1.73-cll
-- PHP Version: 5.4.23

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `propsyst_atlas`
--

-- --------------------------------------------------------

--
-- Table structure for table `accounts_tenant_charge`
--

CREATE TABLE IF NOT EXISTS `accounts_tenant_charge` (
  `tenant_charge_id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_charge_date` date DEFAULT NULL,
  `tenant_charge_payment_terms` tinyint(4) DEFAULT NULL,
  `tenant_charge_tenancy_id` int(11) DEFAULT NULL,
  `tenant_charge_notes` text COLLATE utf8_bin,
  `tenant_charge_total_amount` decimal(10,2) DEFAULT NULL,
  `tenant_charge_date_created` date DEFAULT NULL,
  `tenant_charge_date_updated` date DEFAULT NULL,
  `tenant_charge_created_by` int(11) DEFAULT NULL,
  `tenant_charge_updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`tenant_charge_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=17 ;

--
-- Dumping data for table `accounts_tenant_charge`
--

INSERT INTO `accounts_tenant_charge` (`tenant_charge_id`, `tenant_charge_date`, `tenant_charge_payment_terms`, `tenant_charge_tenancy_id`, `tenant_charge_notes`, `tenant_charge_total_amount`, `tenant_charge_date_created`, `tenant_charge_date_updated`, `tenant_charge_created_by`, `tenant_charge_updated_by`) VALUES
(15, '2015-06-22', 1, 25, '', '180.00', '2015-06-14', '2015-06-14', 1, 1),
(14, '2015-06-15', 1, 25, '', '550.00', '2015-06-14', '2015-06-14', 1, 1),
(16, '2015-06-27', 1, 25, '', '10.00', '2015-06-14', '2015-06-14', 1, 1);

Table #2

-- phpMyAdmin SQL Dump
-- version 4.0.10.7
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 16, 2015 at 01:51 PM
-- Server version: 5.1.73-cll
-- PHP Version: 5.4.23

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `propsyst_atlas`
--

-- --------------------------------------------------------

--
-- Table structure for table `accounts_tenant_charge_payment`
--

CREATE TABLE IF NOT EXISTS `accounts_tenant_charge_payment` (
  `tenant_charge_payment_id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_charge_payment_date` date DEFAULT NULL,
  `tenant_charge_payment_amount` decimal(10,2) DEFAULT NULL,
  `tenant_charge_payment_method` tinyint(4) DEFAULT NULL,
  `tenant_charge_payment_tenant_charge_id` int(11) DEFAULT NULL,
  `tenant_charge_payment_notes` text COLLATE utf8_bin,
  `tenant_charge_payment_date_created` date DEFAULT NULL,
  `tenant_charge_payment_date_updated` date DEFAULT NULL,
  `tenant_charge_payment_created_by` int(11) DEFAULT NULL,
  `tenant_charge_payment_updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`tenant_charge_payment_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=12 ;

--
-- Dumping data for table `accounts_tenant_charge_payment`
--

INSERT INTO `accounts_tenant_charge_payment` (`tenant_charge_payment_id`, `tenant_charge_payment_date`, `tenant_charge_payment_amount`, `tenant_charge_payment_method`, `tenant_charge_payment_tenant_charge_id`, `tenant_charge_payment_notes`, `tenant_charge_payment_date_created`, `tenant_charge_payment_date_updated`, `tenant_charge_payment_created_by`, `tenant_charge_payment_updated_by`) VALUES
(9, '2015-06-15', '550.00', 2, 14, '', '2015-06-14', '2015-06-14', 1, 1),
(10, '2015-06-22', '50.00', 2, 15, '', '2015-06-16', '2015-06-16', 1, 1);

Answer 1

I guess the point is to get rid of several queries and two PHP loops. With no need for loops, this query the calculate the total charge, payments and balance in one SQL query:

SELECT
    tenant_charge_tenancy_id,
    sum(tenant_charge_total_amount) as charge,
    IFNULL(sum(payments.payment), 0) as payment,
    sum(tenant_charge_total_amount) - IFNULL(sum(payments.payment), 0) as balance
FROM accounts_tenant_charge
LEFT OUTER JOIN (
    SELECT
        tenant_charge_payment_tenant_charge_id,
        sum(tenant_charge_payment_amount) as payment
    FROM accounts_tenant_charge_payment
    GROUP BY tenant_charge_payment_tenant_charge_id
) as payments ON tenant_charge_id = tenant_charge_payment_tenant_charge_id
WHERE tenant_charge_tenancy_id= 25
GROUP BY tenant_charge_tenancy_id

Instead of 25 you can put the $tenancy_details['tenancy_id'] in your PHP code. By removing the WHERE tenant_charge_tenancy_id= ... You can use this query to fetch all balances.