Hi,
I've been breaking my head over this problem and getting nowhere. To explain the problem, I'll have to give a bit of
background,so bear with me.
I have this system set up with postgres and php. One of the things this system does is allow the user to enter
invoices.Invoice info is entered into the "cart" table and the "cart_item" table. Invoices can be paid in numerous
ways,including by cheque. Sometimes, the cheque amount will exceed the invoice total. This necessitates creating a
credit,which is the difference between the cheque amount and the invoice amount. This amount is stored in a separate
tablecalled "difference". In addition to the amount, the "difference" table includes the customer's id (clientid) and
theinvoice id (cartid).
Let's say a customer places an order. The user enters the order and when he gets to the invoice page, finds a credit
fromthe previous invoice which the customer over paid. To determine if the customer has a credit, the system does the
following:
// is there a credit/debit associated with this customer
$query = "SELECT * FROM difference WHERE clientid = $CLIENTID";
$result = pg_query($db_conn, $query) or die("Error in query: $query."); // exec query
$numrows = pg_numrows($result); // get number of rows
if ($numrows == 1) // yes
{
// access credit amount and apply to present invoice
}
Now, what happens when the customer has placed several orders over a period of time, and overpaid for each one? To
accessthe pertinent credit amount, I did the following which I think solved the problem:
// is there a credit/debit associated with this customer
$query = "SELECT * FROM difference WHERE clientid = $CLIENTID ORDER BY cartid ASC LIMIT 1";
$result = pg_query($db_conn, $query) or die("Error in query: $query."); // exec query
$numrows = pg_numrows($result); // get number of rows
if ($numrows == 1) // yes
{
// access credit amount and apply to present invoice
}
By adding the "ORDER BY cartid ASC LIMIT 1" to the sql statement, the user gets the most recent credit amount for that
customerentered into the system. That is, the credit amount created by the previous invoice.
On the other hand, if the user wants to view an older invoice with its associated credit, he would do:
$query = "SELECT * FROM difference WHERE clientid = $CLIENTID AND cartid = $CARTID";
...etc...
This is all fine. My big problem is that I have two different sql statements. One to determine if there's a credit for
theinvoice currently being entered; another to determine if there's a credit for a previously created invoice; and no
wayto determine when to use one or the other.
You may ask, why can't I search the cartid in both instances. The reason is, when the user is creating an invoice, that
invoice'scartid (which is a random number) is already entered into the system, and therefore if he searches by clientid
andcartid, he'd get back the same invoice he's currently entering. If, on the other hand, the user is looking up a
previousinvoice, that invoice includes both clientid and cartid, allowing him to search directly for an associated
credit.
So my question is, is there a way to have one sql statement that serves both contexts?
Greatly looking forward to your feedback.
Mark
