Mysql Query with multiple tables

Question

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

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

the table records only receive ID’s from the other tables , it unifies the records in fact,

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 tried to do so but 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 don’t know if this is possible or if this was the best way to do it.

Answer 1

Well I don’t know exactly how your tables are, but I would change the query to the following, just by putting () and a DISTINCT for 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

Answer 2

It does so, in the main table you have the user id, in the other tables you make a field with the name idcli, at the time of Insert, this idcli gets 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 and so forth