Best way to save data question

OK, I have a question now. I need some theory on the best way to do this.

I am creating an invoicing application.

I have a problem with the amount of products to be added. I could easily create 10 fields and have 10 items in the database for products ordered but that limits me to 10.

So at first I did it this way. I had a scrolling text box. Each time an item was added; it refreshed the page and added it to the text box. Then I did a preg_match_all() for the dollar amounts and added them for the total. Then I only had to save the value of the text box. That was easy, but this greatly limits editing the invoice.

What would be the best way to add unlimited line items with, Item Number, Description, Qty, Total, and Taxable? And save it to the database so it is editable?

Code, I can write I am looking for the best way to tackle this.

Thx,
~Cow

As if I understood it I will be doing something else like this. I like using arrays and strings a lot. Since PHP has a lof of functions related to strings and arrays it makes life easier.

I will be storing all the product information as a single string on a single field.
Something else like this.. Say for two products the string would look look this.

itno1,desc1,qty1,total1,taxable1|itno2,desc2,qty2, total2,taxable2

There are two delimeters here. '|' for seperating the products and ',' for seperating fields of the product. To add more products you merge more values seperated with the respective delimeter.

Simply you can use your scrolling text box containing this string. Or say for easy editing if you want to use the form with text fields representing each an every field of the product you can fill the data by using the friendly explode() function for breaking the string into records and then to fields. You can name the text box names as array elements which then enables you to use implode() easily to create the string of data.

I hope I'm clear. The drawback of using this method is that when quering the database with SQL statements. Like I want the sum of all products for that particular order.

For these kinda thing I use seperate fields and store the total when I'm adding the product itself using the code.

The SET field type I think is used to do this kinda thing. But I'm not used to it that much. If there are other ways please post. Probably I can use that method as well.

I have edited my previous post.. I made a small mistake by using the functions at the wrong place.. Read it again if you have previously read it before I edited it. Sorry for the mess.

That is not a bad idea. Like you said explode it into an array then I could even loop and put each into a seperate field of its own. Then passing the total line items to the refresh page after editing...

Hmm, So far thats better than anything I had.

Yep.. It makes things easier when you have them in an array.
I use TEXT as field type for storing this string in the database. Coz this is the best way I could come up with as you can add as much products as you want for a particular order(record).
If you or anyone else come across a method of doing this please post it in. It might be a great help for me too.

the right way: two tables



for every product in the invoice, you INSERT another row into the invoice_products table, set the invoice_id, product_id, quantity, etc..

when you need to list them, you just SELECT * FROM invoice_products WHERE invoice_id='$id'

etc. also, you can add/remove/view/edit product in invoices, and by joining this with the products table (that has prices), you can also get the total amount..




and if you still want to do it the wrong way (store it all in one field in the invoices table), than just serialize() the array with products, and unserialize() it when you get it out of the DB.

Zombie,

NOW THATS WHAT I WAS LOOKING FOR!!!

That my friend is the best way.

Thanks

Great.. Zombie.. You just cleared few doubtful minds in here.. I will stick to the right way..

I agree with zombie.
I know there are many ways to do many things (specially in PHP!)
but the idea mentioned by zombie is the way.

Can I add a small caveat - which has bitten me on occasions.
Personally I would keep the price of an item in the item.detail file and the price charged in the invoice.detail file. Normally this would be the same price as the price on the item.prices file BUT there can be occasions when you get this scenario...

Invoice.Header:
Number=1234 Cust=SMITH1 Date=etc.etc

Invoice.Details:
Item#=AA11 Description=House Bricks Price=10.00 Qty=100
Item#=AA11 Desription=House Bricks FREE Price=0.00 Qty=10

IE Buy 100 get 10 free.

The operator may want to bill some more of an item for free or at a discounted price.

Also this is another reason not to use the item.number as a non-duplicate key.

Hope I have explained myself here - do you get what I mean ~Cow?

HIH

þ

Yes I did and that makes sense. Thanks Price, I love this forum. The way I was going to do it was painfull at best and added an extra 500 lines of code.


~cow