table1 -------- id first_name last_name created etc.....
table2 --------- id table1_id created etc....
I need to insert a row into table1, using a null value for the id so it generate an id automatically. Then I need to insert a row into table2, including the id from table 1 (table1.id needs to be inserted into table2.table1_id).
Any best practices to doing this? I've considered adding additional fields to both tables to represent a unique id or hash that will be generated by PHP. That way I can tie the two tables together and pull the newly-generated table1.id value out based on the unique hash and insert it into table2. But I'd rather not do that if I don't have to.
I don't know if the tables are locked with transactions. If they were, I could insert into table1, then find the last id generated for table1 and then put that into table2. Do transactions behave this way?
I can't just use the PHP session id because someone with the same session may register another person, and then I would have the same session ID in two rows. I can't assume the names or other info are unique either.
Any ideas or is the PHP unique id/hash going to be my best bet? It just seems wasteful to have to add another field just to tie the data together until I can tie it together with the generated ids.
I've looked through the PHP Cookbook and the MySQL cookbook and haven't seen a solution. I've thought about trying to make some unique hash with the data to be inserted but if there's another identical set of data, then the hash would match. I can't use a timestamp in the hash
Now that I'm thinking about it, could I do this:
1. Generate a timestamp value
2. Insert into table1 all the info I need.
3. Create a unique hash from the timestamp and inserted info
4. Find the row and id from table1 where the hash of the timestamp value from step 1 and the data in the row match the hash from step 3
5. Put the id value into table 2
Seems like a lot of work to do to find the answer. Any gurus got an idea?
Scott Plumlee
PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
