Error inserting record into daughter table with PHP

Question

Error inserting record into daughter table with PHP

Navigation

#1 by (0 votes)

0

Hello, I have the following tables:

| ----- tb_user ---------------------------------------- -----

| id - name - login - password - flag_active - permissao

| ------------------------------------------------- --------------- |

| -------- tb_regional ----------

| id - username - login - password

| --------------------------------- |

| ------ tb_regional_user -------

| tb_regional_id - tb_usuario_id

| --------------------------------------- |

And I'm trying to insert the records into the 'tb_user' table and into the 'tb_regional_user' table into a 'user_address' function, but the query that should do the insertion into the 'tb_regional_user' table fails by returning the following error: Can not add or update child row: a foreign key constraint fails . While the query that inserts user is executed successfully and the user is registered in the bank. From what I researched this means that I tried to insert a record 'tb_usuario_id' that does not exist in the parent table but the 'mysqli_insert_id ()' function returns exactly the record id that is inserted into the database when the function is executed. Below is 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!";
    }
}

Thanks very much if you can help me visualize what's wrong with the code.

php mysql mysqli

asked by anonymous 12.07.2016 / 17:22

1 answer

Customizing data from a session array in codeigniter Access Database, Duplication of Records

score 0 · Answer 1

I believe error is in $index
When you do foreach the index becomes a counter and not the regional value. Try to change $index by $valor as in the example below.
Or $valor['id'] if it is array. Or $valor->id if it is 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!";
    }
}