Error while trying to execute Insert PDO function - PHP

Question

I’m trying to create a PDO function to perform two inserts, one after the other, but I’m having difficulties.

The function code is:

public function double_cad(array $dados){
    $pdo = parent::getDB();
    $double_cad = $pdo->prepare("insert into login(nome, login, senha, role)values(?,?,?,?)");
    $double_cad->bindValue(1, $dados[0]);
    $double_cad->bindValue(2, $dados[1]);
    $double_cad->bindValue(3, $dados[2]);
    $double_cad->bindValue(4, $dados[3]);
    $double_cad->execute();

    $id_usr = $pdo->lastInsertId();

    $double_cad = $pdo->prepare("insert into transacoes(codigo_transacao, referencia, status_transacao, ".$id_usr. ", id_servico, tipo_pagamento, data_transacao)values(?,?,?,?,?,?,?)");
    $double_cad->bindValue(1, $dados[0]);
    $double_cad->bindValue(2, $dados[1]);
    $double_cad->bindValue(3, $dados[2]);
    $double_cad->bindValue(4, $dados[3]);
    $double_cad->bindValue(5, $dados[4]);
    $double_cad->bindValue(6, $dados[5]);
    $double_cad->bindValue(7, $dados[6]);
    $double_cad->execute();

}

What I am trying to do is insert the user data in the table "login", recover its id and then enter in the table "transactions" the transaction data of that user (the line "$id_usr = $Pdo->lastInsertId();" is precisely to recover user id to insert as foreign key in transaction table).

However, when performing this function, it displays the error:

Fatal error: Uncaught Exception 'Pdoexception' with message 'SQLSTATE[42000]: Syntax error or access Violation: 1064 You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near '1, id_servico, tipo_pagamento, data_transacao)values('username','user email' at line 1' in C: wamp www xxxx lib class Transacao.php on line 44

Line 44 is just the second run of the function. How to solve?

Answer 1

The syntax of Insert is:

INSERT INTO TABELA (CAMPO1, CAMPO2, CAMPO3) VALUES (VALOR_CAMPO1, VALOR_CAMPO2...)

But in this row you use the id that returned from the last query to list the columns:

insert into transacoes(codigo_transacao, referencia, status_transacao, ".$id_usr. ", id_servico, tipo_pagamento, data_transacao)values(?,?,?,?,?,?,?)");

Generating an Insert in column "1" for example so if you do not have a column with name 1 it will not insert.