Difficulty in 1: n

Question

Difficulty in 1: n

Navigation

#1 by (-1 votes)

1

Hello, I'm having a hard time making the relationship of one table ( clientes ) and another ( acompanhamento ). Come on.

I have these 2 tables in my DB - clients and monitoring. The follow-up table should have the contacts of the vendors with our clients, ie more than one line per customer, and that is where the problem arises, I do not know how to combine my tables in order to identify each contact with its respective customer via ID of the client in the Customers table.

Here is the structure of the tables and the code I use to see if any of you can help me:

Monitoring table

CREATE TABLE pluma156_pena.acompanhamento (
      id_mensagem int(11) NOT NULL AUTO_INCREMENT,
      nome varchar(50) NOT NULL,
      mensagem text NOT NULL,
      data_con date NOT NULL,
      data_ret date NOT NULL,
      id_cliente int(10) NOT NULL,
      PRIMARY KEY (id_mensagem, id_cliente))

Customers table

CREATE TABLE pluma156_pena.clientes (
    id int(10) NOT NULL AUTO_INCREMENT,
    data_cadastrada date DEFAULT NULL,
    data_nascimento date DEFAULT NULL,
    nome varchar(255) DEFAULT NULL,
    telefone varchar(15) DEFAULT NULL,
    celular varchar(16) DEFAULT NULL,
    sexo int(1) DEFAULT 0,
    email varchar(255) DEFAULT NULL,
    senha varchar(255) DEFAULT NULL,
    cep varchar(10) DEFAULT NULL,
    endereco varchar(255) DEFAULT NULL,
    numero varchar(12) DEFAULT NULL,
    complemento varchar(255) DEFAULT NULL,
    bairro varchar(255) DEFAULT NULL,
    cidade varchar(255) DEFAULT NULL
    PRIMARY KEY (id))

PHP Code

<?php       
    session_start(); 
    include("../conexao.php"); 

    if (isset($_SESSION['MSLogin']) and isset($_SESSION['MSSenha']) )
    { }
    else 
    { 
        header("Location: index.php"); 
        exit; 
    }

    $Query = mysql_query("SELECT * FROM clientes WHERE id = '".$_GET["ID"]."'");
    $sqlmensagem = "select id from clientes where id = '".$_GET["ID"]."'";
    $sql = "select * from acompanhamento where id_cliente =  '".$_GET["ID"]."'"; // estou selecionando tudo o que tem na tabela teste bd
    $limite = mysql_query("$sql") or die(mysql_error());

    //laço para mostrar todos os dados da tabela teste
    while($sql = mysql_fetch_array($limite))
    {              
        $id_mensagem = $sql["id_mensagem"];
        $nome        = $sql["nome"];
        $mensagem    = $sql["mensagem"];
        $data_con    = date('d/m/Y');
        $data_ret    = date('d/m/Y');
        //estou mostrando em tela os dados do bd       
        echo "–Assunto-: $id_mensagem–<br>Nome: $nome<br>Assunto:  $mensagem<br>Na data $data_con e retornar na data $data_ret<br><br>"; 
    }
?>

What happens is that the result n is filtered by the client that has already been pre-selected, it displays all entries in the acompanhamento table, not only the respective lines to the client with id (15) for example.

php mysql

asked by anonymous 03.04.2018 / 21:33

1 answer

How to break an infinite loop using Ctrl-C in Java strftime does not work locally

score -1 · Answer 1

The first big mistake is that while more mysql_fetch_array () does not make sense. Or you use

while($sql = mysql_fetch($limite))

or use mysql_fetch_array() and loop ( for ) on top of the returned array, because mysql_fetch_array() returns all rows at once.

I also suggest cleaning the example, both for your own clarity and to leave the question less involved. The variable $sql ora contains a SQL string, sometimes it contains the result of mysql_fetch_array() ... the variables $Query and $sqlmensagem do nothing ...

Go wipe the code, make echo $sql before calling mysql_query() to make sure that the SQL query is formatted the way you expect, and that the ID is actually the client code.