Do insert in two tables at the same time

3

I need to insert into two tables at the same time. I have a user registry and I need the id of a user to be registered in two tables at the same time, thus making an association of this user with another table, which contains a group of users.

    
asked by anonymous 11.01.2016 / 17:24

2 answers

5

Can not multiply inserts by statement , but within transaction yes:

BEGIN TRANSACTION
   INSERT INTO Tabela1 (Coluna1, ...) VALUES (....);
   INSERT INTO Tabela2 VALUES (..., LAST_INSERT_ID());
COMMIT

More about LAST_INSERT_ID ()

In the example above, everything is done by MySql. The LAST_INSERT_ID() will get the value of the column that is autoincrement in the first insert. If you want to use PHP, you can do:

Insert into ....

By PHP, you can get the last value entered:

$ultimo_valor_inserido = mysql_insert_id();

With a little detail:

  

If your AUTO_INCREMENT column is a column of type BIGINT, the value returned by mysql_insert_id () is incorrect. Instead, use the MySQL internal SQL function LAST_INSERT_ID () in an SQL query.

For more information, see

    
11.01.2016 / 17:31
-1

I was able to insert into two tables doing so: I made the first inclusion using an IF, hence I checked if it was done with an IF, if so, then a new script is written inside the IF that makes a new insert. So:

   public function addUsuario($usuarios_nome, $usuarios_email, $usuarios_senha, $usuarios_permissoes, $usuarios_imagem = null){     

    if ($this->existeEmail($usuarios_email) == false) {
                    $sql = "INSERT INTO usuarios (usuarios_nome, usuarios_email, usuarios_senha, usuarios_permissoes, usuarios_imagem) VALUES (?, ?, ?, ?, ?)";
                    $sql = Conexao::getInstance()->prepare($sql);
                    $sql -> bindValue(1, $usuarios_nome);
                    $sql -> bindValue(2, $usuarios_email);
                    $sql -> bindValue(3, $usuarios_senha);
                    $sql -> bindValue(4, $usuarios_permissoes);
                    $sql -> bindValue(5, $usuarios_imagem);                                  
                    //$sql -> execute();
                            if($sql->execute()){
                                    $sql = "INSERT INTO colaboradores (colaboradores_usuarios_id, colaboradores_status) VALUES (LAST_INSERT_ID(), 1)";
                                    $sql = Conexao::getInstance()->prepare($sql);
                                    $sql -> execute();                                                                  
                            };
                                          return true;

    }else{
        return false;
    }           

   }    
    
03.01.2019 / 00:34