Update with foreingkey

Asked

Viewed 46 times

0

I’m having trouble editing a key Foreign key. The code receives the id, but I can’t get past the id of Foreign key to the value using Inner Join. I receive this message:

Cannot add or update a Child Row: a Foreign key Constraint fails (impacto.pessoa, CONSTRAINT pessoa_ibfk_2 FOREIGN KEY (id_tp_pessoa) REFERENCES tp_pessoa (id_tp_pessoa))' in C:\AppServ\www\impacto\user\Pessoa\action_pessoa.php:263 Stack trace: #0 C:\AppServ\www\impacto\user\Pessoa\action_pessoa.php(263): PDOStatement->execute() #1 {main} thrown in C:\AppServ\www\impacto\user\Pessoa\action_pessoa. p

// pagina edicao
<?php
require 'conexao.php';

// Recebe o id do cliente do cliente via GET
$id_cliente = (isset($_GET['id_pessoa'])) ? $_GET['id_pessoa'] : '';

// Valida se existe um id e se ele é numérico
if (!empty($id_cliente) && is_numeric($id_cliente)):

    // Captura os dados do cliente solicitado
    $conexao = conexao::getInstance();
    $sql = 'SELECT id_pessoa , ds_nome , ds_departamento FROM pessoa 
    as a inner join  departamento as b on a.id_departamento=b.id_departamento

    WHERE id_pessoa = :id_pessoa';
    $stm = $conexao->prepare($sql);
    $stm->bindValue(':id_pessoa', $id_cliente);
    $stm->execute();
    $cliente = $stm->fetch(PDO::FETCH_OBJ);


endif;

?>  
<form action="action.php" method="post" id='form-contato' enctype='multipart/form-data'>
  <!-- area de campos do form -->


    <div class="form-group col-md-5">
      <label for="name">Nome </label>
      <input type="text" class="form-control" value="<?=$cliente->ds_nome?>" name="ds_nome">
    </div>


          <div class="col-xs-2">
                 <label for="ex1"> Departamento</label>



                <?php
try{
     $conexao = new PDO('mysql:host=localhost;dbname=impacto', 'root', 'cons2000');
     $conexao ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }catch(PDOException $e){
            echo 'ERROR: ' . $e->getMessage();
        }
 ?>
<select name="id_departamento"  class="form-control">

// o problema é que nao consigo passar o id_departamento para  dentro do value
<option  value="<?=$cliente->id_departamento?>"><?=$cliente->ds_departamento?> </option>

<?php
     $sql_admins = $conexao->prepare("SELECT * FROM departamento where id_departamento=id_departamento order by id_departamento ");
     $sql_admins->execute();

     while($admins = $sql_admins->fetch(PDO::FETCH_ASSOC)){


    echo '<option value="'.$admins['id_departamento'].'">'.$admins['ds_departamento'].'</option>';
      }
   ?>
     </select>

                    </div>



                    <input type="hidden" name="acao" value="editar">
                    <input type="hidden" name="id_pessoa" value="<?=$cliente->id_pessoa?>">

                    <button type="submit" class="btn btn-primary" id='botao'> 
                      Alterar
                    </button>
                    <a href='listar_pessoa.php' class="btn btn-danger">Cancelar</a>
                </form>
            <?php endif; ?> 


////pagina action.php
<?
if ($acao == 'editar'):



            $sql = 'UPDATE pessoa SET ds_nome=:ds_nome id_departamento=:id_departamento
             ';
            $sql .= 'WHERE id_pessoa = :id_pessoa';

            $stm = $conexao->prepare($sql);
            $stm->bindValue(':ds_nome', $ds_nome);  
            $stm->bindValue(':id_departamento', $id_departamento);
            $stm->bindValue(':id_pessoa', $id_pessoa);
            $retorno = $stm->execute();

            if ($retorno):
                echo "<div class='alert alert-info' role='alert'>Registro editado com sucesso, aguarde você está sendo redirecionado ...</div> ";
            else:
                echo "<div class='alert alert-danger' role='alert'>Erro ao editar registro!</div> ";
            endif;

            echo "<meta http-equiv=refresh content='3;URL=listar_pessoa.php'>";
        endif;
php

  • It is not related to PHP, but rather to Mysql, probably the ID you are trying to put is not registered in the table that connects the forekey to your query.

  • when I remove the Inner Join and change the ds_departamento by id_departamento the update works. but I need the department to appear in my select too .

  • You need to show how the tables are assembled to better understand what is happening, by the query itself you can not know right, as I said is related to mysql and not php, your code is not wrong. But as I said before, you are updating a row in the "person" table that needs to be related to the id_department and in your department table NEED to exist the id you are trying to register and your error is in UPDATE and not SELECT

1 answer

0

You are not selected the department id in the query with Join so the value comes blank, right ?

$conexao = conexao::getInstance();
  $sql = 'SELECT 
  id_pessoa, 
  ds_nome, 
  ds_departamento,
  b.id_departamento
  FROM pessoa as a 
  inner join  departamento as b on (a.id_departamento=b.id_departamento)
  WHERE id_pessoa = :id_pessoa';
  $stm = $conexao->prepare($sql);
  $stm->bindValue(':id_pessoa', $id_cliente);
  $stm->execute();
  $cliente = $stm->fetch(PDO::FETCH_OBJ);`

After the line $cliente = $stm->fetch(PDO::FETCH_OBJ); of a var_dump($cliente); die(); and checks if all the data you want to pick up is in the Obj $client

Browser other questions tagged php

You are not signed in. Login or sign up in order to post.