Home / Questions / 461104
Accepted
Critical Ghost
Asked: 2022-06-11 13:46:18 +0800 CST 2022-06-11 13:46:18 +0800 CST

Show all data in a PHP and MYSQL table

  • 772

What I want is to display all the records of a table (SQL)in PHPby means of a table.

This is the table:

<div class="col-md-8">
    <table class="table">
        <thead class="table-success table-striped">
            <tr>
                <th>Id</th>
                <th>Nombre</th>
                <th>Apellido</th>
                <th>Edad</th>
                <th>Fecha de nacimiento</th>
            </tr>
        </thead>
        <tbody>
            <?php
            while ($row = mysqli_fetch_array($query)) {
            ?>
                <tr>
                    <th><?php echo $row['id'] ?></th>
                    <th><?php echo $row['nombre'] ?></th>
                    <th><?php echo $row['apellido'] ?></th>
                    <th><?php echo $row['edad'] ?></th>
                    <th><?php echo $row['fecha_de_nacimiento'] ?></th>
                </tr>
            <?php
            }
            ?>
        </tbody>
    </table>
</div>

This is the code PHPwith which I call the registers

<?php 
include("funcionalidad/conexion.php");
$conn = conectar();

$sql = "SELECT *  FROM usuario";
$query = mysqli_query($conn,$sql);

$row = mysqli_fetch_array($query);
?>

the strange thing is that it works, but not as I expected since I have 2 records in the database
enter image description here

but it only shows me one in thePHP enter image description here

php

3 Answers

  1. The correct and modern way would be to store everything first and then expose the html.

    Separate as much as you can the html from the logic in php it helps to give it better maintenance and if your logic fails your php code will break and not your html, when you get to use templates you will appreciate it.

    <?php
//esto normalmente esta en una clase que maneja consultas Genericas a la base de datos:
function getTable($tabla){
    include("funcionalidad/conexion.php");
    $conn = conectar();
    $sql = "SELECT *  FROM $tabla";
    return mysqli_query($conn,$sql);
}

//esto esta en una capa/clase que se encarga de tomar datos y acomodarlos
function iterateDataToTable($data){
    $lines='';
    while($row = mysqli_fetch_array($data)) {
        $lines.='<tr>
                <td>'.$row['id'].'</td>
                <td>'.$row['nombre'].'</td>
                <td>'.$row['apellido'].'</td>
                <td>'.$row['edad'].'</td>
                <td>'.$row['fecha_de_nacimiento'].'</td>
            </tr>'; 
    }
    return $lines;
}

$data=getTable('usuario');
$lines=iterateDataToTable($data);

?>


<div class="col-md-8">
    <table class="table">
        <thead class="table-success table-striped">
            <tr>
                <th>Id</th>
                <th>Nombre</th>
                <th>Apellido</th>
                <th>Edad</th>
                <th>Fecha de nacimiento</th>
            </tr>
        </thead>
        <tbody>
            <?php
                echo $lines;
            ?>
        </tbody>
    </table>
</div>
    • 3

  2. As I mentioned, you are not generating the dynamic html.
    You are always sending the last record, so no new data is inserted.

    What you should change is this part of the tbody, to the following:

    <tbody>
    <?php
    foreach($row = mysqli_fetch_array($query)) {
        echo "<tr>";
        echo "<th>" . $row['id'] . "</th>";
        echo "<th>" . $row['nombre'] . "</th>";
        echo "<th>" . $row['apellido'] . "</th>";
        echo "<th>" . $row['edad'] . "</th>";
        echo "<th>" . $row['fecha_de_nacimiento'] . "</th>";
        echo "</tr>";
    }
    ?>
</tbody>
    • 1
  3. Best Answer

    I would recommend doing it with foreach friend, with while you only send a data.

    <tbody>
    <?php
    foreach ($query as $row) {
    ?>
        <tr>
            <th><?php echo $row['id'] ?></th>
            <th><?php echo $row['nombre'] ?></th>
            <th><?php echo $row['apellido'] ?></th>
            <th><?php echo $row['edad'] ?></th>
            <th><?php echo $row['fecha_de_nacimiento'] ?></th>
        </tr>
    <?php
    }
    ?>
</tbody>
    • 1