I have a database with a table of YouTube playlists, a table of video references and a junction table connecting them in a many-to-many relationship. I have been trying to create a PHP/mysqli function that adds a YouTube video reference into the 'vids' table and inserts a playlist ID and video ID into the junction table. If the video is already in the vids table I want it to get the record ID, otherwise insert it and use something like mysqli_stmt_insert_id to return the new ID.
Edit: I have removed the code I posted because it was not even close to being correct.
Finally found a way to do this and it has to be done with multiple queries.
The tables are set up so that when you enter a new YouTube video reference into vids table it gets an autoincrement vid_id. The junction table, list_vid_junc, is MyISAM and has 3 columns: list_id, vid_id and sort. The primary key is made up of list_id and sort. sort is set to autoincrement. This allows multiple entries of the same video in a list, which can be sorted by the sort autoincrement.
Note that in a MyISAM table because the primary key index is split this way, mySql starts the sort autoincrement from 1 for each new list that is created rather than making every sort a unique number, see http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html#id583323. List 1 can contain sort ids of 1,2,3 and list 2 can also have sort ids of 1,2,3. The number pairs are unique not the individual numbers.