How to display an image stored in BLOB format in a MySQL database in PHP

Question

How to display an image stored in BLOB format in a MySQL database in PHP

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

3

I have an image field that is a BLOB type in my MySQL database, I collect the data by PDO, but at the time of viewing it, it does not show me anything, I searched the Internet and everywhere I put the same thing with src="data:image/jpeg;base64 .

This is my code:

 <?php   
require_once './BaseDatos.php';
$id = $_GET['id'];
$bd = new BaseDatos('foro');
$imagen = $bd->SenetenciaSelect("usuarios", ["id"=>$id])[0]["image"];
echo '<img src="data:image/jpeg;base64,'.base64_encode($imagen) .' "/>';

The variable $imagen returns this if I visualize it:

yoya \ 0JFIF \ 0 \ 0 \ 0 \ 0 \ 0 \ 0ÿþ \ 0; CREATOR: gd-jpeg v1.0 (using IJG JPEG v62), quality = 90 YU \ 0C \ 0% #, # & \ ') *) - 0- (0% () (ÿÛ \ 0C (((((((((((((((((((((((((( (((((((((((((((ÿÀ \ 0 @ \ 0 \ "\ 0ÿÄ \ 0 \ 0 \ 0 \ 0 \ 0 \ 0 \ 0 \ 0 \ 0 \ 0 \ 0 ! Ya \ 0μ \ 0 \ 0 \ 0} \ 0 1AQa \ "# B ± q2'¡ Arnd $ 3BR,% & \ '() * 456789: CDEFGHIJSTUVWXYZcdefghijstuvwxyzƒ" ... ‰ Š ‡ †' "" • - ~ ™ š ¢ £ ¤ ¥ |§ © ª²³'μ¶ ØÙÚáâãäåæçèéêñòóôõö × ÷ · ¸¹ºÂÃÄÅÆÇÈÉÊÒÓÔÕÖ øùúÿÄ \ 0 \ 0 \ 0 \ 0 \ 0 \ 0 \ 0 \ 0 Ya \ 0μ \ 0 \ 0w \ 0! 1AQaq \ "2B'¡ ± Á etc.

mysql php pdo blob

asked by superzuman 20.02.2017 в 12:06

source

3 answers

Report Viewer DLLS Problems How to read fixes within JSON objects with retrofit 2 in android? error: "java.net.ConnectException: Failed to connect to"

score 0 · Answer 1

Everything points to a problem of conversion of character sets that is corrupting the data of your BLOB or you are suffering some problem related to using the obsolete functions from PHP 5.5 of mysql_* or poorly escaped SQL.

Problems related to the character set:

If at any time you use SET CHARACTER SET utf8 after establishing the connection, the client will begin to convert characters even in the fields of type BLOB from the character set in which the table is.

If you use SET NAMES utf8 instead, both the server and the client will agree on the same character set for everything and there will be no conversion on the client side that could corrupt the data.

View the data on this page .

In it you can see that SET NAMES 'charset_name' is equivalent to:

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

However SET CHARACTER SET 'charset_name' equals:

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET collation_connection = @@collation_database;

So if the table is at its default value ( latin1_swedish_ci ) will be corrupting the data.

Problems related to escaped SQL:

To insert the data in the database properly you must modify your code and use mysql_real_escape_string() instead of addslashes() :

$fp = fopen($ruta, 'r');
$datos_image = fread($fp, filesize($ruta));
$datos_image = mysql_real_escape_string($datos_image);
fclose($fp);

That way you will not have problems with strange characters. You should also try using mysql_set_charset() .

Still, I urge you to migrate your code to mysqli or, better yet, to PDO . That way you can make queries prepared with assignment of fields in which you will not have problems with the escape of chains.

score 0 · Answer 2

Guiding me for your comment in @ a-cedano's response. I put together a test and it seems that the problem is that before saving the BLOB you apply addslashes but you do not apply the stripslashes when you retrieve the info

this is my test code, (assumes a valid test.jpg file on the same route)

<?php

$ruta = 'prueba.jpg';
$fp = fopen($ruta, 'r');
$datos_image = fread($fp, filesize($ruta));
$datos_image = addslashes($datos_image);
fclose($fp);

echo '<img
src="data:image/jpeg;base64,'.base64_encode(stripslashes($datos_image)) .' "/>';
//var_dump($datos_image);

?>

score 0 · Answer 3

EDIT: Complete example

<?php
    /*
     *Modifica modo de conexión, este ejemplo usa mysqli, pero puedes cambiarlo a PDO
     *Modifica las credenciales de conexión: usuario, password, nombre de la BD
     *Modifica los nombres de tablas y columnas
    */
    $conn = mysql_connect("localhost", "root", "");
    mysql_select_db("phppot_examples");
    $sql = "SELECT imageId FROM output_images ORDER BY imageId DESC"; 
    $result = mysql_query($sql);
?>
<HTML>
    <HEAD>
        <TITLE>List BLOB Images</TITLE>
    </HEAD>
<BODY>
<?php
    while($row = mysql_fetch_array($result)) {
?>
//En $row["imageId"] debes cambiar imageId por como se llama en tu tabla la columna que tiene la id de la imagen
<img src="imageView.php?image_id=<?php echo $row["imageId"]; ?>" /><br/>
<?php       
    }
    mysql_close($conn);
?>
    </BODY>
</HTML>

Three possible solutions (@deprecated):

A. Using your variable $imagen after $imagen=$bd... you put this and you comment the current code in which you try to show the image:

<img src="image.php?id=<?php echo $imagen; ?>" />
header('Content-Type: image/jpeg');
echo $imagen;

B. Also using your variable $imagen after $imagen=$bd... comment your current code to show the image and copy and paste this:

echo '<img src="data:image/jpeg;base64,'.base64_encode($imagen).'"/>';

C. It does not work? Make sure your image is correct in the database.