Skip to main content
T
The_FedEx_Guy
Inspiring
January 27, 2009
Question

Calculate total outstanding balance

  • January 27, 2009
  • 21 replies
  • 2505 views
I have 3 tables: Jobs, Customer, Balance

The "jobs" table holds all info on the job and also holds "job_cost" which is the price.

I now need to search by customer, his cost in total for every job he has and then update the job once calculated how much he has paid and how much is remaining from the total cost for all jobs.

This is the first time trying to do calculations and I am not sure what to do.

My table and all details are in the code below:

CREATE TABLE IF NOT EXISTS `balance` (
`bal_id` int(6) NOT NULL auto_increment,
`pay_date` varchar(8) default NULL,
`amount` varchar(11) default NULL,
`outstanding` varchar(11) default NULL,
`cust_id` varchar(8) default NULL,
PRIMARY KEY (`bal_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `customer` (
`cust_id` int(8) NOT NULL auto_increment,
`cust_address` text,
`cust_tel` varchar(12) default NULL,
`cust_mobile` varchar(12) default NULL,
`cust_email` varchar(80) default NULL,
`cust_website` varchar(80) default NULL,
`cust_name` varchar(20) default NULL,
`cust_business_name` varchar(30) NOT NULL,
PRIMARY KEY (`cust_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=73 ;


++++++++++++++++
Currently I have
++++++++++++++++
mysql_select_db($database_db, $db);
$query_total_cost = "SELECT t1.*, t2.*, t3.SUM(CAST(job_cost) AS DECIMAL(6,2)) AS total_amount
FROM customer as t1, balance as t2, jobs as t3
WHERE t1.cust_business_name = t3.cust_business_name
GROUP BY t1.cust_id";

But this does not seem to work and outputs:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS DECIMAL(6,2)) AS total_amount FROM customer as t1, balance as t2, job' at line 1
This topic has been closed for replies.

21 replies

Show previous replies
N
Newsgroup_User
Inspiring
January 29, 2009
The_FedEx_Guy wrote:
> Because the user making the invoice will select one or more jobs_id's to add to
> the invoice and then it displays on screen the selected invoices.
>
> I then need to commit them to the database.
>
> Its not how I would have done it.
>
> This project was only meant to keep track of jobs that come in. But the GIT
> that I work for is too lazy to use Sage Accounts.

Ahh, I see, this is getting tricky. One way would be to have yet another
table, lets call it invoice_jobs, it would have invoice number and job
number as primary keys, which will prevent them from being duplicated.
Your invoice would have a repeat region to show the jobs associated with
the invoice. You could create a running total on the page which gets
inserted into the invoice table.

I feel for you, it sounds like its getting very awkward.

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
T
The_FedEx_GuyAuthor
Inspiring
January 29, 2009
Because the user making the invoice will select one or more jobs_id's to add to the invoice and then it displays on screen the selected invoices.

I then need to commit them to the database.

Its not how I would have done it.

This project was only meant to keep track of jobs that come in. But the GIT that I work for is too lazy to use Sage Accounts.

N
Newsgroup_User
Inspiring
January 29, 2009
The_FedEx_Guy wrote:
> Hi Dooza,
> I'm just working out the DFD for the database for Invoice but I do not know
> how in PHP to add more than one job_id in the invoice table for the invoice_no
> of 1410
> I think im going to get stuck.
>
> I need a link entity i think

Why do you need more than one job_id in the invoice table?

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
T
The_FedEx_GuyAuthor
Inspiring
January 29, 2009
Hi Dooza,
I'm just working out the DFD for the database for Invoice but I do not know how in PHP to add more than one job_id in the invoice table for the invoice_no of 1410
I think im going to get stuck.

I need a link entity i think
N
Newsgroup_User
Inspiring
January 28, 2009
The_FedEx_Guy wrote:
> That makes more sense.
> So shall I delete my balance table?

Yes

> What I was thinking is an invoice table, because I am making the invoices but
> they are not held anywhere, I need an invoice number to start 001410

Thats a sensible idea. Have a int field as your primary key that
auto-increments, you should be able to set the starting number to 1410,
and then use some clever technique maybe to format it with the leading
zeros.

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
T
The_FedEx_GuyAuthor
Inspiring
January 28, 2009
That makes more sense.
So shall I delete my balance table?

What I was thinking is an invoice table, because I am making the invoices but they are not held anywhere, I need an invoice number to start 001410
N
Newsgroup_User
Inspiring
January 28, 2009
The_FedEx_Guy wrote:
> Hi,
> The thing is I need to beable to add an amount to take off the job_cost field.
>
> So if one customer has 2 jobs and both total ?250 and he wants to pay only ?50
>
> I need to enter ?50 into the textbox and then it should update the figures
> live then insert them to the database
>
> I dont have a job invoice table. Are you making that on the fly?

jobs.invoice just referred to the invoice amount field in the jobs table.

How about a payment table? All payments get recorded in there, so you
can keep track of what came in and when. Each payment record is linked
to the customer and the job.

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
T
The_FedEx_GuyAuthor
Inspiring
January 28, 2009
Hi,
The thing is I need to beable to add an amount to take off the job_cost field.

So if one customer has 2 jobs and both total £250 and he wants to pay only £50

I need to enter £50 into the textbox and then it should update the figures live then insert them to the database

I dont have a job invoice field in my jobs table . Are you making that on the fly?
N
Newsgroup_User
Inspiring
January 27, 2009
The_FedEx_Guy wrote:
> Hi Dooza
> I havent done this type of Database in almost 5 years since I left Uni.
>
> I have done it this way instead:
>
> mysql_select_db($database_db, $db);
> $query_total_cost = "SELECT SUM(job_cost) AS Total_Bal
> FROM customer LEFT JOIN jobs ON customer.cust_business_name =
> jobs.cust_business_name
> WHERE jobs.cust_business_name = '$_REQUEST[cust_business_name]'";
> $total_cost = mysql_query($query_total_cost, $db) or die(mysql_error());
> $row_total_cost = mysql_fetch_assoc($total_cost);
> $totalRows_total_cost = mysql_num_rows($total_cost);
>
> It works to find the total. Now I just need to make sure I can update the
> balances table.

Give me an example of the fields you want displayed, and I will see what
I can do.

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html
T
The_FedEx_GuyAuthor
Inspiring
January 28, 2009
Basically mate,
I'm trying to load the total amount that the customer owes from his various jobs and add them to the "outstanding" field of the balance table.

I then have a textbox called "paid" and I want to calculate the new amount the customer has paid and then update the "outstanding" field to hold the new value.

The calculation I have at the moment is in the code below:
$totalled = $row_total_cost['Total_Bal'] - $_GET[paid];

Ideally when I add the job, I want to add the job, I need the cost added to whatever is in outstanding field.

But I have not created anything for that.


T
The_FedEx_GuyAuthor
Inspiring
January 27, 2009
Hi Dooza
I havent done this type of Database in almost 5 years since I left Uni.

I have done it this way instead:

mysql_select_db($database_db, $db);
$query_total_cost = "SELECT SUM(job_cost) AS Total_Bal
FROM customer LEFT JOIN jobs ON customer.cust_business_name = jobs.cust_business_name
WHERE jobs.cust_business_name = '$_REQUEST[cust_business_name]'";
$total_cost = mysql_query($query_total_cost, $db) or die(mysql_error());
$row_total_cost = mysql_fetch_assoc($total_cost);
$totalRows_total_cost = mysql_num_rows($total_cost);

It works to find the total. Now I just need to make sure I can update the balances table.
Show more replies
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices