Invoicing and Order Management

[richardmorris]

I am creating a online control panel for clients to process orders and view invoices.

Prior to the invoice being raised, it must go through our orders database. I am using PHP on a linux server.

When an order is placed and entered into the order database, the client then needs to view the order and select ACCEPT TERMS. When they do this, the order is updated and then the values are entered into an invoice table in a seperate database.

Once the user click ACCEPT TERMS they are then transferred to a page that runs a script to set the variables and insert the fields into the invoicing database. What I didn't consider is this:

The orders and the invoicing databases have two tables. The invoicing database has INVOICE and INVOICE_DETAILS. The invoice table contains the header information, such as the client details and the INVOICE_DETAILS holds all of the transactions such as QTY, DESC, UNIT PRICE. When this displays on a page, I use a while loop to show all the entries for that invoice in the INVOICE_DETAILS table.

To transfer all of the order entries into the invoice_details table, can I simply use an insert query on the ACCEPT TERMS page and then it keeps transferring the details until all have been transferred? for example

$query1 = "SELECT * FROM order_details " .
"WHERE customer_reference = '" . $_SESSION['session_name] . "' AND order_reference = $order_reference AND invoiced = 'No'";
$result1 = mysql_query($query1)
or die(mysql_error());

then use a while statement for insert

[richardmorris]

I have been working on this sytem all day and reviewing that post, I feel like I explained it terribly.

[svilla]

You could use a subselect on the insert.

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2" where column3 = $orderNumber

You would then only need two inserts. One to populate the invoice header from the order header and one to populate the invoice detail from the order details.

Table1 would be your invoice table and table2 would be your order table.