Doubt with relation of tables

Asked

Viewed 107 times

4

Good evening I have the following BD in Mysql

    CREATE TABLE IF NOT EXISTS `cad_cliente` (
      `id_cliente` smallint(5) unsigned NOT NULL,
      `nome_cliente` varchar(45) NOT NULL,
      `nome_dr` varchar(45) NOT NULL,
      `email_cliente` varchar(100) NOT NULL,
      `data_nascimento_cliente` date NOT NULL,
      `endereco_cliente` varchar(30) NOT NULL,
      `bairro_cliente` varchar(20) NOT NULL,
      `cep_cliente` varchar(20) NOT NULL,
      `cidade_cliente` varchar(45) NOT NULL,
      `estado_cliente` smallint(5) unsigned NOT NULL,
      `observacao` varchar(45) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   CREATE TABLE IF NOT EXISTS `cad_paciente` (
     `id_paciente` smallint(5) unsigned NOT NULL,
     `nome_paciente` varchar(45) NOT NULL,
     `id_cliente` varchar(45) NOT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

   CREATE TABLE IF NOT EXISTS `cad_trabalho` (
     `id_trabalho` smallint(5) unsigned NOT NULL,
     `id_cliente` varchar(45) NOT NULL,
     `id_paciente` varchar(45) NOT NULL,
     `id_dente` varchar(45) NOT NULL,
     `id_servico` varchar(45) NOT NULL,
     `id_cor` varchar(45) NOT NULL,
     `observacao_trabalho` varchar(45) NOT NULL,
     `data_entrada_trabalho` varchar(45) NOT NULL,
     `data_saida_trabalho` varchar(45) NOT NULL,
     `pronto_trabalho` varchar(45) NOT NULL,
     `valor_trabalho` varchar(45) NOT NULL,
     `pagamento_trabalho` varchar(45) NOT NULL,
     `foto1_trabalho` varchar(45) NOT NULL,
     `foto2_trabalho` varchar(45) NOT NULL,
     `foto3_trabalho` varchar(45) NOT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I would like to know how to display a query to the database via PHP so that when requesting a page that shows me the table data cad_trabalho, this request search for patient and customer name data in the database.

php mysql relationship
  • 1

    Do you want to "display" a query or need help creating the query? (how do I display a query)... Have you tried creating this query or don’t know where to start?

  • 1

    Good evening @Pauloroberto, I know how to do in a way that would have to create several times the following command $trabalho = mysql_query("select * from cad_trabalho where id_trabalho='".$id_trabalho."'"); and $cliente = mysql_query("select * from cad_cliente where id_cliente='".$id_cliente."'"); and $paciente = mysql_query("select * from cad_paciente where id_paciente='".$id_paciente."'"); I’ve been researching about relationships of tables which has a simpler way of making that query I don’t know where to start

  • 1

    and to display would have to do as follows while($C = mysql_fetch_array($cliente)) {
 $nome_cliente = $V["nome_cliente"]; while($P = mysql_fetch_array($paciente)) {
 $nome_paciente = $V["nome_paciente"]; while($T = mysql_fetch_array($trabalho)) {
 $id_trabalho = $V["nome_trabalho"]; So on to receive all necessary data

  • @Cristianocardososilva Use the field of answer below to present solutions, here is to ask for suggestions or suggest improvements

  • 1

    Good evening @Maiconcarraro I’m just informing to Paulo Roberto how I make my requests today

  • Yes, I understand, but here it is mt short the field

Show 1 more comment

3 answers

3

You did not specify if your doubt is in the assembly of SELECT or in the construction of the code PHP, then we’ll go in pieces:

SQL

(Using INNER JOIN to relate the tables)

SELECT observacao_trabalho, nome_cliente, nome_paciente 
FROM cad_trabalho 
INNER JOIN cad_cliente ON cad_cliente.id_cliente = cad_trabalho.id_cliente 
INNER JOIN cad_paciente ON cad_paciente.id_paciente = cad_trabalho.id_paciente

PHP

(Educational example only)

<?php
    $servidor = "localhost"; /* exemplo */
    $usuarioBanco = "root"; /* exemplo */
    $senhaBanco = "123456"; /* exemplo */
    $nomeBanco = "exemplo"; /* exemplo */

    /* mysqli é o que você utiliza para fazer conexão com o banco */
    $conn = new mysqli($servidor, $usuarioBanco, $senhaBanco, $nomeBanco);

    $sql = "SELECT observacao_trabalho, nome_cliente, nome_paciente FROM cad_trabalho INNER JOIN cad_cliente ON cad_cliente.id_cliente = cad_trabalho.id_cliente INNER JOIN cad_paciente ON cad_paciente.id_paciente = cad_trabalho.id_paciente";

    /* resultado da consulta */
    $result = $conn->query($sql);

    /* verifica se retornou algo */
    if ($result->num_rows > 0) {

        /* começa a construir a tabela no HTML */
        echo "<table><tr><th>Trabalho</th><th>Cliente</th><th>Paciente</th></tr>";

        /* percorre o retorno da consulta */
        while($row = $result->fetch_assoc()) {
            /* dentro do $row[] vai o nome da coluna da sua consulta */
            echo "<tr><td>".$row["observacao_trabalho"]."</td><td>".$row["nome_cliente"]." ".$row["nome_paciente"]."</td></tr>";
        }
        echo "</table>";
    } else {
        echo "Nenhum resultado encontrado";
    }

    /* fecha conexão */
    $conn->close();
?>

Considerations

  • I have tested here but do not search the information in the database and still generates an error Notice: Trying to get property of non-object in C:\xampp\htdocs\lista_servicos.php on line 18&#xA;Nenhum resultado encontrado

  • Ai the error is in this function if ($result->num_rows > 0)

0

I have tested here but does not search the information in the database and still generates an error 

Notice: Trying to get property of non-object in C:\xampp\htdocs\lista_servicos.php on line 18 Nenhum resultado encontrado

The error is in this function

if ($result->num_rows > 0)

0

Good morning my friends, with the tips of our friendly here I managed to solve my select that was like this

SELECT * FROM cad_trabalho
JOIN  cad_paciente ON cad_trabalho.id_paciente
JOIN  cad_cliente ON cad_paciente.id_cliente WHERE cad_trabalho.id_trabalho=1

Now only I need to mount php to display the results

Browser other questions tagged php mysql relationship

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