是否可以在php for loop中使用last\u insert\u id()？

zf9nrax1 于 2021-06-24 发布在 Mysql

关注(0)|答案(2)|浏览(302)

是否可以使用 LAST_INSERT_ID() 在php for循环中。我需要最后一个 playground pk作为fk输入 guardian table。两者需要同时插入。请原谅我没用 PDO ，我只想先把这玩意儿弄好。

$query = "INSERT INTO playground (parent, children, amount) VALUES ('John','susy','2000');";

$levelarray = array ("One", "Two", "Three");
for ($i = 0; $i < count($levelarray); $i++) {
    $level = $levelarray[$i];
    $query .= "INSERT INTO guardian (playgroundid, level) VALUES (LAST_INSERT_ID(),'$level');";
}
mysqli_multi_query($con, $query);

我也试过这个。但是下面的一个输出最后一个id，而不是新插入的id。

$query = "INSERT INTO playground (parent, children, amount) VALUES ('John','Susy','2000');";

$sql = "SELECT playground_id AS playgroundid FROM playground ORDER BY playground_id DESC LIMIT 1";
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_array($result);
$playId = $row['playgroundid'];

$levelarray = array ("One", "Two", "Three");
for ($i = 0; $i < count($levelarray); $i++) {
    $level = $levelarray[$i];
    $query .= "INSERT INTO guardian (playgroundid, level) VALUES ('$playId','$level');";
}
mysqli_multi_query($con, $query);

mysql php mysqli-multi-query

来源：https://stackoverflow.com/questions/50020761/is-it-possible-to-use-last-insert-id-in-php-for-loop

2条答案

按热度按时间

ijnw1ujt1#

您需要使用mysqli\u insert\u id（）获取最后一个插入的id
然后将其用于下一个查询，如below:-

$query = "INSERT INTO playground (parent, children, amount) VALUES ('John','susy','2000');"; 

if(mysqli_query($con,$query)){ 
    $id = mysqli_insert_id($con);  //get last inserted id
    $levelarray = array ("One", "Two", "Three"); 
    for ($i = 0; $i < count($levelarray); $i++) { 
        $level = $levelarray[$i]; 
        $query = "INSERT INTO guardian (playgroundid, level) VALUES ($id,'$level');"; 
        mysqli_query($con,$query) ;
    } 
}

或者你还可以用 mysqli_multi_query() 就像below:-

$query = "INSERT INTO playground (parent, children, amount) VALUES ('John','susy','2000');"; 

if(mysqli_query($con,$query)){ 
    $id = mysqli_insert_id($con);  //get last inserted id
    $query1 = '';
    $levelarray = array ("One", "Two", "Three"); 
    for ($i = 0; $i < count($levelarray); $i++) { 
        $level = $levelarray[$i]; 
        $query1 .= "INSERT INTO guardian (playgroundid, level) VALUES ($id,'$level');"; 
    } 
     mysqli_multi_query($con, $query1);
}

note:- your 这是一个非常开放的问题 SQL INJECTION . 所以试着用 prepared statement 为了防止它。

赞(0）回复(0）举报 2021-06-24

n1bvdmb62#

不，这样不行。第一个监护人将获得操场的id，接下来的两个监护人将获得前一个监护人的id。
使用 mysqli_insert_id() 而是获取id并将其保存为for循环外部的变量。

赞(0）回复(0）举报 2021-06-24