PHP SQL Inner Join - Show name instead of ID number

Question

PHP SQL Inner Join - Show name instead of ID number

Asked 6 years, 2 months ago

Viewed 738 times

3

I’m having a little difficulty with the INNER JOIN query. 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 but when I load my table to show the registered data, the fields "Type" and "Category" show the ID number of the selected value in the combobox instead of the selected name.

Below follows 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 in which I mentioned that shows only the ID number of the value selected in the droplist after registration:

Instead of the iddascategoriesetype_ps` intends to show what exactly?

– João Martins

2018/08/31 at 08:51
In this field you are presenting the type ID and category ID. See which fields your query returns, and then change in html instead of showing Ids, switch to the fields you want.

– Luís Almeida

2018/08/31 at 09:24
Hello John and Louis. Thank you for the return. So, what I intend is that when filling out a form and saving it later using the (Insert into), these data would be saved and shown in a table. In this case I can do this but in the table is shown the ID number of the value contained in the droplist instead of the name itself. In the field (type_ps) I have: (ID 1) -> Product and (ID 2) -> Service and in the field (categoria_fk) I have (ID 1) -> Metals, (ID 2) -> Consulting and (ID 3) -> Electronics.

– Michel

2018/08/31 at 11:01
In html I am using: <select name="typo_fk" class="form-control"> <?php foreach($Results as $Row): ? > <option value="<?= $Row['typo_id']; ? >"><?= $Row['type']; ? ></option> <? php endforeach ? > </select>

– Michel

2018/08/31 at 11:01

2 answers

4

I did not understand why so many parentheses. Since they are unnecessary, I took them.

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 the 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 must put the tabela.nomedocampo.

No select feito, em cat.nome as categoria, I’m bringing the value of the field nome table categorias.

In tip.nome as tipo, same thing, only searching from the table tipo_ps.

More details:

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

This is judicious in reporting what would use a count (count) without filter, and by taking a record relationship with the INNER JOIN, so the values would not match if they were not 100% related.

Extras:

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

Select only tuples from a table with JOIN

Thanks for the Rbz guidance. I adjusted the code and it worked :)

– Michel

2018/08/31 at 14:00

Browser other questions tagged php sql

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

by rLinhares • **7,797** points · Answer 1 · 2018-08-31T10:58:50+00:00

You just need to change the return field of your query; the junction between the tables seems to be being done in the correct way, so just return the description of guy and category instead of id:

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 which field name in the tables tipo_ps and categorias which contains the description of each (in the query, referred to as descricao)