Error inserting record in child table with PHP

Question

Hello, I have the following tables:

|-----tb_usuario---------------------------------------------|
|id - name - login - password - flag_active - permission|
|----------------------------------------------------------------|

|--------tb_regional----------|
|id - name - login - password|
|---------------------------------|

|------tb_regional_usuario-------|
|tb_regional_id - tb_usuario_id|
|---------------------------------------|

And I am trying to insert the records in table 'tb_usuario' and table 'tb_regional_usuario' in a function 'cadastra_usuario()' but the query that should make the insertion in table 'tb_regional_usuario' fails to return the following error: Cannot add or update a Child Row: a Foreign key Constraint fails. While the query that inserts user is executed successfully and the user is registered in the database. By what I researched it means that I tried to insert a record 'tb_usuario_id' that does not exist in the parent table but the function 'mysqli_insert_id()' returns precisely the id of the record that is inserted in the database when the function is executed. Below follows the function for better understanding:

<?php    

include_once(__DIR__.'/../models/conexao.php');

//Função que cadastra usuários
function cadastra_usuario($nome, $login, $senha, $permissao, $flag_ativo, $regional)
{
    $conn = conectar();

    $query =  "INSERT INTO `tb_usuario` (`nome`, `login`, `senha`, `permissao`, `flag_ativo`)"
            . "VALUES ('$nome', '$login', MD5('$senha'), '$permissao', '$flag_ativo');";

    mysqli_query($conn, $query);

    $usuario_id = mysqli_insert_id($conn);

    //echo $query.'<br>';

    foreach ($regional as $index=>$valor)
    {
        $query2 = "INSERT INTO `tb_regional_usuario`(`tb_regional_id`, `tb_usuario_id`) "
                . "VALUES ('$index', '$usuario_id');";

        mysqli_query($conn, $query2) or die(print_r(mysqli_error($conn)));

        //echo $query2.'<br>';

        $valor++;

        //unset($valor);
    }


    if(mysqli_affected_rows($conn) != 0)
    {
        header("Location: ../../views/menu.php?pag=usuarios");

        //var_dump(mysqli_insert_id());
        //var_dump(mysqli_affected_rows($conn));

        $_SESSION['status_registro'] = "Registro inserido com sucesso!";
    }
}

I really appreciate you guys helping me visualize what’s wrong with the code.

Answer 1

I believe the error is in $index
When you do the foreach the index becomes a counter and not the regional value. Try to change the $index for $valor as in the example below.
Or $valor['id'] if it is array.
Or $valor->id if it is an object.

<?php    

include_once(__DIR__.'/../models/conexao.php');

//Função que cadastra usuários
function cadastra_usuario($nome, $login, $senha, $permissao, $flag_ativo, $regional)
{
    $conn = conectar();

    $query =  "INSERT INTO `tb_usuario` (`nome`, `login`, `senha`, `permissao`, `flag_ativo`)"
            . "VALUES ('$nome', '$login', MD5('$senha'), '$permissao', '$flag_ativo');";

    mysqli_query($conn, $query);

    $usuario_id = mysqli_insert_id($conn);

    //echo $query.'<br>';

    foreach ($regional as $index => $valor)
    {
        $query2 = "INSERT INTO `tb_regional_usuario`(`tb_regional_id`, `tb_usuario_id`) "
                . "VALUES ('$valor', '$usuario_id');";

        mysqli_query($conn, $query2) or die(print_r(mysqli_error($conn)));

        //echo $query2.'<br>';

        $valor++;

        //unset($valor);
    }


    if(mysqli_affected_rows($conn) != 0)
    {
        header("Location: ../../views/menu.php?pag=usuarios");

        //var_dump(mysqli_insert_id());
        //var_dump(mysqli_affected_rows($conn));

        $_SESSION['status_registro'] = "Registro inserido com sucesso!";
    }
}