skip to Main Content

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 class="form" action="./insertarNoticias.php" method="post" id="crear-noticia" enctype="multipart/form-data">
                            <div class="form-label">
                                <input type="file" name="imagen" class="form-control mb-3" id="imagen-noticia" required>
                                <input type="text" class="form-control mb-3" name="titulo-noticia" id="titulo-noticia" placeholder="Título noticia" required>
                                <input type="text" class="form-control mb-3" name="texto-noticia" id="texto-noticia" placeholder="Texto noticia" required>
                                <input type="date" class="form-control mb-3" name="fecha-noticia" id="fecha-noticia" required>
                            </div>
                            <button type="submit" class="btn btn-success col-12 text-center">
                                Crear noticia
                            </button>
                        </form>

<div class="grid-container pt-4" id="ver-noticias">
            <div class="grid-item">
                <?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>

@LuqMan this is what I mean

now it is shown like this

<div class="grid-container">
<div class="grid-item">
<h4></h4>
<p></p>
<p></p>
<img>
<h4></h4>
<p></p>
<p></p>
<img>
</div>
</div>

And I would like it to look like this:

<div class="grid-container">
<div class="grid-item">
<h4></h4>
<p></p>
<p></p>
<img>
</div>
<div class="grid-item">
<h4></h4>
<p></p>
<p></p>
<img>
</div>
</div>

2

Answers


  1. 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 class="form" action="./insertarNoticias.php" method="post" id="crear-noticia" enctype="multipart/form-data">
                                <div class="form-label">
                                    <input type="file" name="imagen" class="form-control mb-3" id="imagen-noticia" required>
                                    <input type="text" class="form-control mb-3" name="titulo-noticia" id="titulo-noticia" placeholder="Título noticia" required>
                                    <input type="text" class="form-control mb-3" name="texto-noticia" id="texto-noticia" placeholder="Texto noticia" required>
                                    <input type="date" class="form-control mb-3" name="fecha-noticia" id="fecha-noticia" required>
                                </div>
                                <button type="submit" class="btn btn-success col-12 text-center">
                                    Crear noticia
                                </button>
                            </form>
    
    <div class="grid-container pt-4" 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 class="grid-item">
            <?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;
    

    …….

    Login or Signup to reply.
  2. 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 class="grid-container pt-4" id="ver-noticias">
                <div class="grid-item">
                    <?php
                    echo $output;
                    ?>
                </div>
    </div>
    

    If you need any explanation, I will appreciate 🙂

    EDITED VERSION AS PER YOUR COMMENT

    The DIV element can be embedded in while loop 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 .= "<div class='grid-container pt-4' id='ver-noticias'>";
        $output .= "<div class='grid-item'><h4>$titulo</h4>";
        $output .="<p>$texto</p>
                <p>$fecha</p>
                <img width='300' height='160' src='data:image/png; base64," . base64_encode($imagen) . "'></div></div>";
    
    }
    

    Now Print the Output in adminNoticias.php file

                <?php
                echo $output;
                ?>
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search