Creating an Invoice Form in the cloud

[kuau]

I am trying to create an invoice form to allow sales to be entered to a database. The online invoice form would look a lot like the paper form with a section at the bottom for entering the payments.

I am having trouble figuring out how to add multiple items to the form, ie. the invoice number and date would have a one-to-many relationship to the items purchased and the payments. How do you insert multiple rows to an item table while inserting only one row to the invoice table? And insert possibly more than one row to a payments table at the same time, using the one form?

I was able to do this no problem in MS Access but it appears to be a lot more difficult in php and mySQL. Is this why they invented shopping carts? Am I thinking about this incorrectly?

Thanks.

[traq]

You've got one invoice with one (or more) line-items, correct? Use two tables:

**Invoice**
|---id---|---customer---|---shipto---|---payment---|---etc---|

**LineItems**
|---id---|---invoice_id---|---item---|---cost---|---qty---|---etc---|

you can use joins to select from both tables (as if it were a single table), though I'm not completely familiar with how the queries would be built.

You could also use comma-delineated lists or an XML file, but I think the two-table method would be best.

[ggalan]

the logic behind that relies more on SQL language as apposed to php no?

SELECT * FROM \TableOne INNER JOIN Row1 ON Row1.rowID = \TableOne.rowID WHERE Row1.rowID='$variable' AND Row2 NOT LIKE 'discontinued%' ORDER BY Row3

[kuau]

Thanks. There are actually 4 tables: client, invoice, invoice_detail. and payment. The question is how to write the form so that it pulls together the 4 tables so that it can load multiple rows into the invoice_detail table and payment table while adding only one row to the client table (or linking to an existing client) and the invoice table. Once I have the data loaded, displaying it will be less of a problem.

How do other people handle invoices? This must be a common problem. Can you use a while loop to load a varying number of items to the invoice detail table? Or do I create a tab that opens a page within a page that deals with only the one table. Or do I load the invoice table first to establish the invoice number and the date and client, and then use a separate form to load the items purchased against that invoice number? Do I add payments totally separately on a separate form after the purchases are entered?

Are there any examples of working invoice forms? All I could find was shopping carts which I thought were for clients. I need something for the sales people to use to record the sales in a more efficient way with more data (eg. tracking numbers) than the client would have at the time of the sale. Kind of an internal shopping cart. Is there no such thing? Would rather not reinvent the wheel here if avoidable.

Thanks for any ideas. e

[djr33]

The basic method here is that you share an id from one table ("order id" probably) then attach that to all other components.

Your "orders" table has: id, user id, date, price, status (for example).
Your "purchased items" table has: id, order id, ....

Then it's just a matter of doing a query for purchased items matching the order id, looping through those to gather all instances then generating the full "shopping cart".

You can't store all of this in a single table because the number of items purchased varies per query.

This structure is very similar to a forum's database: the most important table is "members", then various other tables contain rows for what those members do: for example posts which would have: id, user id, topic id, etc.
Then that topic id also is another way to cross reference, in that case to generate a topic.
It's easy to imagine after that creating a search to find "users who posted in this topic"-- find all posts, for which you find the users, then gather a list of unique users and output it. It's multiple steps and involves PHP in addition to several MySQL queries, but it's relatively easy when you consider the utility.

[kuau]

I really appreciate your efforts to help but I don't have a problem creating the tables and displaying the data. My problem is in LOADING THE DATA USING THE FORM. All three of you jumped to after the fact and ignored the issue I was asking about. HOW DO YOU LOAD MULTIPLE ROWS to one of the tables while loading only one row to the main table with the order ID that ties the tables together? You keep talking about after the data is in the tables, but I am asking how you get it in there in the first place.

The sales agents will not want to submit the form once for each item purchased, which would probably add multiple rows to the order table. The form should be submitted once after all items and payments have been recorded. The final result of submitting the order form should be something like this:

order table
orderID SaleDate AgentID ClientID Total Sale TotalTax
12345 2010-7-23 JK 5567 $1302.08 $52.08

order details table
OrderID ItemID Price Commission
12345 34 $ 400.00 10%
12345 55 $ 800.00 11%
12345 22 $ 50.00 7%

payment table
PayDate OrderID PaidBy TotalPaid
2010-7-23 12345 Cash $ 300.00
2010-7-23 12345 VISA $1,002.08

How do I get the form to add muliple rows to the order detail and payment tables but only one row to the order table by submitting the form only once? Imagine that the agents have a pile of orders at the end of the day that they want to enter into the database as quickly as possible so that at the end of the week they can print a sales report that will tell them how much commission they have earned.

Does this make sense now?

Thanks, e

[traq]

ah.

You just have to arrange all the data in your script before you submit it to the database. You could use $_SESSION for this, if there are multiple steps / scripts. You could also submit each query as it becomes ready, with another column called "final" (or something like) set to false, then change it to true when the invoice is complete.

There are Relational Database Management Systems that deal with issues like that (ACIDity), though I don't have much experience with them.

[djr33]

The simple answer is: step by step. First you will generate the order id then you will create any other necessary components. It's not difficult, just complex, because you need to create many processes to do this. You'll need to account for all different types of input then create loops (probably 'while') to go through all relevant rows to insert for the order.

The idea above of storing it first in $_SESSION is a good one if you plan to have a multi-page form.
In fact, that's basically what a shopping cart is: a session storing various items. In the session, you can store arrays of arrays (as many layers as you need) so you end up with: $_SESSION['cart']['item#']['somevalue'], etc. ("Cart" holds an array of all items and each item holds an array of info.) Then you can do whatever you'd like with this. Inserting into the database at this early point is a bad idea. Wait until it's something to store permanently (an order, not a cart) and if you already have it formatted as an array this should be easy (at least relatively so). Use a foreach loop (or several) and go through all of the data, inserting as needed.

[kuau]

I've never used Sessions before. Is there a good tutorial I can start with?

So a session would be started each time an agent began to enter an invoice and then it would be cleared after the form is submitted? I'm just not sure what it would look like in the code. I tried adding a link to display a new row if necessary and when it loaded, even though the extra row was not used, it loaded all blanks to the database.

Any time an agent would be entering an invoice it is safe to assume it is already an order and not just a cart. There are two things going on: clients can place an order online themselves, and an agent can enter actual sales from the gallery. I think I'd like to keep what clients do as a cart that needs to be confirmed by an agent before it gets entered to the order table. I need to sleep now. Thanks so much for the guidance.

[djr33]

Sessions are very easy (really). Here's a tutorial:
At the very beginning of all of your pages add:
<?php session_start(); ?>

Now use $_SESSION just like any other array. But magically, it will be shared throughout all pages for a single user (never between multiple users).

One page 1:
$_SESSION['var'] = 'value';

One page 2:
echo $_SESSION['var']; //"value"

The technical explanation of sessions is a lot more complex, and they can be confusing when you need to deal with security, but more or less that's all you need to know to use them.

A few more notes:
Sessions expire after not being used, closing the browser window, etc. If the user continues to do things on your site the session will stay active and after a reasonable amount of time it will reset itself for security. Nothing to worry about in most cases. It's something like 15 minutes of inactivity, but it can be more or less...
The session is always started so you can always use it. This also keeps it active so that if a user continues to browse but not add things to the cart their cart will not reset and they can still buy the cart's items (several hours later, assuming constant activity).
There is also only one session at a time and that's why it's an array. So just store $_SESSION['myvar'] as a sub-array and do whatever you'd like with it such as 'cart' or 'login', etc.
If you must reset $_SESSION, the easiest way to do this is to use: $_SESSION = array(); and the values will be reset. That's all. If you want to reset only a part, just do that using unset($_SESSION['part']);.


You don't necessarily need to use sessions, because you could also just store the active order id and process each page. For example, you could click "edit order" (that would store a hidden value in the field for the order id) then add a product. And do that as many times as you'd like, each time expanding the order by adding a new product with the relevant order id to the products table.


I was under the impression you needed this for customers in addition to agents. For agents the approach above seems logical since the order ID will already exist. But for customers using a session to store the shopping cart seems the logical way because you don't yet have an order id to attach anything to.