INSERT in two different tables - Mysql

Asked

Viewed 4,412 times

4

I have two tables in Mysql:

  • Alunos: id, nome, idade, email, senha.

  • login: id, email, senha.

When I do Insert to record the information in the table alunos I can do another insert to save the email and password in login?

php mysql

2 answers

5

Yes, just create a STORED PROCEDURE to insert the two tables;

The code below creates the PROCEDURE:

DELIMITER $$ 

    CREATE PROCEDURE Insere(IN aNome VARCHAR(30), IN aIdade INT, IN aEmail VARCHAR(30), IN aSenha VARCHAR(30)) 
    BEGIN 
        INSERT INTO Alunos ( nome, idade, email, senha) VALUES (aNome, aIdade, aEmail, aSenha);
        INSERT INTO Login ( email, senha) VALUES (aEmail, aSenha);
    END $$ 
DELIMITER ;

After that just run PROCEDURE with the command CALL Insere

Ex.:

CALL Insere ('Fulano', 22, '[email protected]', 'senha');

After that the user will be inserted in the two tables.

  • I need a foreign key on the tables?

  • You don’t have to, but it’s good to have it to maintain your comic book consistency. The precedent I posted using your bank model, but it would be good for you to change to add the foreign key from the Student Login table.

  • All right, I’ll try it here. I thought it was just one do two INSERT commands in a row and it was good xD

  • You can just run the two INSERT commands smoothly. But the use of PROCEDURE decreases the chances of any problem occurring between the two commands, and the BD becomes inconsistent. But the ideal option is to use TRANSACTION.

  • Can I apply this code directly in PHP? Why do I receive information from a form

  • The code for the creation of PROCEDURE you will run only once, can be through a specific PHP script, or through the client you are using, Phpmyadmin, Mysqladmin etc ... In the PHP registration script, you will only make the call, ie the part of the CALL Insere ('Fulano', 22, '[email protected]', 'senha');

Show 1 more comment

2


I probably didn’t make it so clear, but I solved it this way:

I receive data from a form:

$nome = $_POST['nome'];

$idade = $_POST['idade'];

$email = $_POST['email'];

$senha = $_POST['senha'];

$sql1 = "INSERT INTO aluno (nome, idade, email, senha) values ('$nome', '$idade', '$email', '$senha')";

$sql2 = "INSERT INTO login (email, senha) values ('$email', '$senha')";

mysqli_query($conexao, sql1) or die ("Erro"); 

mysqli_query($conexao, sql2) or die ("Erro");

echo 'Gravado com sucesso';

So he recorded in both tables the data received from the form. :)

  • Just a suggestion, change the die("erro"); for die(mysqli_error($conexao)); so you know what happened, makes it easier to figure out how to solve it.

  • Thanks @rray. I made the change here.

Browser other questions tagged php mysql

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