Perform table insertion with foreign key

Asked

Viewed 1,298 times

4

include_once 'acesso_bd/conexao_bd.php';

class PropriedadeDAO { 


    function inserirPropriedadeBD($propriedade) { 

        $nome = $propriedade->getNome();
        $endereco = $propriedade->getEndereco();
        $telefone = $propriedade->getTelefone();

        $conexaobd = new ConexaoBD;

        $conexao = $conexaobd->conectarAoBD();

        $sql = "INSERT INTO propriedade (nome, endereco, telefone) VALUES ('$nome', '$endereco', '$telefone')";

        if (!mysqli_query($conexao, $sql)) {
            echo "Erro: " . $sql . "<br>" . mysqli_error($conexao);
        }


    }

the above code refers to the registration of a property, where your ID is AUTO_INCREMENT.

include_once 'acesso_bd/conexao_bd.php';

class TalhaoDAO { 


    function inserirTalhaoBD($talhao) { 

        $nome = $talhao->getNome();


        $conexaobd = new ConexaoBD;

        $conexao = $conexaobd->conectarAoBD();


        $sql = "INSERT INTO talhao (nome, id_propriedade) VALUES ('$nome', LAST_INSERT_ID())";

        if (!mysqli_query($conexao, $sql)) {
            echo "Erro: " . $sql . "<br>" . mysqli_error($conexao);
        }


    }

This is the registration code of Talhao, which necessarily needs the foreign key "id_propriedade" to be identified. I used the LAST_INSERT_ID, the problem is that is returning me the following error when trying to register Talhao:

Error: INSERT INTO (name, id_property) VALUES ('aaa', LAST_INSERT_ID()) Cannot add or update a Child Row: a Foreign key Constraint fails (teste.talhao, CONSTRAINT id_propriedade_fk FOREIGN KEY (id_propriedade) REFERENCES propriedade (id_propriedade) ON DELETE NO ACTION ON UPDATE NO ACTION) 

CREATE TABLE IF NOT EXISTS `propriedade` (
  `id_propriedade` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(20) NOT NULL,
  `endereco` varchar(20) NOT NULL,
  `telefone` varchar(20) NOT NULL,
  PRIMARY KEY (`id_propriedade`)
) ENGINE=InnoDB  ;

CREATE TABLE IF NOT EXISTS `talhao` (
  `id_parcela` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(20) NOT NULL,
  `id_propriedade` int(11) NOT NULL,
  PRIMARY KEY (`id_parcela`),
  KEY `id_propriedade` (`id_propriedade`),
  KEY `id_propriedade_fk` (`id_propriedade`)
) ENGINE=InnoDB ;
php mysql database oop

  • Oops, here at documentation may help you. If I am not mistaken in Mysql you have to put '`' before and after the columns you want to insert values.

  • Hi, Marconi. Thanks for the comment, but I don’t think the quotes are the problem. I did the test here and it didn’t work.

  • Sabrina, can [Edit] the question and add the structure of the tables?

  • Added structures.

  • What is the structure of the tables? Try to change the name of the property table’s Primary key and leave it different from the name of the cut table’s Foreign key. But put the structure of the tables please.

  • Now that I’ve seen the structure...it tries to do what I said and puts for example, the property key Primary as id_prop And the same names for PK and FK usually give you a headache. .

  • Diéfani, I had already managed to carry out manual inserts directly in the bank, indicating the exact code of the property at FK and it worked. That is why I believe that the problem really is in LAST_INSERT_ID(). However, I will make a test with your suggestion.

  • Diéfani, I made a test with your suggestion and you keep making the same mistake.

  • Sabrina, in table carving the definition of FK would be CONSTRAINT id_propriedade_fk FOREIGN KEY (id_propriedade) REFERENCES propriedade(id_propriedade), because KEY is synonymous with INDEX

Show 4 more comments

3 answers

1

Propriedadedao class 

include_once 'acesso_bd/conexao_bd.php';

class PropriedadeDAO { 


    function inserirPropriedadeBD($propriedade) { 

        $nome = $propriedade->getNome();
        $endereco = $propriedade->getEndereco();
        $telefone = $propriedade->getTelefone();

        $conexaobd = new ConexaoBD;

        $conexao = $conexaobd->conectarAoBD();

        $sql = "INSERT INTO propriedade (nome, endereco, telefone) VALUES ('$nome', '$endereco', '$telefone')";

        if (!mysqli_query($conexao, $sql)) {
            echo "Erro: " . $sql . "<br>" . mysqli_error($conexao);
        }else {
        $propriedade->setID(mysqli_insert_id($conexao)); // Alteração
        }


    }

Property class

<?php

include_once '../dao/dao_propriedade.php';

class Propriedade { 
    private $id;
    private $nome; 
    private $endereco; 
    private $telefone; 

    function setID($id) { 
        $this->id = $id;
    } 

    function setNome($nome) { 
        $this->nome = $nome;
    } 

    function setEndereco($endereco) { 
        $this->endereco = $endereco;
    } 

    function setTelefone($telefone) { 
        $this->telefone = $telefone;
    } 

    function getID() { 
        return $this->id; 
    } 

    function getNome() { 
        return $this->nome; 
    } 

    function getEndereco() { 
        return $this->endereco;  
    } 

    function getTelefone() { 
        return $this->telefone;
    }

Classe Talhaodao ` 

include_once 'acesso_bd/conexao_bd.php';
include_once '../modelo/modelo_propriedade.php';

class TalhaoDAO { 



    function inserirTalhaoBD($talhao) { 

        $nome = $talhao->getNome();
        $idPropriedade = $talhao->getPropriedade(); //Alteração


        $conexaobd = new ConexaoBD;

        $conexao = $conexaobd->conectarAoBD();


        $sql = "INSERT INTO talhao (`nome`, `id_propriedade`) VALUES ('$nome', '$idPropriedade')";

        if (!mysqli_query($conexao, $sql)) {
            echo "Erro: " . $sql . "<br>" . mysqli_error($conexao);
        }


    } `

Classe Talhao ` 

include_once '../dao/dao_talhao.php';

class Talhao { 
    private $id;
    private $nome; 
    private $endereco; 
    private $telefone; 
    private $id_propriedade;

    function setID($id) { 
        $this->id = $id;
    } 

    function setNome($nome) { 
        $this->nome = $nome;
    } 

    function setPropriedade($id_propriedade){
    $this->id_propriedade = $id_propriedade;
    }


    function getID() { 
        return $this->id; 
    } 

    function getNome() { 
        return $this->nome; 
    } 

    function getPropriedade(){
     return $this->id_propriedade;
    }`

The error that is returning:

Error: INSERT INTO butcher (nome, id_propriedade) VALUES ('B2', '') Cannot add or update a Child Row: a Foreign key Constraint fails (teste.talhao, CONSTRAINT id_propriedade_fk FOREIGN KEY (id_propriedade) REFERENCES propriedade (id_prop) ON DELETE NO ACTION ON UPDATE NO ACTION)

1

You added KEY instead of FOREIGN KEY, ie, your table has Dice but no foreign key. This way will definitely give error of CONSTRAINT and FK. Take a backup first and then give a DROP TABLE talhao. It is better to delete the table so that you do not run the risk of something being left behind by giving a ALTER TABLE and creates the table as I put it below and see if it works. I removed the quotes just for preference. 

CREATE TABLE IF NOT EXISTS talhao (
  id_parcela int(11) NOT NULL AUTO_INCREMENT,
  nome varchar(20) NOT NULL,
  id_propriedade int(11) NOT NULL,
  PRIMARY KEY (id_parcela),
CONSTRAINT id_propriedade_fk FOREIGN KEY (id_propriedade) REFERENCES propriedade(id_propriedade)
)ENGINE=InnoDB;

Below is my export I created from test, also using phpMyAdmin.

-- phpMyAdmin SQL Dump
-- version 4.5.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 22, 2017 at 11:47 PM
-- Server version: 10.1.16-MariaDB
-- PHP Version: 5.5.38

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `teste`
--

-- --------------------------------------------------------

--
-- Table structure for table `propriedade`
--

CREATE TABLE `propriedade` (
  `id_propriedade` int(11) NOT NULL,
  `nome` varchar(20) NOT NULL,
  `endereco` varchar(20) NOT NULL,
  `telefone` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `talhao`
--

CREATE TABLE `talhao` (
  `id_parcela` int(11) NOT NULL,
  `nome` varchar(20) NOT NULL,
  `id_propriedade` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `propriedade`
--
ALTER TABLE `propriedade`
  ADD PRIMARY KEY (`id_propriedade`);

--
-- Indexes for table `talhao`
--
ALTER TABLE `talhao`
  ADD PRIMARY KEY (`id_parcela`),
  ADD KEY `id_propriedade_fk` (`id_propriedade`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `propriedade`
--
ALTER TABLE `propriedade`
  MODIFY `id_propriedade` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `talhao`
--
ALTER TABLE `talhao`
  MODIFY `id_parcela` int(11) NOT NULL AUTO_INCREMENT;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `talhao`
--
ALTER TABLE `talhao`
  ADD CONSTRAINT `id_propriedade_fk` FOREIGN KEY (`id_propriedade`) REFERENCES `propriedade` (`id_propriedade`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

  • I did as you suggested and error 1215 appeared.

  • Error: 1215 SQLSTATE: HY000 (ER_CANNOT_ADD_FOREIGN) Message: Cannot add Foreign key Constraint

  • Did you delete the cut table and copy and paste what I put here or did you type? Did you create it through code or through the tools of the manager you are using? Because I created it here and it worked. What are you using to manage BD? Phpmyadmin, Mysql Workbench...?

  • I deleted the plot table and copied and pasted what you typed. I’m using Phpmyadmin.

  • Is the test local? Xampp or Wamp? I usually have BD problems when I test locally and the problem is solved by restarting the servers. Above I made an edition and put the export database. Here gave no problem.

  • Without the foreign key, if it works, it is possible that the bank will enter empty values, NULL or 0.

  • It’s Wamp. I’ll try again. But now I was able to insert and remove the error message that was giving initially, the problem is that instead of inserting the last ID in the foreign key, is inserting 0.

  • Ah, I get it. So I’m going to try to insert it again the way you typed it.

  • I was able to add the table structure the way you suggested, but now I’m back to the initial hahaha error.

  • Error: INSERT INTO butcher (nome, id_propriedade) VALUES ('L6', ') Cannot add or update a Child Row: a Foreign key Constraint fails (teste.talhao, CONSTRAINT id_propriedade_fk FOREIGN KEY (id_propriedade) REFERENCES propriedade (id_prop))

  • If your tables are identical to mine, you can change in your code the id_prop for id_propriedade. Why the error now has two parentheses closing at the end?

  • Is that you had suggested changing the name of the property id, then I had done so. But I already put it as id_property again and follows the error. To almost give up rs

  • Worse than I test here with the bank like I told you and with the code that a boy put down and gives it right.

  • Yeah, he also said the same thing. I’m probably doing something wrong then.

  • By setting the foreign key id number I was able to enter. This means that probably the error is in the property id recovery :/

Show 10 more comments

1

The property insertion form: 

<body>
<h1> Nova propriedade</h1>
<form action="../controle/controle_propriedade.php" method="post">
    Nome:
    <br/>
    <input name="nome" type="text" value="" required="required">
    <br/>
    Endereco:
    <br/>
    <input name="endereco" type="text" value="" required="required">
    <br/>
    Telefone:
    <br/>
    <input name="telefone" type="text" value="" required="required">
    <br/>
    <input name="acao" type="hidden" value="inserir">
    <br/>
    <input type="submit" value="Cadastrar">
</form>
<br/>

The property control code: 

include_once '../modelo/modelo_propriedade.php';

$acao = ($_POST["acao"]);

switch ($acao) {
    case "inserir":
        $propriedade = new Propriedade;
        $propriedade->setNome($_POST["nome"]);
        $propriedade->setEndereco($_POST["endereco"]);
        $propriedade->setTelefone($_POST["telefone"]);

        $propriedade->cadastrarPropriedade();

        include_once '../visao/novo_talhao.html';

        break;

Browser other questions tagged php mysql database oop

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