CODEWITHSUNDEEP
mysqldatabase
Mike
Mike

Reputation: 1768

How INSERT data in multiple tables

I have looked at the other questions on SO and I cannot seem to wrap my head around how this works. I'm building an online store for firearms that will catalog firearms and related items. There are two problems I'm having -

PROBLEM 1

In this problem I have an add product form where the product is broken up into two parts; the first is PRIMARY ATTRIBUTES and the second is SECONDARY ATTRIBUTES. Here is a SS of the test form:

enter image description here

An example would be some firearms have eight different variations of that model. It can differ in barrel length, stock style, bullet caliber size, and other features. So I will display the PRIMARY ATTRIBUTES on top of the page and in a chart I will list all the sub-models that list the SECONDARY ATTRIBUTES.

The PRIMARY ATTRIBUTES are attributes that every product in that category will definitely have. The SECONDARY ATTRIBUTES are attributes that some products will have. So when the form is filled out and submitted I want the data to insert into all the tables in one shot. Here are my tables:

enter image description here

PROBLEM 2

The way my SECONDARY ATTRIBUTES table is created I can add one attribute at a time. In the screenshot above there are eight SECONDARY ATTRIBUTES, would I create a loop that will loop eight times inserting data each time? Hopefully that makes sense, if not I will go into more detail.

Note, I'm using PHP/MySQL.

EDIT

I took everyone's advice and tried to design my database using the suggestions. Please take a look and see if what I did is something that would work efficiently. I'm learning the simple concept of one->one, one->many, many->many, and etc ... The only thing that I saw that could be one->one is the product to manufacturer. Does that seem correct? I've spent a lot of time trying to make this the best it could be and I appreciate everyone's feedback/critique. Below is my new schema:

enter image description here

Thanks,

Upvotes: 1

Views: 2121

Answers (2)

bobwienholt
bobwienholt

Reputation: 17610

MySQL supports multiple row inserts:

INSERT INTO table
( col1, col2 )
VALUES
( 1, 'blah'),
( 2, 'foo'),
( 3, 'foobar')

You can also use MySQLi or PDO, use parameter binding, prepare your insert and then execute multiple times (http://us3.php.net/manual/en/mysqli.prepare.php)

Upvotes: 2

Scott Hunter
Scott Hunter

Reputation: 49803

  1. You can't insert into multiple tables in "one shot", but you can do the next best thing: treat the insertions as part of a single transaction, so that if something goes wrong, whatever part of the work has been done will be backed-out.

  2. It IS possible for a single statement to insert multiple records: http://www.electrictoolbox.com/mysql-insert-multiple-records/

Upvotes: 3

Related Questions