phpinsertmysqlimany-to-manyjunction

Many-to-many insert query with PHP/mysqli


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.


Solution

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

    class DbService {
        var $username = "user";
        var $password = "password";
        var $server = "localhost";
        var $port = "3306";
        var $databasename = "database";
        var $connection;
    
    public function __construct() {
        $this->connection = mysqli_connect(
                                $this->server,  
                                $this->username,  
                                $this->password, 
                                $this->databasename,
                                $this->port
                            );
        $this->throwExceptionOnError($this->connection);
    }
    
    // returns array of (vidID, sort); adds duplicate vids to list if run 2+ times
    public function addToPlaylist($ytRef, $listID) {
    
        $stmt = mysqli_prepare($this->connection, "INSERT IGNORE INTO vids(yt_ref) VALUES (?)");
    
        mysqli_bind_param($stmt, 's', $ytRef);
        mysqli_stmt_execute($stmt);
    
        $vidID = mysqli_stmt_insert_id($stmt);    // 0 if video already exists
        mysqli_stmt_free_result($stmt); 
    
        if ($vidID == 0) {
            $stmt = mysqli_prepare($this->connection, "SELECT vid_id FROM vids WHERE yt_ref = ? LIMIT 1");
    
            mysqli_bind_param($stmt, 's', $ytRef);
            mysqli_stmt_execute($stmt);
    
            mysqli_stmt_bind_result($stmt, $vidID);
            mysqli_stmt_fetch($stmt);
            mysqli_stmt_free_result($stmt);     
        }
    
        $stmt = mysqli_prepare($this->connection, "INSERT IGNORE INTO fr_list_vid_junc(vid_id, list_id) VALUES(?, ?)");     
    
        mysqli_stmt_bind_param($stmt, 'ii', $vidID, $listID);
        mysqli_stmt_execute($stmt);
    
        mysqli_stmt_free_result($stmt);     
        mysqli_close($this->connection);
    
        $arr = array($vidID, mysqli_stmt_insert_id($stmt));
        return $arr;
        }
    }