Replace id by name in table

Question

Replace id by name in table

Navigation

#1 by (0 votes)

0

So I have this table with information like: id, student, computer, date of registration, time of entry, time of departure and reason, but in student and computer I want to show the name / description and not the id . Here's my code.

Student and computer are from another table

public function buscaTodos() {
    $sql = "SELECT * FROM " . $this->tabela;
    $sqle = $this->con->query($sql);

    $dados = $sqle->fetchAll(PDO::FETCH_ASSOC);
    return $dados;
}
$registro = new Registro();
$dados = $registro->buscaTodos();


<table class='table][1]][1] table-bordered table-striped'>
        <tr>
            <th>ID</th>
            <th>Aluno</th>
            <th>Computador</th>
            <th>Data</th>
            <th>Hora de Entrada</th>
            <th>Hora de Saída</th>
            <th>Motivo</th>
            <th>Opções</th>
        </tr>

    <?php

    foreach($dados as $r){

        $id=$r['id'];
        $aluno=$r['id_aluno'];
        $computador=$r['id_computador'];
        $dt_registro = $r['dt_registro'];
        $hr_entrada = $r['hr_entrada'];
        $hr_saida = $r['hr_saida'];
        $motivo = $r['motivo'];



    ?>
        <tr>
            <td><?=$id?></td>
            <td><?=$aluno?></td>
            <td><?=$computador?></td>
            <td><?=$dt_registro?></td>
            <td><?=$hr_entrada?></td>
            <td><?=$hr_saida?></td>
            <td><?=$motivo?></td>
        </tr>

    <?php } ?>
    </table>

php

asked by anonymous 02.05.2017 / 21:31

1 answer

Simulate click on a compiled template SQL Report Builder 3.0 Merge Rows into a Totalizer column

score 0 · Answer 1

Hello, well, you want to do a rather complex search in your database, using the student and computer id to refer to their respective tables, taking their name.

For this you should do a query a bit complex too, using join. and on. I do not know how your database is modeled, but I have a query similar to what you want, which can serve as the basis for creating your database. follow the code:

function buscaProjetoPorUsuario($id_usuario){
        $select = $this->conexao->prepare("SELECT p.id, p.nome, p.descricao, p.dono, p.id_usuario,p.data, p.porc_marketing, p.porc_site, p.porc_desing, u.nome FROM projetos P join usuarios U on (P.id_usuario = U.id) WHERE U.id = '$id_usuario'");
        $select->setFetchMode(PDO::FETCH_ASSOC);
        $select->execute();
        $projetos = $select->fetchAll();
        return $projetos;
    }

Now I'll explain the query for you, one step at a time.

The purpose of this query is to fetch information from a project, and fetch the name of a user using user_id, to refer to the users table.

First I define everything I want to search using:

SELECT p.id, p.nome, p.descricao... and u.nome

Then I give FROM projetos P, usuarios U on (P.id_usuario = U.id)

Here I am defending that when I use the letter P, in p.id, p.descricao... I am referring to the Projects table, and when I use the letter U I am referring to the Users table, then I say on (P.id_usuario = U.id) , this causes the query to refer to the Users table, using the user_id field, to relate to the id field of the User table.

I hope you understand, if you need help, share the structure of your tables in the database, so we can help you build a query.