0

I am trying to insert into two tables using two sql insert queries. The first query should insert into one table as shown, and second second statement needs to insert into the second table. HOWEVER, I need the second statement to somehow know what the story ID (SID) is that gets assigned to the record in the first query. SID is the primary key and auto_increments in the table "stories" and it is a field in the table :writing"

Then I want to somehow (with a JOIN I assume) populate the SID in the writing table with the SID assigned to the first query. But how will the code know which is record that was just inserted in the first query to get that SID?

// Get values from form 
$category = $_POST['category'];
$genre = $_POST['genre'];
$story_name = $_POST['story_name'];
$text = $_POST['text'];


$query = "INSERT INTO stories (ID, category, genre, story_name, active)  VALUES
('$user_ID', '$category', '$genre','$story_name', '1')";

$result = mysql_query($query);
$SID = mysql_insert_id();     
$SID2 = "select stories.SID from stories where stories.SID=$SID";

$query2 = "INSERT INTO writing (ID, SID, text, position, approved) 
VALUES('$user_ID',  '$SID2', '$text', '1','N')";


$result = mysql_query($query2);
user5000
  • 129
  • 1
  • 9
  • Have a look at http://php.net/manual/en/function.mysql-insert-id.php - there are equivalents for both mysqli and PDO – andrewsi Aug 03 '14 at 15:26

1 Answers1

1

Just use mysql_insert_id() between database calls.

$query = "INSERT INTO stories (ID, category, genre, story_name, active)  VALUES
('$user_ID', '$category', '$genre','$story_name', '1')";
$result = mysql_query($query2);
$sid = mysql_insert_id(); // <-- GET ID

$query2 = "INSERT INTO writing (ID, SID, text, position, approved) 
VALUES('$user_ID', '$sid', '$text', '1','N')"; // <-- you can now use $sid here
$result = mysql_query($query);

FYI, you shouldn't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Community
  • 1
  • 1
John Conde
  • 217,595
  • 99
  • 455
  • 496
  • Yes, I plan to go back and covert all to PDO once I can get things to work as needed. As for your answer, I love the idea. Didn't work yet, but I guess there is more needed than just the $sid = mysql_insert_id(); Do I needed to add more code that explicitly gets the SID after that line you added? – user5000 Aug 03 '14 at 15:32
  • That should be all you need? Have you confirmed the first query inserts successfully? Does it have an auto-increment for the primary key? – John Conde Aug 03 '14 at 15:34
  • Yes first query inserts perfectly. SID is the primary key with auto-increment. The second table "writing" has SID (which is a regular field there), but it just keeps assignment value of "0" for each attempt. I feel like this is close though... – user5000 Aug 03 '14 at 15:52
  • The problem I think is that mysql_insert_id(); get the ID, but I need it to grab the SID for that ID from the stories table and place that in the $sid variable. Then I will be golden. Can I get to that column with the mysql_insert_id(); approach? – user5000 Aug 03 '14 at 18:33