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
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
And show each row here
PHP – mostrarNoticias.php
…….
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
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.
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
Now Print the Output in adminNoticias.php file