[Mysql]: Inserting values into an intermediate table

Question

I have recently added an intermediary table to link two tables.

Tables:

Purchase:
Transaction_Id  Purchase_Id

Schedule:
Show_Id     Price

Purchase_Schedule:
PurchaseId(fk)   Show_Id(fk)

My problem is that purhcase_id is on auto_increment. So i'm not sure where to pull that ID from.

I was going to do something like this...

INSERT INTO
    Purchase_Schedule
        (Purchase_ID, Show_ID)
    VALUES
        ((SELECT Purchase_ID FROM Purchase WHERE Transaction_ID=$transactionID),$purchaseID)";

The issue is that a Transaction_ID can have more than one purchase_ID tied to it... so this method is out.

So I believe the best way to approach this situation would be to use some sort of stored procedure to automatically update Purchase_Schedule.

Should I go with a stored procedure?
Is there a way to insert into Purchase_Schedule @ the current insert's Purchase_ID?
or a way to return the auto_incremented Purchase_ID?

Answer 1

If you're using PHP to insert the data, you can use mysql_insert_id to retrieve the value of the auto-incremented id for the last row inserted.

You can also get it via SQL:

SELECT LAST_INSERT_ID()

See the MySQL manual for more information.