How effective is SUM and SUBTRACT from two conditionally related tables using PHP and MySQL

Question

Is there a more efficient solution for the following query. I tried to study this topic to the best of my ability, but it’s hard for me to understand what to really look 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 added a 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); `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 -- 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 think the point is to get rid of a few queries and two PHP loops. If there is no need for cycles, this query will calculate the total charge, payments and balance in a single 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 $tenancy_details['tenancy_id'] in your PHP code. By removing WHERE tenant_charge_tenancy_id= ... This query can be used to get all balances.

Answer 2

you request strings from account_tenant_charge with identifier, e.g.

 SELECT tenant_charge_id, tenant_charge_total_amount FROM accounts_tenant_charge WHERE tenant_charge_tenancy_id= ? 

then for each line returned by this request, adding tenant_charge_total_amount to $tenant_balance and requesting (with each tenant_charge_id )

 SELECT tenant_charge_payment_amount FROM accounts_tenant_charge_payment WHERE tenant_charge_payment_tenant_charge_id= ? 

subtracting each tenant_charge_payment_amount from $tenant_balance .

First of all, you can do this with a single request that will be compiled once and executed on the server to provide a single result, preventing the generation of a large number of requests that need to be compiled and executed sequentially, as well as transferring data back and forth between the web server and dbms only to calculate one result.

It will be something like this

 SELECT SUM(x.amount) FROM (SELECT tenant_charge_total_amount amount FROM accounts_tenant_charge WHERE tenant_charge_tenancy_id= ? UNION SELECT -tenant_charge_payment_amount amount FROM accounts_tenant_charge a JOIN accounts_tenant_charge_payment b ON tenant_charge_payment_tenant_charge_id = a.tenant_charge_id WHERE tenant_charge_tenancy_id= ?) x 

Secondly, use bind variables and prepared statements ( see here ). If you create a dynamic query with every other identifier, you force db to compile the query each time, adding overhead to the execution time. Dynamic queries are also susceptible to SQL injection attacks.

SQL is a declarative language, which means that you specify what you want to receive, and dbms will do it in the best way. You must first rely on the db engine to optimize your queries. Looping using result sets to repeat queries is likely to prevent engine optimization and add a lot of avoidable overhead.

See DEMO here .

Answer 3

This request returns all tenant_charge_id and lists the total amount that must be paid, as well as the total amount that has already been paid.

 SELECT charge.tenant_charge_id, charge.tenant_charge_total_amount, SUM(payment.tenant_charge_payment_amount) total_payments FROM accounts_tenant_charge charge LEFT JOIN accounts_tenant_charge_payment payment ON payment.tenant_charge_payment_tenant_charge_id = charge.tenant_charge_id GROUP BY tenant_charge_id 

You can add a WHERE to limit your search to only a subset of tenants.

Answer 4

Try the following:

 SELECT tenant_charge_payment_tenant_charge_id, SUM(amount) balance FROM ( SELECT tenant_charge_payment_tenant_charge_id, tenant_charge_total_amount amount FROM accounts_tenant_charge WHERE tenant_charge_tenancy_id='" . $tenancy_details['tenancy_id'] . "' UNION SELECT tenant_charge_payment_tenant_charge_id, CONCAT('-',tenant_charge_payment_amount) amount FROM accounts_tenant_charge_payment WHERE tenant_charge_payment_tenant_charge_id='" . $total_charge_amount_row['tenant_charge_id'] . "' ) temp GROUP BY tenant_charge_payment_tenant_charge_id