Selection of SQL tables

Question

E ae Personal, blz? People, I have a little problem, I have 3 SQL tables, a so-called students, another so-called collaborator and another of posts

On the table students we have:

id
nome;
img;
email;
pass;
end;
status
matricula
dash
...

On the table collaborativer we have:

id
nome
img
email
pass
end
status
codigo

And on the table posting we have:

id
id_user (id do usuário q postou a publicação)
publicacao
img
date

My problem is this, I have a connection to table posting:

<?php
    $sqlPublic = "SELECT * FROM postagem ORDER BY id DESC";
    $resultPublic = mysqli_query($conn, $sqlPublic);
?>

And I have a While to present all publications in this way:

<?php while ($public = mysqli_fetch_array($resultPublic)) { ?>
    <div class="publicacao">
        div class="headerPost">
    <?php
        $id_Post = $public['id']; //A partir daqui pega os dados da publicação do $public que está sendo feita no while com array;
        $id_user = $public['id_user'];
        $postagem = $public['postagem'];
        $datePost = $public['datePost'];
        $sqlPostUser = "SELECT * FROM alunos WHERE id = '$id_user'"; // Aqui eu faço a conexão com a tabela alunos com sql_assoc para associar cada publicação com o usuário que à postou com referência no ID do usuário, com o id cadastrado na publicação.
        $queryPostUser = mysqli_query($conn, $sqlPostUser);
        $linhaAssoc = mysqli_fetch_assoc($queryPostUser);
        $dasheres = $linhaAssoc['dash'];
    ?>
    <div class="arPer">
        <div class="perfilPost">
            <div class="perfilFoto">
                <img src="../arquivs/perfil/<?php echo $linhaAssoc['img']; ?>" />
            </div>
            <a href=""><h1><?php
                $linhaNameAssoc = $linhaAssoc['name_user'];
                $linhaNameAssoc = explode(" ", $linhaNameAssoc);
                echo $linhaNameAssoc[0]." ".$linhaNameAssoc[1];
            ?></h1></a>
        </div>
    </div>
</div>
O restante do código é apenas a estrutura normal...

Going on... The problem is this, I can list with while all publications, so far beauty, only that, as you can see only reference with the table students, and also need to do with the table collaborator, why if a contributor posts in the form it is, will not appear his data, and will give error in the display, I tried to make several ways the selection of the two tables (students & collaborator), but I couldn’t, I tried using UNION but I couldn’t, I tried with JOIN, but for this case it does not serve, I had almost the same problem with the listing of users, but I managed to solve, this is still a little more complicated, please, who can help me reply there, wanted to select the two tables to be able to display the posts correctly... I’ll try something that comes to mind now, but just really test

I AM SORRY FOR THE SIZE OF THIS.

Answer 1

(sorry my Portuguese I am not native)

It’s actually normal to do this kind of thing kkk, but you can do the same with a singular query:

$sqlPublic = SELECT * FROM postagem AS posta LEFT OUTER JOIN alunos AS alu ON alu.id = posta.id_user LEFT OUTER JOIN colaborador AS cola.id ON cola.id = posta.id_user ORDER BY posta.id DESC;

$resultPublic = mysqli_query($conn, $sqlPublic);

In order to select a field, you can use the table prefix with the field name. For example: __$public['alu.nome']__ is the student’s name and __$public['cola.nome']__ is the name of the collaborator.

So when you do the while, the problem will be that if the posting was made with a collaborator, then the student data will be NULL and vice versa. So you first have to select which field of which table you’re going to use, but this can be solved by using the ternary operator as follows to allocate the username:

$nome_user = (!is_null($public['alu.nome'])) ? $public['alu.nome'] : $public['cola.nome'];

I suppose, that a posting has as creator a collaborator or a student, then if one is NULL you take the value of the other. And so you can go doing with other fields of collaborator and student.

I hope you can understand what I said.

Answer 2

I managed to do a super gambiarra kkkkk

It’s nothing professional. But that’s what gave... In the future I intend to improve this for sure.

In the selection part of the table students to give an Assoc, I did the following, I did two different query, one for each table, as shown below:

<?php
$id_Post = $public['id'];
    $id_user = $public['id_user'];
    $postagem = $public['postagem'];
    $datePost = $public['datePost'];
    $assocIf = "SELECT * FROM alunos WHERE id = '$id_user'";
    $associf = "SELECT * FROM colaborador WHERE id = '$id_user'";
    $queryAss = mysqli_query($conn, $assocIf);
    $queryass = mysqli_query($conn, $associf);
    $alunos = mysqli_fetch_assoc($queryAss);
    $colaborador = mysqli_fetch_assoc($queryass);
?>

And the "GAMBIARRA" that I did was the following, I took and did just one if|Else if, as shown below:

if($alunos['id'] === $id_user){
    $sqlAlunos = mysqli_query($conn, "SELECT * FROM alunos WHERE id = '$id_user'");
    $linhaAssoc = mysqli_fetch_assoc($sqlAlunos);
}else if($colaborador['id'] === $id_user){
    $sqlAlunos = mysqli_query($conn, "SELECT * FROM colaborador WHERE id = '$id_user'");
    $linhaAssoc = mysqli_fetch_assoc($sqlAlunos);
}

It worked, but like I said, I don’t think it’s professional.