Insert with select from two different places

Asked

Viewed 131 times

0

My code when registering a new user: 

$sql1        = "INSERT INTO usuario (login, senha_hash, nome, email, direito_deAcesso)
                   VALUES ('{$usuario}', '{$senha_hash}', '{$nome}', '{$email}', '{$direito_deAcesso}')";
$query1      =  mysqli_query($conexao, $sql);

After a new user is registered, I have to associate him with a partner. In summary, a partner may have one or more hitched users and a user may only be hitched to a single partner.

So, after the user is registered and the query is executed, I run another:

$sql2  = "INSERT INTO parceiro_has_usuario (idUsuario, idParceiro)
          SELECT usuario.idusuario FROM usuario
          WHERE login = '{$usuario}'";

idUsuario and idParceiro are two Foreign key.

What I want to include in sql2 to include in the table parteiro_has_usuario (which contains the two fks) is the name of the partner that is in the table partner.

I tried something like that, but it didn’t work:

$sql2  = "INSERT INTO parceiro_has_usuario (idUsuario, idParceiro)
          SELECT usuario.idusuario FROM usuario
          WHERE login = '{$usuario}',
          SELECT parceiro.nome FROM parceiro WHERE nome = '{$parceiro}'";

Could you help me? By the way, I don’t know if I’m doing it the right way to add the values in the table that contains the two fk.

mysql

  • makes use of indices along with the logical operator AND

1 answer

2


Guy in the first Insert if you use the returning it will already return the code of the guy inserted. Ex:

insert into tabela(nome, etc) values('fulano', true) returning id;

assuming your primary key calls if id if returning name_da_your_key

assign this id to a variable and it seems to me you already have the name of the partner there is only to play the in values of the same Insert, I hope to have been clear, just a note keep name as fk is not a good practice that it seems that is what you are wanting to do, is not?

Now to select in the two tables you can do the following:

 INSERT INTO parceiro_has_usuario (idUsuario, idParceiro)
      SELECT usuario.idusuario, parceiro.nome  FROM usuario, parceiro
      WHERE usuario.login = '{$usuario}' and parceiro.nome = '{$parceiro}'";

Also assuming I only have a partner with that name, why else will it fail.

I hope I’ve helped.

Browser other questions tagged mysql

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