Connect 3 sql tables to each other using INSERT

Question

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();
}

Answer 1

I’ll try to clear up some things for you from SQL.

1. You did right in creating the separate tables.

2. You must place the field that has the reference of another table (Foreign key), in main table, in the case in the table "persons".

Briefly, the "Chave estrangeira" or "Foreign Key" is for you to relate your tables, they will "talk" to each other, they help you not insert incorrect data in your database, helps in a precise future case analysis, and also leaves more structured/elaborate.

In practice, its SQL in the right way:

CREATE TABLE profissao (
  id_profissao int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
  nome_profissao varchar(45) NOT NULL
);

CREATE TABLE universidade (
  id_universidade int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
  nome_universidade varchar(45) NOT NULL
);

-- Por ultímo a tabela principal, para não gerar conflitos na hora da criação.
-- Pois os campos "fk" estão se referindo a campo de outra tabela.  

CREATE TABLE pessoa (
  id_pessoa int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
  nome varchar(45) NOT NULL,
  sobrenome varchar(45) NOT NULL,
  telefone varchar(45) NOT NULL,

  -- Criando chaves estrangeiras
  fk_profissao INT(5),
  foreign key (fk_profissao ) references profissao(id_profissao),

  fk_universidade INT(5),
  foreign key (fk_universidade ) references universidade(id_universidade)
);

After the creation of tables, you do the insertion of data, as you did your.

They must have some ID that you have created, in the table "profissao" and "univerisidade", otherwise the database itself will show the error as they are referenced.

3. After the inserts, you can perform queries using JOINS.

SELECT A.id_pessoa, A.nome, A.sobrenome, A.telefone, B.nome_profissao, C.nome_universidade
FROM pessoa A 
JOIN profissao B ON A.fk_profissao = B.id_profissao
JOIN universidade C ON A.fk_universidade = C.id_universidade;

In case you have any questions, you can ask, there’s nothing wrong with that, I hope it helped.

UPDATE: Now, answering your code questions.

// how would I enter the name of the university from the Profession table to Person table ?

// as I would to enter the university name from University table to Person table ?

R: Simple and easy, you will not enter the name of the university or the name of the profession, you will enter the ID of them, with the ID except on the table, you can bring her name, you understand?

First, you must rebuild the structure of your database, as I showed above.

Answer 2

The @Samuel Verissimo answer already solves the question. You need a "link" between a table and another, and this is done using Foreign Key.

---

If you know how Foreign key works, which @Samuel Verissimo explained, then just apply it to your PHP code, basically you should use the ->lastInsertId(); as documented in https://www.php.net/manual/en/pdo.lastinsertid.php.

So apparently I’d have to do something like:

public function addPessoa($nome, $sobrenome, $telefone, $id_p, $id_u) : int {
    global $pdo;

    $sql = $pdo->prepare("INSERT INTO pessoa SET nome = :nome, sobrenome = :sobrenome, telefone = :telefone, profissao = :id_p, universidade = :id_u");
    $sql->bindValue(":nome", $nome);
    $sql->bindValue(":sobrenome", $sobrenome);
    $sql->bindValue(":telefone", $telefone);
    $sql->bindValue(":id_p", $id_p);
    $sql->bindValue(":id_u", $id_u);

    $sql->execute();

    return $sql->lastInsertId();
}


public function addProfissao($nome) : int {
    global $pdo;

    $sql = $pdo->prepare("INSERT INTO profissao SET nome = :nome");
    $sql->bindValue(":nome", $nome);
    $sql->bindValue(":id", $id);

    $sql->execute();

    return $sql->lastInsertId();
}


public function addUniversidade($nome) : int {
    global $pdo;

    $sql = $pdo->prepare("INSERT INTO universidade SET nome = :nome");
    $sql->bindValue(":nome", $nome);

    $sql->execute();

    return $sql->lastInsertId();
}

That way when calling the addProfissaoa will return the id the newly created profession. You should also check if the profession no longer exists and return possible errors, since otherwise will return id = 0. This works assuming you have an AUTO_INCREMENT.

---

I would recommend creating a Transaction using beginTransaction(); (https://www.php.net/manual/en/pdo.begintransaction.php). After all, imagine that a profession is inserted, but the user insertion will fail?! In this case it is important that you manage to give a rollback (https://www.php.net/manual/en/pdo.rollback.php), canceling all inserts made.