Many-to-many insert query with PHP/mysqli

Question

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.

From the stack trace it looks like you are using a database service that makes use of some time related functions. If you want to get rid of the error set the date.timezone setting in your php.ini as the error message states. — Dan, Oct 30 '12 at 12:34
Thanks, Dan. Didn't know it referred to php.ini. Changed to: date.timezone = "Europe/London" and get a more relevant error: Warning: mysqli_prepare() [function.mysqli-prepare]: Couldn't fetch mysqli in C:\Sites\LocalDev\Site1\MyApp\services\DbService.php on line 251. Editing the post to reflect this. — Mr.Oz, Oct 30 '12 at 13:16
mysqli_close seems to expect a statement instead of a connection. Did you maybe mean to type $stmt instead of $this->connection? — Dan, Oct 30 '12 at 22:02
To be honest, I have no idea! I have rewritten the function many times and it currently fails silently. I have edited my post again because it is is now completely different (and still wrong). — Mr.Oz, Oct 31 '12 at 21:08

Mr.Oz · Accepted Answer · 2020-03-01T10:31:32.570

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

Many-to-many insert query with PHP/mysqli

1 Answers1