Need to update 2 tables in 1 go (collecting data from one to go in the second)

[rich_traff]

Hi, i need to update 2 MySQL tables in 1 go using PHP

 

Im posting from a comment form.

 

The tables that i have are;

TABLE: Comments [ commentId ] [ comment ] [ userId ] [ courseId ] [ partId ]

TABLE: Course [ userId ] [ part1_comment ] [ part2_comment ] [ part3_comment ]

 

In the comments table the 'commentId' auto increments.

 

What i want to happen is when someone posts a comment;

 

1) it gets added to the comments table as a new entry

2) the 'commentId' for that entry is recorded and an entry is made in the course table with that 'commentId' being put into the relevant 'part_comment'

 

Im assuming this kind of thing is a 'fairly' regular occurrence so am wondering if theres any best practice ways of approaching it?

 

Also, if theres any potential problems that could occur if many people are posting comments at the same time and how they can be avoided…

 

Any advice would be appreciated.

[rich_traff]

Currently using;

 

MySQL: 5.1.47

PHP: 5.2.17

 

thanks

[fenway]

You can't do a multi-table insert.

[rich_traff]

I assumed as much, what i am currently doing is the following;

 

1) INSERT query into comments table

2) SELECT latest entry from comments table

3) INSERT query into course table (using the data from the latest comments entry)

 

This is working, however im concerned that potentially 100's of people could all be writing comments at the same time and SELECT last entry might not pick up the right comment id...

 

So am wondering if theres a better way?

[fenway]

(2) should be replaced with last-insert-id() -- or whatever the php function from mysqli is called.

[Pikachu2000]

mysql_insert_id