StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL/PHP: Selecting jobs with outstanding balances
text
Body
copied!<p>Hopefully I can do this without writing a book... I'm working on a system for a Surveying company. They're also throwing in an accounting section, and one of the functionalities it needs is to find jobs that are not completely paid off.</p> <p>A few points to be made:</p> <ul> <li>A 'job' is a project, essentially. There are different types of surveys.</li> <li>A job can have more than one "type" of survey in it, thus having more than one price that is calculated into the job's total price.</li> <li>Payments are made on individual jobs (the client might send 30 checks if they have 30 jobs, it's an accounting thing)</li> <li>Job types are dynamic (they can create/delete them from the system panel) - so I can't hardcode these things</li> </ul> <p>Here's the applicable database structure:</p> <pre><code>table jobs: job_id, client_id table job_types: type_id, type_name table job_type_assoc: id_job, type_id, price table payments: payment_id, job_id, amount </code></pre> <p>Note: payments aren't made on each job type, but the JOB as a whole (again, as opposed to the client's account having a "balance").</p> <p>I need to somehow pull jobs where the total of <code>price</code> from <code>job_type_assoc</code> is less than the total of <code>amount</code> in <code>payments</code>.</p> <p>I don't know if this is possible in mysql alone or if php would be more efficient - also, their old system has about 340,000 jobs in it. Granted that they won't have the accounting info from then, they do have a lot of work and the new entries will build quickly, AND if I somehow do it in PHP where I end up querying the jobs table a lot, it might get messy.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload