Search result in related tables

Navigation
0

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 in order of priority, until now all right, I need now the field "$ row- > created_by "look for the name of the requester that is in another table, it currently bears the ID, do I have to use Inner Join to do this?

Could someone explain 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();

            ?>
    
asked by anonymous 31.03.2015 / 16:17

2 answers

1

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 users 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 callers now have a person in charge, who is also connected to the users table by the assigned_to field of the chamadosti table, making the necessary changes to SQL would look like this: p> 

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

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)
    
31.03.2015 / 19:46
1

Hello,

Try using 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
    
31.03.2015 / 17:06
How to return only the last result of more than one field from the same table? Return and reply to sending the email in the layout