Selecting SQL tables

Question

Selecting SQL tables

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

What about people, blz? I have 3 SQL Tables, one student call, another call collaborator and another one of posts

In the students table we have:

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

In the collaborated table we have:

id
nome
img
email
pass
end
status
codigo

And in the post table 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 the posting table :

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

And I have While to display all posts like this:

<?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...

The problem is this: I can list with while all the publications, so far, beauty, only, as you can see, it only has reference to the student table, and I also need to do with the contributor table , so if a contributor posts the form as it is, their data will not appear, and it will give error in the display, I tried to make the selection of the two tables ( students & collaborator ), but could not, tried using UNION but could not, tried with JOIN , but in that case it does not serve , I had almost the same problem with the list of users, but I managed to solve it, this is still a bit more complicated, please, who can help me answer there, I wanted to make the selection of the two tables to be able to correctly display the publications. I'm going to try something that comes to mind now, but just test it out.

I'm sorry for the size of this, it was to be clear what's happening.

php

asked by anonymous 22.07.2018 / 21:43

2 answers

MySQL printing multiple times How to merge sublists of a class and return the result using Linq and C #

score 2 · Answer 1

(sorry, my Portuguese is not native)

Actually it's normal to do this kind of kkk thing, 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);

To be able to select some field, you use the table prefix with the field name. For example: __$public['alu.nome']__ is the name of the student and __$public['cola.nome']__ is the name of the collaborator.

So when you do while , the problem will be that if the post was done with a collaborator, then the student data will be NULL and vice versa. So you first have to select which field from which table to use, but this can be resolved 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 post is created by a collaborator or a student, so 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 understand what I said.

score 0 · Answer 2

I could do a super gambiarra kkkkkkk

It's not professional at all. But that's what it was ... I intend to improve this in the future.

In the part of selecting the students table to give a assoc, I did the following, I did two different queries, 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" I made was as follows, I picked it up and just made an 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 do not think it's so professional.