Insert data from one table into another by comparing ID

Question

In my system I have two tables, one with user registrations, obviously, each one has its ID.

And another table where are placed the data sending gifts from one user to another, but only appear the Ids of who sends and who receives.

I added two more columns in the gift table that would be for the names and username of the users who received the gift, so I knew who received by name and not by ID.

My idea would be to update/insert the names and usernames by comparing the ID of who received with the ID of the user table.

Just for the record, I bought the system ready and wanted to implement, I tried to replace anyway so that the names were already for table, but nothing done.

Answer 1

Your concept is wrong, you do not need to duplicate data in your database. For you to recover the name of the user who gave the gift is just to make a INNER JOIN, for example:

SELECT a.id, userDoador.nome AS nome_doador, userReceptor AS nome_receptor FROM Presentes a
INNER JOIN Usuarios userDoador ON a.id_user_doador = userDoador.id
INNER JOIN Usuarios userReceptor ON a.id_user_receptor = userReceptor.id;

This way you don’t need the additional fields in the table Presentes, break, once you update the user name, will automatically affect the result of this SELECT.

See more about INNER JOIN