1
Hello, before I try to work on PHP more deeply, I am studying the logic of mysql better, with select, delete,Insert etc. But when studying the sql database over time, I realized that in practice it is not correct to insert all the elements in a single table, since it causes a mess in logic. And so I’m at this impasse trying to understand this logic.
Example, I have 3 tables... One is "Pessoa", "Profissao" and "Universidade", which instead of placing the profession and university within the Pessoa table itself, I decided to separate them p/ try to understand how they work through the interconnection between the 3 tables. Being that I could connect the profession table and university to Pessoa, problem that I’m not getting, and I don’t want to be doing tricks. How would I insert an element from one table into another ? I am placing a link of the p/you files try to understand according to the form I made, separately...
[LINK TO THE ARCHIVES] [1]: https://drive.google.com/drive/folders/1HxMz4WRrPfAa-X153rxZOOQntpgGk2C4?usp=sharing
Demo: (Or follow, the files I left on the link, in order to understand better, is simple to understand)
public function addPessoa($nome, $sobrenome, $telefone) {
global $pdo;
$sql = $pdo->prepare("INSERT INTO pessoa SET nome = :nome, sobrenome = :sobrenome, telefone = :telefone");
$sql->bindValue(":nome", $nome);
$sql->bindValue(":sobrenome", $sobrenome);
$sql->bindValue(":telefone", $telefone);
$sql->execute();
}
public function addProfissao($nome) {// como eu faria para inserir o nome da universidade da tabela Profissão para tabela Pessoa ?
global $pdo;
$sql = $pdo->prepare("INSERT INTO profissao SET nome = :nome");
$sql->bindValue(":nome", $nome);
$sql->execute();
}
public function addUniversidade($nome) { // como eu faria para inserir o nome da universidade da tabela Universidade para tabela Pessoa ?
global $pdo;
$sql = $pdo->prepare("INSERT INTO universidade SET nome = :nome");
$sql->bindValue(":nome", $nome);
$sql->execute();
}
If you don’t understand, ask the questions here, thank you.
– Samuel Verissimo
Samuel, first thank you for the objective explanation with mysql with the foreign key. But how would it fit with my PHP logic that I left, since there is now a foreign key... In the part "public Function addProfissao() and "Function addUniversidade" for "addPessoa()" ?
– Israel Tavares
Dude, your php code won’t change, you just need to add the field "fk_professional" and "fk_university" in your
INSERT INTO pessoa
, simple... reinforcing once again, that the value of these two fields that we created, must be of typeINT
, and he must be theID
of the other two tables...– Samuel Verissimo
You can keep the INSERT profession and university, just need to change the INSERT of the person table... Example
INSERT INTO pessoa SET nome = 'Kleber', sobrenome = 'Souza', telefone = '40028922', fk_profissao = '1', fk_universidade = '2';
– Samuel Verissimo
Only that there is one thing, the php pages I created with the Formularios, each one has only the fields of index.php(PERSON), profession.php(PROFESSIONAL) and university.php(UNIVERSITY), ie separate pages from each other. Not a form on a page only no, prints here: https://drive.google.com/drive/folders/1Ii28eDf_7qCVO5X39EcBuqME8rfeBxoc?usp=sharing
– Israel Tavares
In this case, you can add the 2 fields in the person form... Can create 2 selects by pulling straight from the table "professional" and "university", would be what you need?
– Samuel Verissimo