Send html form data to two separate tables in the database using sql and php

Question

I have these 2 tables:

ornaments (ornament_id, ornament_name)
ornament_to_size (ornament_id, size_id, ornament_type, colour, material, finish, height, width, weight, location, price, description, image_path)

And I want to create a form in admin to insert a new ornament item into the database. What would be the best way of doing this? I need to insert in both tables which is making it more difficult for me. The ornament_id in the ornaments table is obviously the PK and the PK in the ornament_to_size table is ornament_id and size_id as a composite key.

The reason there is an ornament to size table is because an ornament can have more than one size like so:

ornament_id = 3 & size_id = 1
ornament_id = 3 & size_id = 2 
ornament_id = 3 & size_id = 3

I just want to know what the method would be not the actual code

There are ways to dynamically add inputs to a form so that you can enter in multiple ornament_to_size rows with a single submission of the form. (Or if you knew that you will only ever want to submit 3 ornament_to_size rows then you can just put 3 static sets of inputs for the ornament_to_size rows.) Now I have to ask: Is size_id truly an id? Meaning it is the id to another table (perhaps named size)? — ghbarratt
– ghbarratt, Commented Mar 8, 2012 at 8:08
Oh sorry, I 4got to say that I have a sizes table with the names of each size corresponding to the size id. Also, each ornament can have different sizes that have different dimension measurements. So one ornament can be classed as 'small' and so can another ornament - but they may both have different dimensions — user1227124
– user1227124, Commented Mar 8, 2012 at 9:10
Okay, but the approach will basically be the same as what I described before: You will collect the information for the new ornament as well as the different associated sizes in the same form (possibly with inputs that can be dynamically added for more sizes). On the server side you will gather the information for the ornament and insert that first so that you can get the ornament_id for the ornament just inserted (perhaps with lastInsertId) which you will need to insert the ornament size rows. Does this answer the question? — ghbarratt
– ghbarratt, Commented Mar 8, 2012 at 9:29

kawashita86 · Accepted Answer · 2012-03-08 09:46:53Z

1

if the size table has already got the value in it you have two choice to let the user insert the data:

select if you want to let the user add a single new value
checkboxes field if you want the user to insert more then one,
remember to name it as 'checkboxname[]' so that php can use it as an array.

In both cases use the id of the size as value. On the Db inserting part you will have to parse the form data, then perform:

a Insert into the ornament table, straight after perform a LastInsertId() query (this is pdo function but you can make it in mysql too) on it and save the result in a var.
perform a subsequent query into the ornament_to_size table, using the var you have saved as ornament_id value.

Remember that if you have chose to use checkboxes you can just run a foreach with the $_POST['checkboxname[]'] to insert all the new rows thanks to how PhP read the POST .

edited Mar 8, 2012 at 9:46

answered Mar 8, 2012 at 9:41

kawashita86

1,5833 gold badges18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1227124 Over a year ago

Thanks for your help mate. I kinda get it. I will have to look into it.

Collectives™ on Stack Overflow

Send html form data to two separate tables in the database using sql and php

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related