[thelist] mySQL queries

Beau Hartshorne beau at members.evolt.org
Mon Aug 12 13:55:01 CDT 2002


Hi,

I'm building an online time/billing application for myself with
php/mysql. I've set up a billing_job table, and a billing_invoice table.
Each job can have one or more invoices associated with it (deposit,
milestones, final).

The tables are set up something like this:

Table: billing_invoice
invoice_id
job_id
number
date
amount

Table: billing_job
job_id
client_id
name
amount
date
active

I want to display a list of jobs that have not been fully paid, and I
want the list to show how much is still owing on each job.

I've come up with a query that returns all of the jobs that have one or
more invoice, and that returns how much has been invoiced for these
jobs:

SELECT
	billing_invoice.*,
	billing_job.*,
	SUM(billing_invoice.amount)
FROM
	billing_invoice,billing_job
WHERE
	(billing_job.job_id = billing_invoice.job_id)
GROUP BY
	billing_job.job_id

There are a couple problems with the query (aside from the .*'s, I'll
fix those later):

1. It does not return jobs that have zero invoices. (I can hack around
this by running another query to find all the jobs without invoices.
Yuk.)

2. It will return jobs that have been fully invoiced. The SUM function
doesn't seem to work in the WHERE clause. (I can hack around this by
using PHP to ignore rows where the amount still owing is a positive
number. Yuk.)

3. All my worries would go away if each billing_job row kept its own
track of how much money had been paid and how much was owing. (Yuk. I'm
trying to do this properly.)

Will I have to resort to these hacks, assuming that I'm stuck with
mySQL? Or is there some other way to deal with this?

Thanks,

Beau





More information about the thelist mailing list