Displaying results from two tables

General PHP Help
#1

Hi,

I hope some one can help, this is not my strong suit.

I have two tables, *Sales Leger and Payments

I want to display a list of the sales ledger records with a sum of the amount outstanding (this will be the *sales ledger.invoice total minus the sum of payments.payment amount). There will only be one sales ledger record but there can be many payment records that link to it.

I can already sisplay a list of the sales ledger records using this:

$sl_query = “SELECT * FROM *Sales Ledger WHERE Customer ID = :customersID”;
$sl_query = $db->bindVars($sl_query, ‘:customersID’, $_SESSION[‘customer_id’], ‘integer’);
$sl = $db->Execute($sl_query);

$alArray = array();
while (!$sl->EOF) {

$slArray[] = array(‘Transaction Date’=>$sl->fields[‘Transaction Date’],
‘Sales Ledger ID’=>$sl->fields[‘Sales Ledger ID’],
‘Doc Type’=>$sl->fields[‘Doc Type’],
‘Document Number’=>$sl->fields[‘Document Number’],
‘Work Order ID’=>$sl->fields[‘Work Order ID’],
‘order number’=>$sl->fields[‘order number’],
‘invoice net’=>$sl->fields[‘invoice net’],
‘invoice vat’=>$sl->fields[‘invoice vat’],
‘invoice total’=>$sl->fields[‘invoice total’],
‘due date’=>$sl->fields[‘due date’],

);

$sl->MoveNext();
}

<?php foreach($slArray as $sl) { ?> <?php } ?>
Date Due Date T Number Work Order Order Number Net VAT Total View
<?php echo zen_date_short($sl['Transaction Date']); ?> <?php echo zen_date_short($sl['due date']); ?> <?php echo $sl['Doc Type']; ?> <?php echo $sl['Document Number']; ?> <?php echo $sl['Work Order ID']; ?> <?php echo $sl['order number']; ?> £<?php echo $sl['invoice net']; ?> £<?php echo $sl['invoice vat']; ?> £<?php echo $sl['invoice total']; ?> <?php echo ' ' . zen_image_button(BUTTON_IMAGE_VIEW_SMALL, BUTTON_VIEW_SMALL_ALT) . ''; ?>

I want to have the amount due in the same table, I just have no idear how to proceed.

Can any one help?

#2

If you post your DB schema you will get a faster and accurate answer.

#3

I think this is what you asked for:

Table name: *Sales Ledger

Sales Ledger ID, Transaction Date, Posting Date, Doc Type, Document Number, Customer ID, Work Order ID, Work Order Sub ID, order number, invoice net, invoice total, invoice vat, due date, P, state link, original printed, timestamp, internam-marker, devision

Table name: Payments

Payment ID, Payment Date, Payment Method, Payment Amount, PL ID, PL Document, SL ID, SL Document, Ref 1, Ref 2, Cistomer ID, in buffer, poa allocated, doc, Proforma ID, Estimate ID, WO ID, wo allocation marker, ID, timestamp, devision, processed by

The two tables will be linked by *Sales Ledger.Document Number and Payments.SL Document

Thanks in advance

#4

Sort of. I meant the actual sql dump to recreate your tables exactly has you have them. A few related records in each table would also be helpful. It would look like the example below. It would take us too much time to recreate your database by hand. You can do it in a matter of seconds.

[code]-- ----------------------------
– Table structure for score_results

DROP TABLE IF EXISTS score_results;
CREATE TABLE score_results (
id int(11) NOT NULL AUTO_INCREMENT,
schoolID int(11) DEFAULT NULL,
team1_score int(11) DEFAULT NULL,
opponentID int(11) DEFAULT NULL,
team2_score int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

– Records of score_results

INSERT INTO score_results VALUES (‘1’, ‘1001’, ‘6’, ‘2005’, ‘4’);
INSERT INTO score_results VALUES (‘2’, ‘2001’, ‘8’, ‘2010’, ‘3’);
INSERT INTO score_results VALUES (‘3’, ‘2005’, ‘10’, ‘2010’, ‘5’);[/code]

#5

Please find these attached.

_Sales_Ledger.txt (10.3 KB)

Payments.txt (31.5 KB)

#6

You shouldn’t use spaces or characters in your table names.

#7

I know, unfortunatly these came with another system and its too much code to change.

#8

Actually its not at all. Its just a simple rename. You also have an error with your customers column. One table is Customer, the other is Cistomer

To rename the table do this command:
RENAME TABLE *Sales Ledger TO sales_ledger;

I just noticed you have space in field names too. Easiest thing to do is export your database like you just did and do a search and replace spaces with an undescore. It will save you many headaches later. After you export, rename your database tables with _BKUP at the end so you have the original tables saved in the database. Fix the exported files, then re-import.

The first thing to do is get your database straightened out.

Not sure how many files you already have that interact with the database as is, but it is a simple matter of doing a search and replace. It will only take seconds.

You caught me on a good day. If you want to provide me ALL the relevant files, I will do it for you. Email them to me in a zip file. Click on my user name to get my email.

#9

In the mean time, with what you have, here is your query. You can add more columns to the results as you see fit:

[php]SELECT (
(SELECT SUM(*sales ledger.invoice total)
FROM *sales ledger
WHERE *sales ledger.Customer ID = 10074) -
(SELECT SUM(payments.Payment Amount)
FROM payments
WHERE payments.Cistomer ID = 10074) ) AS Total Owed;[/php]

Sponsor our Newsletter | Privacy Policy | Terms of Service