Insert with PDO and INNER JOIN

Asked

Viewed 516 times

0

I have a table person who owns the fields name, city and phone and time_id. I also have another table with a team name and in it I have a field called meu_time, as I can do an INNER JOIN to register the team in this table time but in the table person in the field time_id 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

  • Just a question because it is not clear in the question. You are wanting to use INNER JOIN in a command INSERT?

  • @Pagotti, I believe it is an Insert in two tables

  • axo que ele quer buscar um informação em uma tabela e usar ela... but I think he’ll have to make a select

  • Exactly, I’ll try to explain it better. I have a form and for it register in the bank the name of the person, city, phone and his team, but I did not want to leave the team in the same table of the person, this is because other people can have the same team, then it would be more organized if the tables were separate

  • You can put the structure of the two tables in the question to see if you can use the LAST_INSERT_ID?

  • The team I type to register, but in case it already exists I did not want to create another registration with the same team name, in case there is I just wanted to use what is already there

  • The table is called main is called user, then inside it I have the table person and the table time

  • the team is already registered?

  • The team I will register together when registering the user, but I did not want to register the team in the same person field

  • is that it gets more organized separately and I’ll have greater control over it

  • Updates your question by placing these details and the structure of the tables involved.

Show 6 more comments

2 answers

3


Case time_id already exists, you make a 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 it will be two Inserts and not to do two Inserts at the same time in a single statement, so we will 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

Reference on TRANSACTION

Reference on LAST_INSERT_ID()

Reference on INSERT WITH SELECT

  • 1

    Just one correction. Use BEGIN or else START TRANSACTION. https://www.db-fiddle.com/f/9JgDy59Bbu19TtcYgzja4j/0

  • @Pagotti, thank you for the correction

  • 1

    It worked, I love you guys hehe

0

Can you give me the last help? I decided to put another table, this time called selecto, 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;";

Browser other questions tagged php mysql pdo

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