phpsqlmysqli-multi-query

How to insert a value into an auto_increment of a different table in the same string


I am using mysqli_multi_query to insert user's information and a default null profile photo, there are two tables for that, "esc_usuarios" for the personal data and "esc_usuarios_fotos" for the photos.

What do I want is, after inserting the personal data, insert this null image in the "esc_usuarios_fotos" table binding "img_usu_codigo" with this inserted person, the person's id is being auto incremented in the column "usu_codigo" from the table "esc_usuarios".

The query:

<?php
$inserir_usuario = "INSERT INTO `esc_usuarios` 
    (
        usu_nome, 
        usu_senha, 
        usu_sobrenome, 
        usu_cpf, 
        usu_rg, 
        usu_nasc, 
        usu_endereco, 
        usu_numero, 
        usu_bairro, 
        usu_cep, 
        usu_cidade, 
        usu_uf, 
        usu_tel, 
        usu_cel, 
        usu_genero, 
        usu_email, 
        usu_indicador_codigo, 
        usu_datacadastro
    ) VALUES (
        '$nome', 
        '".md5('$password')."', 
        '$sobrenome', 
        '$cpf', 
        '$rg', 
        '$nascimento', 
        '$endereco', 
        '$numero', 
        '$bairro', 
        '$cep', 
        '$cidade', 
        '$uf', 
        '$tel', 
        '$cel',
        '$genero', 
        '$email', 
        '" . $_SESSION['codigo'] . "', 
        '$trn_date'
    );
    INSERT INTO esc_usuarios_fotos(img_local,img_usu_codigo) VALUES ('null', //i want to insert here the inserted person's id  "usu_codigo" of the first insert statement)";

$re = mysqli_multi_query($conexao, $inserir_usuario);

Solution

  • Tackling a few issues here. You really should be using parameterized inserts for security reasons. Split out your inserts and then use insert_id to grab the newly created ID from your person insert. Wrap everything in a transaction as others in the comments pointed out - this will ensure you get everything or nothing.

    And lastly, use mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); to turn MySQL errors into PHP exceptions. You can then wrap everything in a try/catch block. Good luck!

    Pseduo code:

    // Turn MySQL errors into PHP exceptions.
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    
    try {
        // Open connection
        $connection = new mysqli(...);
    
        // check connection 
        if (mysqli_connect_errno()) {
            printf("Connect failed: %s\n", mysqli_connect_error());
            exit();
        }
    
        // Start transaction here 
        ...
    
        /***********************************/
        /********** PERSON INSERT **********/
        /***********************************/
        if ($stmt = $connection->prepare("INSERT INTO `esc_usuarios` ... (?, ?, ...)")) {
    
            /* bind parameters for markers */
            $stmt->bind_param("s", $...);
            ...
    
            /* execute first query */
            $stmt->execute();
    
            /* This is the newly created ID */
            $id = $connection->insert_id
    
            /***********************************/
            /********** PHOTOS INSERT **********/
            /***********************************/
            if ($stmt = $connection->prepare("INSERT INTO `esc_usuarios_fotos` ... (?, ?)")) {
    
                /* Use that newly created ID here along with other params for your query */
                $stmt->bind_param("s", $id);
    
                /* execute query */
                $stmt->execute();
            }
        }
    
        // Commit transaction here
        ...
    }
    catch ( Exception $e ) {
        // Rollback transaction if the exception is DB related
        ...
    }