Insert with select from two different places

Question

Insert with select from two different places

Navigation

#1 by (2 votes)

0

My code when registering for 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 it with a partner. In short, a partner can have one or more users hooked up and a user can only be tied to a single partner.

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

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

id and IdParceiro are two foreign key

What I want to include in sql2 to include in the partner_has_user table (which contains both fks) is the partner name that is in the partner table.

I tried something like this, but it did not 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'm not sure if I'm doing the right thing to add the values in the table that contains the two fk.

mysql

asked by anonymous 13.05.2016 / 23:23

1 answer

Receiving Data and Converting to String Fluent API zero-to-one Relationship 0-1 - bringing results from another relationship

score 2 · Accepted Answer

Face in the first insert if you use the returning it will already return the inserted face code. Ex:

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

Assuming your primary key calls id if returning your key_name

Assign this id to a variable and from what I think you already have the name of the partner, just play them in the values of the same insert, I hope it was clear, just a note save name as fk is not a good practice Apparently that's what you're trying to do, is not it?

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 that I only have one partner with that name, why else will it fail.

I hope I have helped.