J. Shearon
J. Shearon

Reputation: 23

Creating an invoice from SQL query without putting query in a loop

My business has a simple invoicing system written by me a few years ago in php with a MySQL database. When I wrote it, I knew just enough to be dangerous. While it does work, a lot of the code is very inefficient, slow and poorly conceived, so I'm re-writing some of it in the hopes of fixing it.

There are two tables: customers and transactions. When invoices need to be created, there are two queries, the second unfortunately in a while loop. Here is some simplified pseudo-code to show what I'm doing:

// Get list of customers who owe something
SELECT name, address 
FROM customers 
WHERE 
   (SELECT COUNT(*) FROM transactions 
    WHERE transactions.customer_id = customers.id 
    AND owed > 0) 
  > 0
ORDER BY address

//loop through that result and query the transactions table to get a   
//list of charges for each customer. Like:

while ($row = customer_array()) {
    echo name_and_address;

    SELECT * FROM transactions WHERE id = $row['customer_id']
      AND owed = TRUE ORDER BY date

      while ($row = transactions_array()) {
        echo each_transaction_row;

}

So obviously the nested loops, subqueries and queries in loops are bad, worse and slow. I've tried joins but can't seem to figure out how to make them work correctly in this context. What is the correct way to do something like this? Can it be done with a single query?

Table structure:

CREATE TABLE `customer_data` (
`account_id` int(6) NOT NULL AUTO_INCREMENT,
`service_address` varchar(255) DEFAULT NULL,
`service_zipcode` int(5) DEFAULT NULL,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`billing_address1` varchar(255) DEFAULT NULL,
`billing_address2` varchar(255) DEFAULT NULL,
`billing_city` varchar(255) DEFAULT NULL,
`billing_state` varchar(2) DEFAULT NULL,
`billing_zipcode` varchar(20) DEFAULT NULL,
`phone1` varchar(100) DEFAULT NULL,
`phone2` varchar(100) DEFAULT NULL,
`quoted_price` int(6) DEFAULT NULL,
`current_price` int(6) DEFAULT NULL,
`original_interval` varchar(10) DEFAULT NULL,
`current_interval` varchar(7) DEFAULT NULL,
`temp_interval` int(5) NOT NULL,
`email` varchar(100) DEFAULT NULL,
`origin_date` varchar(20) DEFAULT NULL,
`remarks` text,
`crew_remarks` text NOT NULL,
`customer_type` varchar(10) DEFAULT NULL,
`perm_crew_assign` int(5) NOT NULL DEFAULT '0',
`temp_crew_assign` int(5) NOT NULL,
`date_last_service` date DEFAULT NULL,
`next_scheduled_date` date DEFAULT NULL,
`excluded_days` varchar(255) NOT NULL,
`special_instructions` text NOT NULL,
`inactive` tinyint(1) NOT NULL DEFAULT '0',
`location` varchar(255) NOT NULL,
`sent_letter` date NOT NULL,
`date_added` datetime NOT NULL,
`email_notify` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1687 DEFAULT CHARSET=utf8

CREATE TABLE `transactions` (
`transaction_id` int(10) NOT NULL AUTO_INCREMENT,
`account_id` int(6) NOT NULL,
`crew_id` int(3) NOT NULL,
`date_performed` date NOT NULL,
`date_recorded` datetime NOT NULL,
`price` int(6) NOT NULL,
`amount_paid` int(6) NOT NULL,
`description` varchar(255) NOT NULL,
`user` varchar(255) NOT NULL,
`status` int(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`transaction_id`)
) ENGINE=InnoDB AUTO_INCREMENT=69233 DEFAULT CHARSET=latin1

Upvotes: 2

Views: 486

Answers (2)

Sam Phomsopha
Sam Phomsopha

Reputation: 257

Yes you can do this in one query, ordering by account_id. In your 'transactions' table account_id should be indexed, since you'll be using it to join to 'customer_data'.

$st_date = '2016-01-01';
$end_date = '2016-02-01';
//select sql
SELECT c.name,c.account_id, ts.*
FROM customer_data c
INNER JOIN transactions ts ON (c.account_id = ts.account_id)
WHERE ts.price > 0 and ts.date_performed between $st_date and $end_data

$current_account_id = null;
while ($row = $results) {
   if ($current_account_id != $row['account_id']) {
      //print customer name, details, etc..
   }
   //print the transactions detail
   echo $results['transaction_id']; //etc...

   //keep track of the current customer
   $current_account_id = $row['account_id']
}

Upvotes: 1

ryantxr
ryantxr

Reputation: 4219

You can do this with one query and one loop. You can join the two tables and sort the result. Then when the name changes, you know you are starting a new customer.

SELECT c.name, c.address, t.*
FROM customers  AS c
JOIN transactions AS t ON (t.customer_id = c.customers.id)
WHERE c.owed > 0
ORDER BY `c`.`name`

// Data looks like this
// | customer.name | customer.address | transaction.* |

$prevName = null;
$prevCustTransList = [];
$prevCustTransTotalOwed = 0;
while ($row = result_array()) { // pseudo code
    if ( $row['c.name'] != $prevName ) {

        if ( $prevName != null  && $prevCustTransTotalOwed > 0 ) {
            echo $prevName; // pseudo code
            foreach($prevCustTransList as $t) {
                echo $t; // pseudo code
            }
        }

        $prevName = $row['c.name'];
        $prevCustTransTotalOwed = 0;
        $prevCustTransList = [];
    }
    // Keep the list of transactions to print out later.
    $prevCustTransList[] = transaction data; // pseudo code
    // Keep a running total of the amount owed
    // Replace with actual calculation
    $prevCustTransTotalOwed += transaction.price;
}

Upvotes: 1

Related Questions