I'm trying to insert into a table that has id int auto_increment as the primary key, and to use that insert for a follow-up query.
INSERT INTO SOMETHING (COL_NAME) VALUES (COL_VALUE);
// Somehow I would like to use the auto_incremented id generated from this insert, for the next insert
INSERT INTO SOMETHING_ELSE (SOMETHING_ID, SOME_FIELD)
VALUES (SOMETHING_ID_FROM_PREVIOUS_QUERY, some_field_value);
I was trying to do something like
START TRANSACTION;
INSERT INTO ...; //etc
INSERT INTO OTHER_TABLE (relation_id, new_content) values (LAST_INSERT_ID(), new_content_value);
COMMIT;
Also attempting to use SELECT LAST_INSERT_ID(); in the planetscale console on their website always returns 0.
Perhaps there's a better way I should be going about this, but I was curious if there was a way to batch transactions like that.
Yes, PlanetScale does support LAST_INSERT_ID.
It now works in the web console as well if you test it there. The reason it was not working at the time was because the web console used to not maintain a session between requests.
It now does.
Here's an example.
insert into test_table (name) values("example");
select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 7 |
+------------------+
Your transaction example looks correct to me. And it should work in the web console now if you give it a try. Just be aware there is a 20 second limit for transactions. It may be easier for you to submit it from your code than by hand through the console.