使用两个单独的查询插入两个表

Question

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);

Answer 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.