mysql重用自动增量id

Question

I have the following situation,I simplified the table to keep things readable here.

Table structure: (note: id is auto-increment)

columns : id,text,rid

Based on the input of a form, 2 rows are being inserted into a table.

INSERT INTO tablename (id,text,rid) 
VALUES (NULL, $usertext1, ??),(NULL, $usertext2, ??)

Now, Where is my problem's at ?

I need the first ?? and the second ?? to be the same int, and Both need to be the auto-increment id from the first insert! I tried LAST_INSERT_ID() but it returned 0.

The result how I'd like to have it :

1 | abctext | 1

2 | lalala | 1

3 | fajoif | 2

4 | oijgoi | 2

Any help would be highly appreciated :)

Thanks.

Answer 1

you have to follow something like this to get it done:

<?php 
$i=0;
$firstInsertId=0;
foreach($post as $value){// suppose each $post index contains the one complete post data
    $usertxt=$value['text'];
$sql="INSERT INTO tablename (id,text,rid) 
VALUES (NULL, $usertxt,$firstInsertId)";
$mysqli->query(sql);
if($i == 0)//flag to get first inserted Id
    $firstInsertId=$mysqli->insert_id;

  $i++;
}

$sql="update tablename set rid=$firstInsertId where id=$firstInsertId";
$mysqli->query(sql);