Search for results in related tables

Question

I have the following question, I have a ticket system to create calls and I need to do a search to bring the open calls sorted by priority order, so far so good, I need now that the field "$Row->created_by" search the name of the requester that is in another table, currently it brings the ID, I have to use Inner Join to do this?

Could someone explain to me the best way to do this?

Thanks in advance.

  // Busca dados no banco
                    if ($result = $mysqli->query("SELECT chamadosti.*,users.first_name FROM chamadosti INNER JOIN users ON users.id = chamadosti.created_by WHERE status='open' ORDER BY priority DESC "))
                    {
                            // exibir os registros se há registros para mostrar
                            if ($result->num_rows > 0)
                            {
                                    // exibir registros em uma tabela
                                    echo "<table border='1' cellpadding='5' cellspacing=0 style=border-collapse: collapse bordercolor='#4B5AAB'>";


                                    // definir cabeçalhos de tabela
                                   echo "<tr> <th>N° do Chamado</th> <th>Prioridade</th> <th>Descrição</th> <th>Inicio</th> <th>Criado por:</th> <th>Assumiu:</th> <th>Categoria</th> <th>Sub chamado</th> <th>Editar</th> <th>Deletar</th></tr>";

                                    while ($row = $result->fetch_object())
                                    {
                                            // cria uma linha para cada registro
                                            echo "<tr>";
                                            echo "<td>" . utf8_encode ($row->id) . "</td>";
                                            echo "<td>" . utf8_encode ($row->priority) . "</td>";
                                            echo "<td>" . utf8_encode ($row->summary) . "</td>";
                                            echo "<td>" . utf8_encode ($row->created_at) . "</td>";
                                            echo "<td>" . utf8_encode ($row->created_by) . "</td>";
                                            echo "<td>" . utf8_encode ($row->assigned_to) . "</td>";
                                            echo "<td>" . utf8_encode ($row->category) . "</td>";
                                            echo "<td>" . utf8_encode ($row->parent_id) . "</td>";
                                            echo "<td><a href='edit_cam.php?id=" . $row->id . "'>Editar</a></td>";
                                            echo "<td><a href='del_cam.php?id=" . $row->id . "' onclick=\"return confirm('Tem certeza que deseja deletar esse registro?');\">Deletar</a></td>";
                                            echo "</tr>";
                                    }

                                    echo "</table>";
                            }
                            // se não há registros no banco de dados, exibir uma mensagem de alerta
                            else
                            {
                                    echo "Nenhum registro encontrado!";
                            }
                    }
                    // mostrar um erro se houver um problema com a consulta de banco de dados
                    else
                    {
                            echo "Error: " . $mysqli->error;
                    }

                    // fecha con com banco
                    $mysqli->close();

            ?>

Answer 1

Hello,

Try to use this code:

SELECT * FROM tabela INNER JOIN tabela_usuario ON tabela_usuario.id = tabela.created_by 
WHERE tabela.status='open'  
ORDER BY tabela.priority DESC

Answer 2

Thiago, you can use the following SQL:

SELECT cha.*, u.first_name FROM chamadosti as cha INNER JOIN users as u ON u.id = cha.created_by WHERE cha.status='open' ORDER BY cha.priority DESC

It will bring all the information you already use, plus the first_name of your user table.

Now in your file, you can use the following code where you want to display the resulting sql name:

utf8_encode ($row->first_name)

---

EDIT

Given the new request, Thiago reported that the calls now have a responsible, which is also linked to table users across the countryside assigned_to table chamadosti, making the necessary changes the SQL would be as follows:

SELECT cha.*, u_created.first_name as criador, u_assigned.first_name as responsavel FROM chamadosti as cha 
LEFT JOIN users as u_created ON u_created.id = cha.created_by 
LEFT JOIN users as u_assigned ON u_assigned.id = cha.assigned_to  
WHERE cha.status='open' ORDER BY cha.priority DESC

and now to show the creator and responsible you can use the following code:

utf8_encode ($row->criador) // para mostrar o criador (vide: alias SQL)
utf8_encode ($row->responsavel) // para mostrar quem assumiu o chamado (vide: alias SQL)