Save auto_increment id of a table to another table in the same file

Question

I want the value of ID automatically generated in auto_increment in the table wpne_posts is saved in another table called wpne_postmeta

The INSERT code of the first action is:

mysqli_query($con, "SELECT * FROM wpne_posts");
    $sql1 = "
    INSERT INTO
      `wpne_posts`(
        `ID`,
        `post_author`,
        `post_date`,
        `post_date_gmt`,
        `post_content`,
        `post_title`,
        `post_excerpt`,
        `post_status`,
        `comment_status`,
        `ping_status`,
        `post_password`,
        `post_name`,
        `to_ping`,
        `pinged`,
        `post_modified`,
        `post_modified_gmt`,
        `post_content_filtered`,
        `post_parent`,
        `guid`,
        `menu_order`,
        `post_type`,
        `post_mime_type`,
        `comment_count`
      )
    VALUES
      (
        ID,
        '$_post_author',
        '$_post_date',
        '$_post_date_gmt',
        '$_post_content',
        '$_post_title',
        '$_post_excerpt',
        '$_post_status',
        '$_comment_status',
        '$_ping_status',
        '$_post_password',
        '$_post_name',
        '$_to_ping',
        '$_pinged',
        '$_post_modified',
        '$_post_modified_gmt',
        '$_post_content_filtered',
        '$_post_parent',
        '$_guide',
        '$_menu_order',
        '$_post_type',
        '$_post_mime_type',
        '$_comment_count'
      )
      ";

Just below, I want to send the automatically generated ID value in the table wpne_posts for the wpne_postmeta table in the column post_id, see:

    mysqli_query($con, "SELECT * FROM wpne_postmeta");
$sql2 = "

        INSERT INTO
      `wpne_postmeta`(`meta_id`, `post_id`, `meta_key`, `meta_value`)
    VALUES
      (
        meta_id,
        'ID',
        '$meta_key_guest_name',
        '$meta_value_guest_name'
      ),
      (
        meta_id,
        'ID)',
        '$meta_key_timestamp',
        '$meta_value_timestamp'
      ),
      (
        meta_id,
        'ID',
        '$meta_key_timeslot',
        '$meta_value_timeslot'
      ),
      (
        meta_id,
        'ID',
        '$meta_key_cf_meta_value',
        '$meta_value_cf_meta_value'
      ),
        (
        meta_id,
        'ID',
        '$_appointment_title',
        ''
      ),
        (
        meta_id,
        'ID',
        '$_appointment_guest_surname',
        ''
      )

        ";

    mysqli_query($con, $sql1) or die("Erro SQL 1");
    mysqli_query($con, $sql2) or die("Erro SQL 2");

I tried to do with mysqli_insert_id(), but I get 0. I would like to save the value of the wpne_posts table ID into a variable or any other solution.

I think I’m a bit of a layman, so I appreciate your help anyway.

Answer 1

The best way to do this is by using the function mysqli_insert_id(). But this implies that the primary key of your table has the characteristic AUTO_INCREMENT, that is, your key must have been generated by auto-registration. Modify your INSERT and simply do not inform the field ID.

Let’s take a practical example:

<?php
    $con = mysqli_connect("localhost", "meu_usuario", "minha_senha","meu_bd");

    mysqli_query($con, "INSERT INTO Pessoas (PrimeiroNome, SegundoNome, Idade) 
    VALUES ('Pedro', 'Souza', 25)");

    $idGerado = mysqli_insert_id($con);

    // Imprimir o auto-generated ID
    echo "O novo ID do registro é: " . $idGerado; 

    mysqli_close($con);
?>

Note that in the INSERT executed above, I filled in the fields and values, but did not inform the ID column, as this is the work of Mysql, know who is the primary key and since it is AUTO_INCREMENT, assign it. After that the function mysqli_insert_id() is able to capture it.

After that I can use my captured ID, to perform other INSERTS:

<?php
    mysqli_query($con, "INSERT INTO Profissoes (IDPessoa, Profissao) 
    VALUES (" . $idGerado . ", 'Analista de Sistemas')");
?>

Answer 2

The solution was to invert and use mysqli_insert_id($con) saving in a variable before executing the query of the second table:

$ID = mysqli_insert_id($con);

Thank you!

Answer 3

The native way (no PHP functions) to identify the last ID generated by auto_increment is the function:

SELECT LAST_INSERT_ID();

It must be invoked right after an INSERT statement.

Note: The generated ID is kept on the server by connection. It means that the generated ID does not affect the ID generated by other connections/clients.

https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-Insert-id

Using your example:

$sql1 = "INSERT INTO `wpne_posts`(`ID`, `post_author`,`post_date`, ....)
                        VALUES (ID, '$_post_author','$_post_date', ....)";

//executa a instrução INSERT
mysqli_query($con, $sql1);

//obtem o último ID duma instrução INSERT
$lastID = mysqli_query($con, "SELECT LAST_INSERT_ID();");

//insere na segunda tabela
$sql2 = "INSERT INTO  `wpne_postmeta`(`meta_id`, `post_id`, `meta_key`, `meta_value`)
VALUES (meta_id, $lastID,'$meta_key_guest_name', '$meta_value_guest_name')";

//executa o script
mysqli_query($con, $sql2);