Enter multiple data into mysql?? Help a feller out please!

idotcom

Hi guys (and girls)

I need your professional help with this unusual task.:rolleyes:

Now, I'm not a php retard, I'm ok ( really 😉 ), but this one I have not done before.

I am building this php/mysql application for a company to enter and store upto 140 items that will be used to generate proposals.

So far.. I have 2 tables: items and proposals.
The items table has: item_id, desc, cost, and price.
The proposals table has: prop_id, ( cutomer info fields ) and cust_items field.

Now, the problem I am having, is...

When they start a new proposal, they enter customer name address.. etc and select all the items needed out of the 140 items, for that customer. Now if there are 140 mysql rows in a table containing the items, how would I insert all the items selected into the proposals ( cust_items ) field?

I was thinking to insert all the selected items ID's into the proposals ( cust_items ) field using commas to seperate the items.

The thing is.. I would need to select all the proposal fields on the next page to print the generated proposal with all the items, prices, and descriptions and total price.

Please, someone guide me!!
I would GREATLY appreciate any info, ideas, or code samples!!

Thank you in advance.

Chris

If anyone can crack this task, you are a GOD! 😃

also let me know if I need to explain more.

Weedpacket

Have a third table. It needs two columns; prop_id and item_id. The names should make it obvious what they contain. If proposal 31 uses item 25, then there'll be a row in the table (which I'll call for the sake of argument "proposal_items"):

prop_id | item_id
--------+--------
31      | 25

And so on for each item in each proposal.

To select all the item descriptions (for example) for a given proposal id

SELECT i.desc
FROM items i, proposal_items pi
WHERE pi.item_id=i.item_id AND pi.prop_id=$prop_id

where of course $prop_id is the variable containing the proposal ID in question.

This is known in database engineering as "normalisation". Most sites that teach database design should have something on the subject. There are also one or two articles on the subject on this site.

idotcom

Hi thanks for your response.. It was a good one!
I actually went and read an article on the normalization. Pretty coll, but didnt cover it all, especially entering multipe id's at once.

but lets say I create a customer, go to the next page and select 99 of the 140 items and click next.. how do I go about inserting the 99 item id's into the proposals table along with customer id?

If you have an answer.. that would be cool. If not, thats ok..

Thanks again

Chris😃