-2

Possible Duplicate:
Insert into 2 tables with PDO MySQL

Im trying to insert data into 2 tables, my problem is in 1 table i have an ID that is automatically incremented, I then want to insert into another table. At the minute as I don't know how to solve this im passing a hidden field input using rand(); and inserting that field into both queries, only sometimes the web pages may get cached and some records are being overwritten, I hope this makes sense? 

  $sql = "

  INSERT INTO `directory` (`First_Name`,`Surname`,`Nicknames` ) VALUES (:firstname, :surname, :nicknames);

  INSERT INTO `user_sightings` (`UID`, `postcode`) VALUES (:uid, :lastseenpostcode);

  ";
Community
  • 1
  • 1
Liam
  • 9,725
  • 39
  • 111
  • 209
  • Can you explain what fields are in the two tables and how those tables relate to each other. I am not seeing a common field in your queries. – Mike Brant Dec 27 '12 at 20:44
  • Begin transaction, insert into first table, get ID of latest insert, insert into second table, commit transaction. – DCoder Dec 27 '12 at 20:45

1 Answers1

3

Use LAST_INSERT_ID() in your second query.

  $sql = "

  INSERT INTO `directory` (`First_Name`,`Surname`,`Nicknames` ) VALUES (:firstname, :surname, :nicknames);

  INSERT INTO `user_sightings` (`UID`, `postcode`) VALUES (LAST_INSERT_ID(), :lastseenpostcode);

  ";
Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
Naftali
  • 144,921
  • 39
  • 244
  • 303
  • while this is nice. I can yield some SQL injection issues, since you are sending more than once query at the time. I would say user _stores procedures_ or do it using PHP methods, (and if you would like use transactions) – Gabriel Sosa Dec 27 '12 at 20:51