Query Mysql with several tables

Navigation
0

I'm having trouble performing a select on some tables, I did the following.

I have 5 tables (module, components, step, images, records)

the records table only receives ID's from other tables, it unifies the records actually,

What I need to do is fetch the records from the other tables based on the iD's that are in the record table. I have tried to do so but it is returning me repeated values. 

SELECT modulo.nome_modulo,modulo.indice,modulo.revisao,componentes.codigo,componentes.local,etapa.etapa,componentes.quantidade,imagens.imagem FROM registros
INNER JOIN etapa ON etapa.id_etapa= registros.id_etapa
INNER JOIN componentes ON  componentes.id_componente = registros.id_componente
INNER JOIN imagens ON  imagens.id_componente = registros.id_imagem
INNER JOIN modulo ON modulo.id_modulo = registros.id_modulo

WHERE.....

I'm a beginner, I do not know if this is possible or not if this was the best way to do it.

    
asked by anonymous 15.02.2018 / 17:33

2 answers

0

So, in the main table you have the id of the user, in the other tables you make a field with the name idcli, at the time of the insert, that idcli receives the user id of the main table, then to recover you do: 

<?php
$mysqli = new mysqli("host", "user", "password", "db");

$id = $_POST['id'];

if ($sth = $mysqli->query("SELECT * FROM suaTabela WHERE idcli='$id'")){
    while ($row = $sth->fetch_assoc()) {
        echo $row['nome'];

    }
}

if ($sth = $mysqli->query("SELECT * FROM suaTabela2 WHERE idcli='$id'")){
    while ($row = $sth->fetch_assoc()) {
        echo $row['nome2'];

    }
}

And so on

    
15.02.2018 / 17:47
0

Well I do not know exactly how your tables are, but I would change the query to the following, just putting () and DISTINCT to a better organization: 

SELECT DISTINCT modulo.nome_modulo,modulo.indice,modulo.revisao,componentes.codigo,compone
ntes.local,etapa.etapa,componentes.quantidade,imagens.imagem FROM 
((((registros
INNER JOIN etapa ON etapa.id_etapa= registros.id_etapa)
INNER JOIN componentes ON  componentes.id_componente = registros.id_componente)
INNER JOIN imagens ON  imagens.id_imagem = registros.id_imagem)
INNER JOIN modulo ON modulo.id_modulo = registros.id_modulo)

And I also think that on line 4 you just forgot to put imagens.id_imagem

    
15.02.2018 / 17:46
Your DKIM signature is not valid. I can not receive emails from my server! phpmyadmin icons do not work