How to display the result of a query on an PHP html page?

Question

How can I display the results of a query in a table of an html page?

Below is what I was able to do. However, the code only shows the first record.

include("conectar.php");
$sql = mysql_query("Select * From tb_trabalhador and tb_detalhe_trabalhador");
$exibe = mysql_fetch_assoc($sql);
echo "<table>"; 
echo  "<tr><td>Nome:</td>";
echo "<td>".$exibe["Nome"]."</td></tr>"; e os outros campos

Answer 1

When more than one result is returned by your query, use a while to get them all.

$sql = mysql_query("Select * From tb_trabalhador and tb_detalhe_trabalhador");
while($exibe = mysql_fetch_assoc($sql)){
  echo $exibe['nome'] .'<br>';
}

I recommend using the mysql_error() to debug your code and get an error message from the database, if this is a legacy project.

mysql_query($sql) or die(mysql_error());

It is highly recommended to use the PDO or mysqli to make connection to database, because the functions mysql_* have already been discontinued and will soon be removed.

Reasons for not using mysql functions_*

Answer 2

To get all the records you need a repeat loop:

include("conectar.php");
$sql = mysql_query("Select * From tb_trabalhador and tb_detalhe_trabalhador");
echo "<table>";
while($exibe = mysql_fetch_assoc($sql)){
   echo "<tr><td>Nome:</td>";
   echo "<td>".$exibe["Nome"]."</td></tr>";
}
echo "</table>";

Answer 3

All functions mysql_fetch_* return a single line and advance the internal cursor to the next record.

To get all records, you need to use them within some repeating structure.

Example:

while ($exibe = mysql_fetch_assoc($sql) ) { // Obtém os dados da linha atual e avança para o próximo registro
  echo $exibe["nome"];
}

As mentioned, consider using a PDO extension to manage communication between application <-> database, as these low-level functions will be removed in the future.

Answer 4

$connection = new mysqli("localhost", "user", "pass", "database");
$sql = $connection->query("Select * From tb_trabalhador and tb_detalhe_trabalhador");

if($sql){ // If $sql is True
    while($exibe = $sql->fetch_assoc()){
        foreach($exibe as $key => $value){
            echo "<br />" . $value;
        }

    }
}

This form exempts the use of these html tags in its code. The foreach displays all the information, without specifying what should be displayed, simplifying its code, e.g. $displays["Name"].

Answer 5

It is important to note that queries should not be executed in a view. The correct is that you apply a MVC Pattern( Model View Controller) and separate things. A good example, using the Code Igniter Framework, would be:

model.php
function carregar_trabalhador(){
$banco = new mysqli("servidor","usuário","senha");
$sql="Select * From tb_trabalhador and tb_detalhe_trabalhador";
$resultadoConsulta=$banco->query($sql);
$trabalhadores = array();
$i=0;
        while (!empty($resultadoConsulta[$i])) {
            $row = $resultadoConsulta[$i]=
            $trabalhadores[] = $row;
            $i++;
        }

return $resultadoConsulta
}

controller.php 
function exibir_trabalhador(){
$model= new Model();
$trabalhadores = $model->carregar_trabalhador(); 
$this->load->view("trabalhador.html", $trabalhadores);  
}

trabalhador.html
<?php
foreach ($trabalhadores as $trabalhador) {
?>
<table> 
    <tr><td>Nome:</td> 
        <td><?= $trabalhador["Nome"] ?></td></tr>; e os outros campos
    <?php }
?>