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

Question

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

Split them out into 2 separate queries. You can use `mysqli_insert_id` to grab the ID that was just created and then use that in your 2nd insert statement - http://php.net/manual/en/mysqli.insert-id.php#refsect1-mysqli.insert-id-examples — waterloomatt, Sep 19 '18 at 13:39
In addition to what @waterloomatt said: I'd recommend the use of a transaction for the whole process, so you either have all of the data or nothing, not a half-existing user which may break when you try to use him in your code. — Tobias F., Sep 19 '18 at 13:42
**Warning:** Don't use `md5()` for password hashing. It's old, super fast (which is bad when it comes to password hashing) and broken (hash collisions have occurred). Use PHP's `password_hash()` and `password_verify()` instead. — M. Eriksson, Sep 19 '18 at 13:42
You should also look into using prepared statements instead of manually building your queries like that. I don't know if you're escaping the data first, but thinking of security is extra important when using multi queries. — M. Eriksson, Sep 19 '18 at 13:43
@TobiasF. but how can I specify the table mysqli_insert_id will select? — , Sep 19 '18 at 13:55
@MagnusEriksson definetely, I am still studying prepared statements — , Sep 19 '18 at 13:56
@LuannSousa from the [manual page](http://php.net/manual/en/mysqli.insert-id.php): "Returns the auto generated id used in the **latest query**" The direct SQL equivalent would be `SELECT LAST_INSERT_ID()` — Tobias F., Sep 19 '18 at 14:03
@TobiasF. i've changed to ('null', mysql_insert_id()) didn't worked, the user is inserted, but nothing on the photos table — , Sep 19 '18 at 14:10
`mysql_insert_id()` needs the connection passed to it - see the manual. — waterloomatt, Sep 19 '18 at 14:11
@waterloomatt i've put the string connection mysqli_insert_id($conexao), but gives me Recoverable fatal error: Object of class mysqli could not be converted to string — , Sep 19 '18 at 14:14
You have two options: 1.) Use the PHP function `mysqli_insert_id()` according to the manual. 2.) Use the native (My)SQL approach by using the SQL function `LAST_INSERT_ID()`: `INSERT INTO esc_usuarios_fotos (...) VALUES ('null', LAST_INSERT_ID());` — Tobias F., Sep 19 '18 at 14:15
@TobiasF. it worked! i forgotted parentheses in last_insert_id haha, thanks everyone for the help — , Sep 19 '18 at 14:21
Be sure to accept the answer posted by @waterloomatt if it summarizes and also answers your question :) — Tobias F., Sep 19 '18 at 14:22

waterloomatt · Accepted Answer · 2018-09-19T14:57:10.387

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
    ...
}

Nice one. I would suggest to change "Pseudo code" to "Template code", since this is almost perfectly valid PHP. — Tobias F., Sep 19 '18 at 14:17

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

1 Answers1