mysql_insert_id, does not return the last inserted

Question

mysql_insert_id does not return the last inserted id when i place it inside a function.

im kinda confused why it does not.

here is my code:

function addAlbum($artist,$album,$year,$genre) {
    $connection = mysql_connect(HOST,USER,PASS);
    $sql = 'INSERT INTO `'.TABLE_ARTIST.'` (artistName) VALUES ("'.$artist.'")';
    $resultArtist = mysql_query($sql);
    $sql = 'INSERT INTO `'.TABLE_ALBUMS.'` (albumName) VALUES ("'.$album.'")';
    $resultAlbums = mysql_query($sql);
    $sql = 'INSERT INTO `'.TABLE_GENRE.'` (musicGenre) VALUES ("'.$genre.'")';
    $resultGenre = mysql_query($sql);
    $sql = 'INSERT INTO `'.TABLE_YEAR.'` (albumYear) VALUES ("'.$year.'")';
    $resultYear = mysql_query($sql);
    $lastId = mysql_insert_id();
    $sql = 'INSERT INTO `'.TABLE_LINK.'` (albumsId,artistId,genreId,yearId) VALUES ("'.$lastId.'","'.$lastId.'","'.$lastId.'","'.$lastId.'")';
    $resultLink = mysql_query($sql);
    if(!$resultArtist && $resultAlbums && $resultGenre && $resultYear && $resultLink){
        echo mysql_error();    
    }
}

thanks in advance

adam

Answer 1

You are calling mysql_insert_id() once after four separate INSERTs, and using that ID four times for albumsId, artistId, genreId and yearId. That doesn't seem right.

You should also check that your tables are using AUTO_INCREMENT fields. If not, mysql_insert_id() will not return the insert ID. See the docs:

http://www.php.net/manual/en/function.mysql-insert-id.php

I highly recommend that you use prepared statements with mysqli::prepare, perhaps via PDO. It's ultimately simpler and safer. Here's an untested example:

$dsn = 'mysql:dbname=test;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

$dbh = new PDO($dsn, $user, $password);

$stmt_artist = $dbh->prepare(
    'INSERT INTO `table_artist` (artistName) VALUES (?)'
);

$stmt_albums = $dbh->prepare(
    'INSERT INTO `table_albums` (albumName) VALUES (?)'
);

$stmt_genre = $dbh->prepare(
    'INSERT INTO `table_genre` (musicGenre) VALUES (?)'
);

$stmt_year = $dbh->prepare(
    'INSERT INTO `table_year` (albumYear) VALUES (?)'
);

$stmt_link = $dbh->prepare(
    'INSERT INTO `table_link` (albumsId, artistId, genreId, yearId) '.
    'VALUES (?, ?, ?, ?)'
);

$stmt_albums->execute(array( $artist ));
$artist_id = $dbh->lastInsertId();

$stmt_albums->execute(array( $album ));
$album_id = $dbh->lastInsertId();

$stmt_genre->execute(array( $genre ));
$genre_id = $dbh->lastInsertId();

$stmt_year->execute(array( $year ));
$year_id = $dbh->lastInsertId();

$stmt_link->execute(array( $artist_id, $album_id, $genre_id, $year_id ));

Answer 2

You need to call it separately for each insert, and store the result of each call separately. Like this:

$sql = 'INSERT INTO `'.TABLE_ARTIST.'` (artistName) VALUES ("'.$artist.'")';
$resultArtist = mysql_query($sql);
$lastArtistId = mysql_insert_id();
$sql = 'INSERT INTO `'.TABLE_ALBUMS.'` (albumName) VALUES ("'.$album.'")';
$resultAlbums = mysql_query($sql);
$lastAlbumId = mysql_insert_id();
$sql = 'INSERT INTO `'.TABLE_GENRE.'` (musicGenre) VALUES ("'.$genre.'")';
$resultGenre = mysql_query($sql);
$lastGenreId = mysql_insert_id();
$sql = 'INSERT INTO `'.TABLE_YEAR.'` (albumYear) VALUES ("'.$year.'")';
$resultYear = mysql_query($sql);
$lastYearId = mysql_insert_id();
$sql = 'INSERT INTO `'.TABLE_LINK.'` (albumsId,artistId,genreId,yearId) VALUES ("'.$lastAlbumId.'","'.$lastArtistId.'","'.$lastGenreId.'","'.$lastYearId.'")';

Also, it only works if each of tables you're inserting into has AUTO_INCREMENT enabled.

Answer 3

Did you ever try to debug your code?

With echo() (for showing your SQL queries) or var_dump() (for checking the results of e. g. mysql_insert_id(), mysql_query()).

Also check mysql_error().

Furthermore be sure to set the resource identifier in your mysql_*() functions. It's possible to have more than just one open MySQL resource - so be sure to identify the resource.

For example:

$result = mysql_query($SQL, $connection);
$lastInsertID = mysql_insert_id($connection);

And - it's very important to know that mysql_insert_id() just works with tables which have an AUTO_INCREMENT-field.

And what's also interesting with your code: you call mysql_insert_id solely after the last of 5 queries. Is this really wanted? So you only receive the ID of your last INSERT query.