Relate tables MYSQL

Question

Good afternoon everyone,

When I try to insert data in my schedule table, the following error returns.

1452 - Cannot add or update a child row: a foreign key constraint fails (u748203187_agenda.agendamentos, CONSTRAINT fk_agendamentos_clientes FOREIGN KEY (id_users) REFERENCES clients (id_users) ON DELETE NO ACTION ON UPDATE NO ACTION)

Below are my tables:

CREATE TABLE `scheduling` (
  `id` int (11) NOT NULL,
  `name` varchar (220) NOT NULL,
  `phone` varchar (20) NOT NULL,
  varchar (220) NOT NULL services,
  `data` datetime NOT NULL,
  `id_users` int (11) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = latin1;

CREATE TABLE `customers` (
  `id_users` int (11) NOT NULL,
  `name` varchar (50) NOT NULL,
  `login` varchar (20) NOT NULL,
  `password` varchar (32) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = latin1;

ALTER TABLE `scheduling`
  ADD PRIMARY KEY (`id`,` id_users`) USING BTREE,
  ADD KEY `fk_agendamentos_clientes_idx` (` id_users`);

ALTER TABLE `scheduling`
  ADD CONSTRAINT `fk_agendamentos_clientes` FOREIGN KEY (` id_users`) REFERENCES `clients` (` id_users`) ON DELETE NO ACTION ON UPDATE NO ACTION;
COMMIT;

connection.php:

<?php
$servidor = "localhost";
$usuario = "u748203187_agenda";
$senha = "My passoword";
$dbname = "u748203187_agenda";

$conn = mysqli_connect($servidor, $usuario, $senha, $dbname);

?>

index.php:

<body>
<form class="form-horizontal" action="processa.php" method="POST"> 
        <div class="col-sm-3 col-sm-offset-3">         
        <label>Nome</label>
        <input class="form-control" type="text" name="nome" placeholder="Digite seu nome" required> 
    </div>
    <div class="col-sm-3"> 
      <label>Telefone</label>         
      <input class="form-control" type="text" name="telefone" placeholder="Digite seu telefone" required>
    </div>
  
        <div class="col-sm-6 col-sm-offset-3">
            <label>Data e hora</label>
                <div class="input-group date data_formato" data-date-format="dd/mm/yyyy HH:ii">
                    <input class="form-control" type="text" name="data" placeholder="Data do serviço">
                    <span class="input-group-addon">
                        <span class="glyphicon glyphicon-th"></span>
                    </span>
                  </div> 
        </div>
        
            <div class="col-sm-offset-3 col-sm-6"><br>
                <button type="submit" class="btn btn-success">Agendar</button>
          <a class="btn btn-primary btn_carrega_conteudo" href='#' id="pagina">Ver agendamentos</a><br><br>
          <?php
            if(isset($_SESSION['msg'])){
            echo $_SESSION['msg'];
            unset($_SESSION['msg']);
            }
          ?>
            </div>
        
    </form>

  <div class="col-sm-6 col-sm-offset-3" id="div_conteudo"><!-- div onde será exibido o conteúdo-->
    <img id="loader" src="loader.gif" style="display:none;margin: 0 auto;">
  </div>  
</div>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>
<script src="js/bootstrap-datetimepicker.min.js"></script>
<script src="js/locales/bootstrap-datetimepicker.pt-BR.js"></script>
<script type="text/javascript">
    $('.data_formato').datetimepicker({
        weeKStart: 1,
        todayBtn: 1,
        autoclose: 1,
        todayHighlight: 1,
        startView: 2,
        forceParse: 0,
        showMeridian: 1,
        language: "pt-BR",
        startDate: '-0d'
    });

   $(document).ready(function(){// Ao carregar a página faça o conteudo abaixo
        $('.btn_carrega_conteudo').click(function(){// Ao clicar no elemento que contenha a classe .btn_carrega_conteudo faça...
                        
        var carrega_url = this.id; //Carregar url pegando os dados pelo ID
        carrega_url = carrega_url+'_listar.php'; //Carregar a url e o conteudo da página
                        
            $.ajax({ //Carregar a função ajax embutida no jQuery
                url: carrega_url,
                           
                //Variável DATA armazena o conteúdo da requisição
                success: function(data){//Caso a requisição seja completada com sucesso faça...
                    $('#div_conteudo').html(data);// Incluir o conteúdo dentro da DIV
                },
                           
                beforeSend: function(){//Antes do envio do cabeçalho faça...
                    $('#loader').css({display:"block"});//carregar a imagem de load
                },
                           
                complete: function(){//Após o envio do cabeçalho faça...
                    $('#loader').css({display:"none"});//esconder a imagem de load
                }
            });  
        });
    });
</script>

page_list.php:

<?php 
session_start();
include_once("conexao.php");

?>

<div class="panel panel-success text-center">
<nav class="navbar navbar-default">
    <h3 class="text-center text-success">Agendamentos deste mês</h3>
</nav>
<?php
    $result_horarios = "SELECT * FROM agendamentos WHERE MONTH(data) = MONTH(CURDATE()) AND YEAR(data) = YEAR(CURDATE())";
    $resultado_horarios = mysqli_query($conn, $result_horarios);
    while($row_horarios = mysqli_fetch_array($resultado_horarios)){
        echo "<strong>Nome:</strong> ".$row_horarios['nome']."<br>";
        echo "<strong>Telefone</strong> ".$row_horarios['telefone']."<br>";
        echo "<strong>Data e Horário:</strong> ".date('d/m/Y H:i:', strtotime($row_horarios['data']))."<br>";
        echo "<hr>";
    }
?>

<div class="panel panel-danger text-center">
<nav class="navbar navbar-default">
    <h3 class="text-center text-danger">Agendamentos para hoje</h3>
</nav>
<?php
    $result_horarios = "SELECT * FROM agendamentos WHERE DAY(data) = DAY(CURDATE()) AND MONTH(data) = MONTH(CURDATE()) AND YEAR(data) = YEAR(CURDATE())";
    $resultado_horarios = mysqli_query($conn, $result_horarios);
    while($row_horarios = mysqli_fetch_array($resultado_horarios)){
        echo "<div class='text-center'>";
        echo "<strong>Nome:</strong> ".$row_horarios['nome']."<br>";
        echo "<strong>Telefone</strong> ".$row_horarios['telefone']."<br>";
        echo "<strong>Data e Horário:</strong> ".date('d/m/Y H:i:', strtotime($row_horarios['data']))."<br>";
        echo "</div>";
        echo "<br>";
    }

process.php:

<?php
session_start();


include_once("conexao.php");

//Receber os dados do formulário
$data = $_REQUEST['data'];
$nome = $_REQUEST['nome'];
$telefone = $_REQUEST['telefone'];

$data = explode(" ", $data);
list($date, $hora) = $data;
$data_sem_barra = array_reverse(explode("/", $date));
$data_sem_barra = implode("-", $data_sem_barra);
$data_sem_barra = $data_sem_barra . " " . $hora;

if(empty($_POST['nome']) || empty($_POST['data'])){
$_SESSION['msg'] = "<div class='alert alert-warning'>Preencha os campos corretamente</div>";
header("Location: index.php"); 
}else{

$result_data = "INSERT INTO agendamentos(data, nome, telefone) VALUES ('$data_sem_barra','$nome','$telefone')";
$resultado_data = mysqli_query($conn, $result_data);

if(mysqli_insert_id($conn)){
    $_SESSION['msg'] = "<div class='alert alert-success'>Agendamento efetuado com sucesso</div>";
    header("Location: index.php");
}else{
$_SESSION['msg'] = "<div class='alert alert-danger'>Erro ao efetuar o agendamento</div>";
    header("Location: index.php");
}
}

Please if anyone can help me I would be very grateful, I have been looking for a solution to this problem for weeks.

I thank you all.

You cannot create a child row before the associated parent row has been created. Its as simple as that — RiggsFolly, Jul 09 '20 at 18:22
Have you already created the `clients` table? I would guess not, so move the create for that above the failing ALTER command — RiggsFolly, Jul 09 '20 at 18:24
What was the point of destroying @Nigelren edit, which made the question readable — RiggsFolly, Jul 09 '20 at 18:26
You are trying to add a `ADD CONSTRAINT `fk_agendamentos_clientes` FOREIGN KEY (` id_users`) REFERENCES `clients` (` id_users`)` this part `REFERENCES `clients` (` id_users`)` means MySQL need to know what the `clients` table looks like before it can make that association — RiggsFolly, Jul 09 '20 at 18:36
So either create `clients **before** running that command OR move that whole command to **after** you do create the `clients` table. — RiggsFolly, Jul 09 '20 at 18:37
Are you running these DDL command you show OR are you running some SQL to Insert or Update the `scheduling` table — RiggsFolly, Jul 09 '20 at 19:58
So you are doing an INSERT Right?? So the code you show us is NOT Particularly relevant to the error or your situation — RiggsFolly, Jul 09 '20 at 22:36
The insert is being accurate, when I disable the foreign key id_users the data is inserted, but without the id_users like foreing key — vinicius, Jul 09 '20 at 23:38
So make sure that you are creating the `client` row exists (Has Been Inserted) before you attempt to create the `scheduling` row and that when you create the scheduling row it has a valid `id_user` set — RiggsFolly, Jul 10 '20 at 09:34
Maybe if you were to ask another question and this time include the PHP code that you are getting the issue with we could find the issue quite quickly — RiggsFolly, Jul 10 '20 at 09:35
I will edit the same question because my questions have already expired, as soon as possible I redo another question — vinicius, Jul 10 '20 at 16:20
@Riggs Folly I posted the question with the foreign key title # 1452, please if you can help me — vinicius, Jul 13 '20 at 19:51

Relate tables MYSQL

0 Answers0