PHP SQL Inner Join - Show name instead of ID number

Question

PHP SQL Inner Join - Show name instead of ID number

Navigation

#1 by (3 votes)
#2 by (2 votes)

2

I'm having a little difficulty regarding query INNER JOIN. I have a table called users and it contains the following fields:

First Name
Last Name
Tipo_fk (Foreing Key)
Categoria_fk (Foreing Key)

As shown above, the HTML select function works fine, however when I load my table to show the data, the "Type" and "Category" fields show the ID number of the selected value in the combobox instead of the selected name. / p>

Below is the query (INNER JOIN) I am using to try to display the name instead of the ID number of the selected item.

SELECT users.*, tipo_ps.tipo_id , categorias.categoria_id
FROM ((users
INNER JOIN categorias ON users.categoria_fk = categorias.categoria_id)
INNER JOIN tipo_ps ON users.tipo_fk = tipo_ps.tipo_id)

And an excerpt from the table I mentioned that shows only the ID number of the selected value in the droplist after being registered:

php sql

asked by anonymous 31.08.2018 / 05:19

2 answers

2

You just need to change the return field of your query; the join between the tables seems to be being done correctly, then it would be enough to return the description of type and category instead of id : p>

SELECT users.*, tipo_ps.descricao , categorias.descricao
FROM ((users
INNER JOIN categorias ON users.categoria_fk = categorias.categoria_id)
INNER JOIN tipo_ps ON users.tipo_fk = tipo_ps.tipo_id)

detail : just note the name of the field in the tipo_ps and categorias tables that contains the description of each one (in the query, referenced as descricao )

31.08.2018 / 12:58

String search with "|" character Problem with autocomplete

score 3 · Accepted Answer

I do not understand why so many parentheses. As they are unnecessary, I took them out.

Solution:

SELECT u.*, cat.nome as categoria, tip.nome as tipo
FROM users u
INNER JOIN categorias cat ON cat.categoria_id = u.categoria_fk
INNER JOIN tipo_ps tip ON tipo_ps.tipo_id = u.tipo_fk

Explaining:

When you do JOIN you are linking the tables, where you will have access to the records and all related fields.

To filter the fields you want, you should put tabela.nomedocampo .

No select done, in cat.nome as categoria , I'm bringing the value of field nome of table categorias .

In tip.nome as tipo , same thing, only looking for table tipo_ps .

More details:

It is important to know the relationship between your tables, because using INNER JOIN , you only get the values contained in both tables, that is, if there is a record in the main table without the FK field filled, no longer will log, and vice versa.

This is judicious in reports that you would use a non-filtered count ( count ), and taking a record relationship with INNER JOIN , so values would not beat if they were not 100% related.

Extras:

A brief explanation of Inner, Left, Right, Outer / Full and Cross Join

Select only tuples from a table with JOIN