mysql_insert_id，当我把它放入一个函数时，不返回最后插入的id [英] mysql_insert_id, does not return the last inserted id when i place it in a function

问题描述

im有点困惑为什么它没有。

这是我的代码：

 函数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）; （$！resultArtist&& $ resultAlbums&& $ resultGenre&& $ resultYear&& $ resultLink）{
 echo mysql_error（）; 
} 
} 
  

提前致谢

adam

解决方案

您正在调用 mysql_insert_id（）一次，在四个单独的INSERT之后，并且使用该ID四次用于 albumsId ， artistId ， genreId 和 yearId 。这似乎不正确。

您还应该检查您的表是否使用AUTO_INCREMENT字段。如果不是， mysql_insert_id（）将不会返回插入ID。查看文档：

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

我强烈建议您可以使用准备好的语句以 mysqli :: prepare ，也许通过 PDO 。它最终更简单，更安全。这里有一个未经测试的例子：

  $ dsn ='mysql：dbname = test; host = 127.0.0.1'; 
 $ user ='dbuser'; 
 $ password ='dbpass'; 
 
 $ dbh =新的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`（albumEear）VALUES（？）'
）; 
 $ b $ 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））; 
  

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

解决方案

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

这篇关于mysql_insert_id，当我把它放入一个函数时，不返回最后插入的id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！