Difficulty in 1:n

Asked

Viewed 49 times

1

Hello, I’m having great difficulty in making the relationship a table (clientes) and another (acompanhamento). Come on.

I have these 2 tables in my comic book - clients and follow-up. The follow-up table should have salespeople contacts with our customers, that is to say more than one line per customer, and that is where the problem arises, do not know how to combine my tables to be able to identify each contact with their respective client via customer ID in the client table.

Follow the table structure and the code I use to see if any of you can help me:

Follow-up 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))

Table customers 

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 table entries acompanhamento not only the respective lines to the client with id (15) for example.

php mysql

  • The column id_cliente on the table acompanhamento must be int(10), which is the same data type as in the column id on the table clientes (your reference)

  • Not understood the problem, is appearing some error? It is not working as desired, in case, how it should work?

  • Guilherme changed to int(10) but n solved nothing :/

  • what happens is that the result n is filtered by the client that has already been pre-selected, it displays all table entries not only the respective lines to the client with id (example)

  • Read this answer here from the site to understand how Joins work.

  • Regarding the type of data in the bank, it was just an observation. About the problem, I still do not understand very well, can put a photo of how is the exit and how should be?

Show 1 more comment

1 answer

-1

The first major error is that while more mysql_fetch_array() makes no sense. Or you use 

while($sql = mysql_fetch($limite))

or uses mysql_fetch_array() and executes a loop (for) on top of the returned matrix, because mysql_fetch_array() returns all rows at once.

I also suggest to clean up the example, both for its own clarity and to leave the question less curled up. The variable $sql or contains an SQL string, or contains the result of mysql_fetch_array()... the variables $Query and $sqlmensagem do nothing...

Go wiping the code, do it 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’s code.

Browser other questions tagged php mysql

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