Two inserts -> key from 1st to 2nd

[bleedi]

Hey,

I have a problem in my database. I have these two tables:

Table "File":
+----------+--------------+-----+----------------+
| Field    | Type         | Key | Extra          |
+----------+--------------+-----+----------------+
| idFile   | int(11)      | PRI | auto_increment |
| path     | varchar(255) |     |                |
+----------+--------------+-----+----------------+

Table "Media":
+----------+--------------+-----+----------------+
| Field    | Type         | Key | Extra          |
+----------+--------------+-----+----------------+
| idMedia  | int(11)      | PRI | auto_increment |
| name     | varchar(60)  |     |                |
| ...      | ...          |     |                |
| file     | int(11)      | MUL |                |
+----------+--------------+-----+----------------+

I use a java program to ask information for a media entry. User types in the information for media and browses for the right file. After that the program makes all necessary checks and send the insert commands to the MySQL server. Now, the problem is, I need to insert the "file" row first, before the "media" row, but I can't find a way to retrieve the id of the same file I just added. I used subquery like "SELECT idFile FROM file WHERE path = '"the_path_i_just_inserted"' ", but I got problems with two pieces of media pointing to the same file. This causes the subquery to produce more than one rows, which then fails the insert command.

One method could include retrieving the the latest row from "File" table, but I don't like that since there is the tiny possibility of update of the table between my two queries. I want the exact id of the same row I just added. :)

[smantscheff]

Have a look at the function last_insert_id(). It retrieves the last automatically generated id for this session so there has to be no worries about other updates sneaking in.
And bracket your two inserts with "begin transaction" and "commit".
Apart from that, it seems to me like bad design having the same content (file names) duplicated in one table with different ids.

[bleedi]

Have a look at the function last_insert_id(). It retrieves the last automatically generated id for this session so there has to be no worries about other updates sneaking in.
And bracket your two inserts with "begin transaction" and "commit".
Apart from that, it seems to me like bad design having the same content (file names) duplicated in one table with different ids.

Ah, doing these things a bit tired seems to be bad, never even thought of checking if the file already is in the database. I must check all the dependencies etc. if it's okay to just make the check, since there may be other information saved in the "File" table as well.

Thanks nonetheless :)