MySQL returning wrong insert ID

Question

I ran this SQL statement in PHP to copy data from all columns from "other" into "table" (both table and other have same column names)

    $x=mysqli_query($conn,"INSERT INTO table SELECT * from other WHERE item_id='33'");
    $nid=mysqli_insert_id($conn);

I would expect $nid to have the new ID from "table" that it just inserted, but instead it returns "33", the ID of the other table coping from.

Maybe it copied everything including the ID from the other table and returned that?

Anybody knows why or how to fix?

Thanks

Since you need to select all columns except one (`item_id`) I included in my answer a code snippet to do that. See my edit... — Paolo, Mar 26 '17 at 08:54

Paolo · Answer 1 · 2017-03-26T08:51:50.083

As both tables have the same column names your insert is setting the id as well as all the other fields.

To avoid that select from other all the fields except the item_id

You can write statically all the column names (except one) in the query in place of * or you can build the column list dinamically with a preliminary query:

// Get all columns names except one
// as comma separated list
// Column names are bacltick quoted

$result = $conn->query( 
    "SELECT `COLUMN_NAME` "
  . "FROM `INFORMATION_SCHEMA`.`COLUMNS` " 
  . "WHERE `TABLE_SCHEMA`='your_db_name' " // DB name here
  . "AND `TABLE_NAME`='other' "            // TABLE name here
  . "AND `COLUMN_NAME` != 'item_id'" );    // EXCLUDED column here

if( ! $result )
{
    // HANDLE EXCEPTION
}

$columns = $result->fetch_all();

foreach( $columns as &$c )
{
    $c = '`' . $c[ 0 ] . '`';
}

$columns = implode( ',', $columns );


// Perform the copy

$x = mysqli_query( $conn, "INSERT INTO `table` SELECT $columns from `other` WHERE item_id='33'");
$nid = mysqli_insert_id( $conn );

Side note: if your destination table name is table it must be escaped between backticks `table`.

either manually or programmatically. For the latter see http://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql — Paolo, Mar 25 '17 at 21:41

MySQL returning wrong insert ID

1 Answers1