Inserting into two tables using two separate queries

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

Have a look at php.net/manual/en/function.mysql-insert-id.php - there are equivalents for both mysqli and PDO — andrewsi
– andrewsi, Commented Aug 3, 2014 at 15:26

Community · Accepted Answer · 2017-05-23 10:33:31Z

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.

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Aug 3, 2014 at 15:26

John Conde

220k100 gold badges464 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user5000 Over a year ago

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?

John Conde Over a year ago

That should be all you need? Have you confirmed the first query inserts successfully? Does it have an auto-increment for the primary key?

user5000 Over a year ago

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 Over a year ago

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?

Collectives™ on Stack Overflow

Inserting into two tables using two separate queries

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related