How to display all the data of a table stored in a MySQL database in PHP-CodePudding

I am trying to display the data of a table containing an image in blob format, but I can only get the last inserted record to be displayed. I would like to show all the records with their respective images, and when adding a new record it will be shown with the rest of the table data.

MYSQL

noticias: idNoticia(PK), idUser(FK), titulo, imagen(LONGBLOB), texto, fecha

PHP - insertarNoticias.php

include('./DB.php');
session_start();

$idUser = $_SESSION['usuario'][0];
   $titulo = $_POST['titulo-noticia'];
   $texto = $_POST['texto-noticia'];
   $fecha = $_POST['fecha-noticia'];
   $nombreImagen = $_FILES['imagen']['name'];
   $carpetaDestino = $_SERVER['DOCUMENT_ROOT'] . '/masterD/trabajo_final_php/img/'; 
   move_uploaded_file($_FILES['imagen']['tmp_name'], $carpetaDestino . $nombreImagen);
   
   $conexion = DB::conn();
   $imagen = fopen($carpetaDestino . $nombreImagen, "r");
   $archivoBytes = fread($imagen, intval(filesize($carpetaDestino . $nombreImagen)));
   fclose($imagen);
   
   $sentencia = 'INSERT INTO noticias (idUser, titulo, imagen, texto, fecha) VALUES (:idUser, :titulo, :imagen, :texto, :fecha)';
   $consulta = $conexion->prepare($sentencia);
   $consulta->bindParam(':idUser', $idUser);
   $consulta->bindParam(':titulo', $titulo);
   $consulta->bindParam(':imagen', $archivoBytes);
   $consulta->bindParam(':texto', $texto);
   $consulta->bindParam(':fecha', $fecha);
   $consulta->execute();
   $consulta->closeCursor();
   $conexion = null;

PHP - mostrarNoticias.php

include('./DB.php');
session_start();

$titulo = '';
    $imagen = '';
    $texto = '';
    $fecha = '';

    $conexion = DB::conn();
    $sentencia = 'SELECT * FROM noticias';
    $consulta = $conexion->prepare($sentencia);
    $consulta->execute();

    
    while ($row = $consulta->fetch(PDO::FETCH_ASSOC)) {
        
        $titulo = $row['titulo'];
        $imagen = $row['imagen'];
        $texto = $row['texto'];
        $fecha = $row['fecha'];
        
    }

    $consulta->closeCursor();
    $conexion = null;

PHP - adminNoticias.php

<?php
include('./mostrarNoticias.php');
?>

<form  action="./insertarNoticias.php" method="post" id="crear-noticia" enctype="multipart/form-data">
                            <div >
                                <input type="file" name="imagen"  id="imagen-noticia" required>
                                <input type="text"  name="titulo-noticia" id="titulo-noticia" placeholder="Título noticia" required>
                                <input type="text"  name="texto-noticia" id="texto-noticia" placeholder="Texto noticia" required>
                                <input type="date"  name="fecha-noticia" id="fecha-noticia" required>
                            </div>
                            <button type="submit" >
                                Crear noticia
                            </button>
                        </form>

<div  id="ver-noticias">
            <div >
                <?php
                echo "<h4>$titulo</h4>";
                echo "<p>$texto</p>";
                echo "<p>$fecha</p>";
                echo "<img width='300' height='160' src='data:image/png; base64," . base64_encode($imagen) . "'>";
                ?>
            </div>
        </div>

CodePudding user response：

You are fetching the data in a loop, over-writing the same variable. You need to display the variables in the loop. Its best to create a function to display each row and call it from the loop.

One solution is as follows -

Shift the include down to where the display is

PHP - adminNoticias.php

<form  action="./insertarNoticias.php" method="post" id="crear-noticia" enctype="multipart/form-data">
                            <div >
                                <input type="file" name="imagen"  id="imagen-noticia" required>
                                <input type="text"  name="titulo-noticia" id="titulo-noticia" placeholder="Título noticia" required>
                                <input type="text"  name="texto-noticia" id="texto-noticia" placeholder="Texto noticia" required>
                                <input type="date"  name="fecha-noticia" id="fecha-noticia" required>
                            </div>
                            <button type="submit" >
                                Crear noticia
                            </button>
                        </form>

<div  id="ver-noticias">
<?php
include('./mostrarNoticias.php');
?>
</div>

And show each row here

PHP - mostrarNoticias.php

include('./DB.php');
session_start();

$titulo = '';
    $imagen = '';
    $texto = '';
    $fecha = '';

    $conexion = DB::conn();
    $sentencia = 'SELECT * FROM noticias';
    $consulta = $conexion->prepare($sentencia);
    $consulta->execute();

    
    while ($row = $consulta->fetch(PDO::FETCH_ASSOC)) {
        
        $titulo = $row['titulo'];
        $imagen = $row['imagen'];
        $texto = $row['texto'];
        $fecha = $row['fecha'];
        ?>
        <div >
        <?php
        echo "<h4>$titulo</h4>";
        echo "<p>$texto</p>";
        echo "<p>$fecha</p>";
        echo "<img width='300' height='160' src='data:image/png; base64," . base64_encode($imagen) . "'>";
        ?>
        </div>        
        <?php
    }

    $consulta->closeCursor();
    $conexion = null;

CodePudding user response：

The very easy solution is shared here, but there may be alternative of this too. The problem in your code is that, your while loop continues its loops and the variables in the loop are replacing its values at each iteration. You can convert each variable to array and then use them in your code where you want to display the table using foreach loop. But the easiest solution is as below:

PHP - mostrarNoticias.php

$output='';
while ($row = $consulta->fetch(PDO::FETCH_ASSOC)) {

    $titulo = $row['titulo'];
    $imagen = $row['imagen'];
    $texto = $row['texto'];
    $fecha = $row['fecha'];

    $output .= "<h4>$titulo</h4>
            <p>$texto</p>
            <p>$fecha</p>
            <img width='300' height='160' src='data:image/png; base64," . base64_encode($imagen) . "'>";

}

PHP - adminNoticias.php In your adminNoticias file, replace the following div as below to use the output fetched from previous while loop containing all records.

<div  id="ver-noticias">
            <div >
                <?php
                echo $output;
                ?>
            </div>
</div>

If you need any explanation, I will appreciate :-)