Insert Mysql with PHP - problem with foreign key insert

Question

[project_select]
UserID (fk) | project_id (fk) | project_category_id (fk)

[project_category]
project_category_id | category

[projects]
project_id | projectname

[project_user]
UserID | Name

How can I insert Data with php in the tables project_category, projects and project_user, to get automatically the values in the project_select table with the FK's?

Update: How can I merge this 3 queries into one line?

INSERT INTO project_category
VALUES (1,'Fruits')

INSERT INTO projects
VALUES (4,'Apple')

INSERT INTO project_user
VALUES (2,'Adam')

and get this values with this one query in the project_select table:

[project_select]
UserID (fk) | project_id (fk) | project_category_id (fk)
    2              4                  1

@elmanio what exactly do you mean by "How can I insert Data with php in the tables project_category, projects and project_user, to get automatically the values in the project_select table with the FK's?". I guess many others did not understand completely. — Sandeepan Nath
– Sandeepan Nath, Commented Oct 4, 2010 at 20:43

Steve Claridge · Accepted Answer · 2010-10-04 21:10:44Z

1

You can use the mysql_insert_id function to retrieve the id you just inserted.

mysql_query("INSERT INTO Persons VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')");
$person_id = mysql_insert_id();

So, do inserts on your tables, call mysql_insert_id() after each insert, store inserted IDs and finally use the IDs in the mysql command to create the join table.

answered Oct 4, 2010 at 21:10

Steve Claridge

11.2k8 gold badges35 silver badges36 bronze badges

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

5 Comments

elmanino Over a year ago

no, I want the same with the [project_select] table, how can I insert value in the tables, to get the value automatically in the FK field?

Steve Claridge Over a year ago

Not sure I follow you. Using mysql_insert_id to retrieve the newly created primary keys on your three tables will give you the keys to insert into project_select.

Steve Claridge Over a year ago

It should work for what you want but there's no way to insert into three table, store the PKs and then insert them into another table all in one query.

elmanino Over a year ago

thank you, what's the best method to insert my Data in all 4 tables? with mysql_insert_id? can you show me an example pleas?

elmanino Over a year ago

I found a very good example on this page desilva.biz/mysql/insertid.html thank you again!

Community · Accepted Answer · 2017-05-23 11:55:22Z

0

I doubt that I understood your problem correctly. But if you want to have the values entered automatically in project_select table? Then how about using MySql triggers? But be sure you are well aware of the database internals before using it.

What are triggers?, Setting up triggers

But, please describe this -

problem with foreign key insert?

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Oct 4, 2010 at 21:03

Sandeepan Nath

10.3k18 gold badges92 silver badges162 bronze badges

Collectives™ on Stack Overflow

Insert Mysql with PHP - problem with foreign key insert

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related