Insert with PDO and INNER JOIN

Question

Insert with PDO and INNER JOIN

Navigation

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

0

I have a person table that has the name, city, and phone fields and time_id. I also have another table with a team name and in it I have a field called my_time, how can I make an INNER JOIN to register the team in that time table but in the person table in the time_id field to receive the corresponding id?

$sql = "INSERT INTO pessoa (nome, cidade, telefone) VALUES(:nome, :cidade, :telefone)";
$stmt = Parent::__construct()->prepare($sql);
$stmt->bindParam(':nome', $nome);
$stmt->bindParam(':cidade', $cidade);
$stmt->bindParam(':telefone', $telefone);
$stmt->execute()

php mysql pdo

asked by anonymous 29.06.2018 / 12:59

2 answers

0

Can you give me the last help? I decided to put another table, this time called selection, but it's giving error:

"begin;
insert into time    (meu_time) values ('cruzeiro');
$time = last_insert_id();
insert into selecao (minha_selecao) values ('brasileira');
insert into pessoa  (nome, cidade, telefone, time_id, selecao_id) values ('Otavio', 'São Paulo', 'telefone', $time, last_insert_id());
commit;";

29.06.2018 / 14:40

Problem solution error [closed] Excel - How to concatenate cell range?

score 3 · Accepted Answer

If time_id already exists, you make INSERT with SELECT :

INSERT INTO pessoa (
    nome,
    cidade,
    telefone,
    time_id
    ) 
VALUES (
    :nome,
    :cidade,
    :telefone,
    SELECT meu_time FROM tabela WHERE tabela.campo=pessoa.campo LIMIT 1
)

But if not, then there will be two inserts and not two inserts at the same time in a single statement, so let's use the TRANSACTION that makes it possible:

BEGIN
   INSERT INTO tabela_time (coluna1,coluna2,coluna3,...) VALUES (valor1,valor2,valor3,...);
   INSERT INTO pessoa (nome,cidade,telefone,time_id) VALUES (:nome,:cidade,:telefone,LAST_INSERT_ID());
COMMIT

TRANSACTION reference

LAST_INSERT_ID () reference

INSERT with SELECT