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

Navigation
1

I want the ID automatically generated in auto_increment in the wpne_posts table to be saved to another table named wpne_postmeta

The INSERT code for 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'
      )
      ";

Next, I want to send the automatically generated ID value in the wpne_posts table to the wpne_postmeta table in the post_id column, 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 value 0. I would like to save the ID value of the wpne_posts table to a variable or any other solution.

I think I'm kind of a layperson, anyway I appreciate help.

    
asked by anonymous 15.08.2018 / 20:23

3 answers

1

The best way to do this is to use the mysqli_insert_id() function. However this implies that the primary key of your table has the AUTO_INCREMENT feature, that is, your key must have been generated by self-healing. Modify your INSERT and simply do not enter the ID field.

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

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

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

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

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!

    
15.08.2018 / 21:06
0

The native form (without PHP functions) of identifying the last ID generated by auto_increment is the function: 

SELECT LAST_INSERT_ID();

It should be invoked immediately following an INSERT statement.

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

link

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);
    
16.08.2018 / 20:50
Font Awesome icons do not get in PrimeNG How to Decrease TAB Size in Sublime Text 3