Update mysql problem

Asked

Viewed 131 times

0

I have a function that performs an update:

function alterarConta(){
     $dataemissao = $_POST['dataemissao'];
     $id = $_POST['id'];
     $select_fornecedores = $_POST['select_fornecedores'];
     $valor = $_POST['valor'];
     $datavencimento = $_POST["datavencimento"];
     $especie = $_POST['especie'];
     $observacao = $_POST['observacao'];
     $banco = abrirBanco();
     $sql = " UPDATE contas SET c.dataemissao = '$dataemissao', c.id_fornecedor = '$select_fornecedores', c.valor = '$valor', c.datavencimento = '$datavencimento', c.especie = '$especie', c.observacao = '$observacao' FROM contas c INNER JOIN pessoa f ON (c.id_fornecedor = f.id) WHERE c.id = '$id'";
    $banco->query($sql);
    $banco->close();

    header('Location: consulta_contas.php');
}

I’m having a hard time, I can’t find where you’re wrong.

The structure: inserir a descrição da imagem aqui

php mysql database

  • What error is displayed? Or what is the difficulty?

  • 2

    Leticia, take a look here to learn how to perform a UPDATE with INNER JOIN can help you.

  • 1

    @Everson can do an UPDATE with INNER JOIN yes.

  • 1

    I think what @Everson meant was that it is not possible to perform an UPDATE with FROM.

  • @Pantoja that’s right, thank you!

  • 1

    The error that appears: Cannot add or update a Child Row: a Foreign key Constraint fails (despesas.contas, CONSTRAINT contas_ibfk_1 FOREIGN KEY (id_fornecedor) REFERENCES pessoa (id))

  • I tried something like: $sql = "UPDATE accounts AS c INNER JOIN person AS p ON(c.id_vendor = p.id) SET c.outgoing date = '$outgoing date', c.id_vendor = '$select_vendors', c.value = '$value', c.outgoing date = '$due date', c.especie = '$specify', c.note = '$note' WHERE c.id = '$id' "; But it hasn’t worked out yet. I added a photo with the structure of the tables

Show 2 more comments

1 answer

0

Right at the beginning of the update replace update contas for update c

After the from you set alises for the tables and when you try to perform the update directly in the table the update gets lost and tries to update the table as a whole.

function alterarConta(){
     $dataemissao = $_POST['dataemissao'];
     $id = $_POST['id'];
     $select_fornecedores = $_POST['select_fornecedores'];
     $valor = $_POST['valor'];
     $datavencimento = $_POST["datavencimento"];
     $especie = $_POST['especie'];
     $observacao = $_POST['observacao'];
     $banco = abrirBanco();
     $sql = " UPDATE c SET c.dataemissao = '$dataemissao', c.id_fornecedor = '$select_fornecedores', c.valor = '$valor', c.datavencimento = '$datavencimento', c.especie = '$especie', c.observacao = '$observacao' FROM contas c INNER JOIN pessoa f ON (c.id_fornecedor = f.id) WHERE c.id = '$id'";
    $banco->query($sql);
    $banco->close();

    header('Location: consulta_contas.php');
}

  • Every update with multiple tables should refer to the alias and not the table directly.

Browser other questions tagged php mysql database

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