CODEWITHSUNDEEP
phpmysql
otinanai
otinanai

Reputation: 4025

Make 2 tables identical on every form submission

I have 2 tables (with identical structure) and I want both to update after a form submission and both have the exact same data.

The tables structure are as below:

id | parent_id | name | surname

The id is the primary key, which means that I cannot execute the following query because it throws an error (Duplicate entry '1' for key 'PRIMARY'):

INSERT INTO table_2 SELECT * FROM table_1

My goal is to keep the data identical (including the PRIMARY) in both tables after a row is updated or a new row is added. How can I do this and avoid the Duplicate entry error?

Upvotes: 0

Views: 64

Answers (1)

Damon
Damon

Reputation: 4514

Because your forms are on two different pages, you need some way to connect the two when creating the second record.

Using language as an example, I would have my "primary" language form on one page. Submit that information to your "default/primary" language table.

Then, on your "secondary" table, I would include a select input that listed out my "primary" languages. The values would be the primary key of your "primary" language. The user has to choose a parent language to connect to. Otherwise the secondary language has no idea who it belongs to.

On your secondary table, I would not have an auto_incrementing primary key. That way you can share the same ID as your parent table.

I would create a foreign key to the "secondary" table that was related to the "primary" table id field.

This will allow you to run queries like:

Not tested at all - and probably incorrect syntax.

SELECT * FROM `primary` WHERE something JOIN `secondary` ON `secondary.id` WHERE `secondary.id` = `primary_id`

But, that would give you an idea of what I'm talking about.

Edit

Based on our conversation, it sounds like you will either need to drop your secondary table on each insert (to clear the primary keys). Or I found this thread on resetting the key that might be helpful.

Upvotes: 1

Related Questions