2

Just looking for some tips and pointers for a small project I am doing. I have some ideas but I am not sure if they are the best practice. I am using mysql and php.

I have a table called nomsing in the database.

It has a primary key called row id which is an integer.

Then I have about 8 other tables referencing this table. That are called nomplu, accsing,accplu, datsing, datplu for instance. Each has a column that references the primary key of nomsing.

Withing my php code I have all the information to insert into the tables except one thing , the row id primary key of the nomsing table. So that php generates a series of inserts like the following.

INSERT INTO nomsing(word,postress,gender) VALUES (''велосипед","8","mask").
INSERT INTO nomplu(word,postress,NOMSING?REFERENCE) VALUES (''велосипеды","2",@the reference to the id of the first insert@).

There are more inserts but this one gets the point across. The second insert should reference the auto generated id for the first insert. I was this to work as a transaction so all inserts should complete or none.

One idea I have is to not auto generate the id and generate it myself in php. That way would know the id given before the transaction but then I would have to check if the id was already in the db.

Another idea I have is to do the first insert and then query for the row id of that insert in php and then make the second insert. I mean both should work but they don't seem like an optimal solution. I am not too familiar with the database transactional features but what would be the best approach to do in this case. I don't like the idea of inserting then querying for the id and then running the rest of the queries. Just seems very inefficient or perhaps I am wrong.

KennyBartMan
  • 940
  • 9
  • 21
  • check this: http://stackoverflow.com/questions/621369/sql-insert-and-catch-the-id-auto-increment-value – mostruash Sep 03 '12 at 10:00
  • in transactions `new.PK_field_name_in_inserted_table` will return last inserted id for current session. So you can use it in manually created transactions. I don't know if it's what you want but hope it helps. By the way, there's syntax error in your queries. – Leri Sep 03 '12 at 10:01
  • That looks very good. Thank you. Do you know if this mysql_insert_id function is thread safe. I mean if one user calls the insert and then another at a split second later. Then when the first user calls the get ID it gets the id of the insert from the second user. – KennyBartMan Sep 03 '12 at 10:07
  • Yes the queries are just dummy queries to explain the idea. – KennyBartMan Sep 03 '12 at 10:08

3 Answers3

1

Just insert a row in the master table. Then you can fetch the insert id ( lastInserId when on PDO) and use that to populate your other queries.

JvdBerg
  • 21,777
  • 8
  • 38
  • 55
0

You could use the php version as given by JvdBerg , or Mysql's LAST_INSERT_ID. I usually use the former option.

See a similar SO question here.

Community
  • 1
  • 1
Awemo
  • 875
  • 1
  • 12
  • 25
0

You could add a new column to the nomsing table, called 'insert_order' (or similar) with a default value of 0, then instead of generating one SQL statement per insert create a bulk insert statement e.g.

INSERT INTO nomsing(word,postress,gender, insert_order) 
VALUES (''велосипед","8","mask",1), (''abcd'',"9","hat",2).....

you generate the insert_order number with a counter in your loop starting at one. Then you can perform one SELECT on the table to get the ids e.g.

SELECT row_id
FROM   nomsing
WHERE  insert_order > 0;

now you have all the IDs you can now do a bulk insert for your following queries. At the end of your script just do an update to reset the insert_order column back to 0

UPDATE nomsing SET insert_order = 0 WHERE insert_order > 0;

It may seem messy to add an extra column to do this but it will add a significant speed increase over performing one query at a time.

Springie
  • 718
  • 5
  • 8