Displaying BLOB image from Mysql database into dynamic div in html

Question

I have a BLOB image that is stored when the user submits an advert form, they have the choice of uploading one image. The image is stored in the database with the other information.

Every time my page loads it dynamically creates the advert divs and also fills in the matching information from my database into the div.

The problem I have is displaying all the information including the image together at the same time so when the user clicks to view the page they see each div with a different picture and information. I've seen the other posts on how to display the image but I doubt its the same method when you're displaying several images from the same database.

My database is set up as follows:

ID ADTITLE EMAIL PRICE DESCRIPTION CATEGORY name type size content DATE

The bold variables are for the image/blob

Here is my code where I retrieve the information :

$Category = 'Specials'; 
$query = $pdo->prepare("SELECT * FROM adsubm WHERE CATEGORY LIKE '%$Category%' ORDER BY DATE DESC" );
$query->execute();

while($row = $query->fetch()) {
    $Adtitle=$row['ADTITLE'];
    $Desc=$row['DESCRIPTION'];
    $Price=$row['PRICE'];
    $Date=$row['DATE'];
    $timestamp=strtotime($Date);
    $Day= date("d",$timestamp);
    $Month=date("F",$timestamp);
    $Newmonth=date('M', strtotime($Month . '01'));
    $Year=date("Y",$timestamp);
    header('Content-type: image/jpeg');
    $Image=$row['content'];


echo  " 
           <div class='[ col-xs-12 col-sm-offset-2 col-sm-8 ]' style='margin-top: 10px'>
                <ul class='event-list'>
                    <li>
                        <time datetime='$Date'>
                            <span class='day'>$Day</span>
                            <span class='month'>$Newmonth</span>
                            <span class='year'>$Year</span>
                            <span class='time'>ALL DAY</span>
                        </time>
                        <img alt='#' src='$Image/>
                        <div class='info'>
                            <h2 class='title'>$Adtitle</h2>
                            <p class='desc'>$Desc</p>
                                                        <ul>
                                <li style='width:50%;'><span class='fa fa-money'></span>  $Price</li>
                            </ul>
                        </div>

                    </li>
                              </ul>
                    </div>
        ";

My php skills are still beginner levels as well. I'm just trying to keep things as plain and simple as possible, i'll look into other methods abit later.

All help is appreciated. Thanks

Nigercongo

So your image is stored in db? Then have a look at #2329864 – Dianoetic

@Dianoetic could I do this then : $Image = data:image/png;base64,'.base64_encode(file_get_contents('image.png')); then just echo that? – Nigercongo

nah more $Image = 'data:image/png;base64,'.$blob ; where $blob is the image – Reckford

AFAI understand $Image should contain the image file, so just do: echo 'data:image/png;base64,'.base64_encode($Image); – Dianoetic

And remove header('Content-type: image/jpeg'); – Dianoetic

I did this $Displayimage="data:image/png;base64,'.base64_encode($Image)"; And changed the <img scr='$Displayimage'> I got a whole lot of ëA™1œÓôU=GV³Òl庽ºŽÖÚ1—š – Nigercongo

@Reckford the $blob, what would I define that as? would it be a $blob =$row['content']; – Nigercongo

Answer 1

1) Base64 option

Work with a single line, image/png for a png image and image/jpeg for a jpg one :

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

example :

<div style="background-color:black; text-align:center; padding: 5px;">
  <img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAADIAAAAwBAMAAACh2TSJAAAALVBMVEUAAADtNTX////3n5/+9fX719f7zMz5tLTzfHzuQED//f31jY3ybGzxXV3wVFRaxp+rAAAAAXRSTlMAQObYZgAAALVJREFUOMut0rENAjEQRNHdC4kY0QBaAQUQX0QAFSAKIKQEKiAA6VqgIkriApuV1x7pQPz0aWwHljLMpZ0CRDBGoXmeghGYKFJsUo90giAImCgV5OJF+oOgKE48MlGgs2VLBIunWesw0a1ZHqF82c7GmmIfUSpgotOly29DFPFJFDEhkgIT/V5mZuvj6XofKrHU6vyI4u37IYi36aN4h5tL7PJyif1dvCgEpapzISbCTEj5R78BZq5A5Ldh2XYAAAAASUVORK5CYII">
</div>

---

2) Dedicated page

With many big picture on the same page, the base64 may be not the good choice

Base64 is cool, but a bit heavy (usually around twice as the binary value encoded) and can't be cached by the browser as it's a part of the page, and not a page by itself (like a picture).

In this case, the best is to use a specific php page to display your picture :

On the main page use instead of base 64 : echo '<img src="image.php?id='.$id.'"/>'; with the id of the line you want the image.

On your image.php, for the basic you should use this :

// << include the $pdo here
$query = $pdo->prepare("SELECT `content` FROM `adsubm` WHERE `id` = :id" );
$query->execute(array(':id'=>$_GET['id']));
$data = $query->fetch();

if(empty($data)))
    header("HTTP/1.0 404 Not Found");
else {
    header('Content-type: image/jpeg');
    echo $data['content'];
}

Narcolepsy

Brilliant. Thank you! Good to see a working example, too. – Teleplay

Answer 2

I have spent many days to do research on this issue and here are some working ways tested, they look like these:

    1.1*<% 
            ResultSet rs = (ResultSet)request.getAttribute("bookinfo");
            String imgstr = Base64.getEncoder().encodeToString(rs.getBytes(9)); 
        %>
        <img  src="data:image/jpg;base64,<%= imgstr %>" > 
    3** <img  src="ImageLoader.do?bid=<%= rs.getInt(1) %>" >
    8*  <img  src="/Library_DataLogger/image/Prof.jpg" >
    10**<img  src="obj_url1" >
(At the nineth column, it contains a blob object for image of bookcover.)

For 1.1, previously at servlet, you have to write something like this to prepare $image1

String ImageFilePath = "D:/JSP_Servlet/Library_DataLogger/src/main/webapp/image/" + request.getPart("bookcover").getSubmittedFileName());
for (Part part : request.getParts()) part.write(ImageFilePath);
PreparedStatement psm = conn.prepareStatement("insert into books values (default,?,?,?,?,?,?,?,?)");            
//...
psm.setBinaryStream(8, new FileInputStream(new File(imgfile)));
psm.executeUpdate();
ResultSet rs = conn.createStatement().executeQuery("select * from books where id = (select max(id) from books)");
if (rs.next()) request.setAttribute("bookinfo", rs);

For 3, you have to prepare an ImageLoader servlet to get image for you like this:

Class.forName("com.mysql.cj.jdbc.Driver"); 
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/Tablename","user","password");
ResultSet rs1 = conn.createStatement().executeQuery("select * from books where id = " + request.getParameter("bid"));           
if (rs1.next()) { 
    response.setContentType("image/jpg");
    OutputStream os = response.getOutputStream();
    os.write(rs1.getBytes(9)); 
    os.flush(); os.close();
} else System.out.println("Rs1 is null");

For 8, previously at servlet, you have to save image in your java webapp at path webapp/image/Prof.bmp :

//After you upload image file, then you move to servlet, you have take the file and write it down to some where like this:  
String ImageFilePath = "D:/JSP_Servlet/Library_DataLogger/src/main/webapp/image/" + request.getPart("bookcover").getSubmittedFileName());
for (Part part : request.getParts()) part.write(ImageFilePath);

For 10, you have to use 1 HTML Input tag to choose a file then transform it into a HTML blob which can by used be HTML Img tag later: (it won't work with mySQL blob, I tried!!)

<script type="text/javascript">
    function getblob1() {
        let obj_url1 = window.URL.createObjectURL(document.getElementById("fileinput").files[0])
    }   
</script>
<input id="fileinput" type="file" onchange="getblob1()">
<img  src="obj_url1" >

Concisely, you can only use the 1.1* and 3** way only because the last 2 can not really help you display blob from resultset obtained from SQL Database system.

Imbecile

Furthermore, 3** way is abundant because it requires to implement a dedicated servlet just for (re)loading image once again. Normally, When you have a piece of book info, you have image of book cover in the result set already as well, just pull it out and display it as in 1.1 way. Hence, 1.1 way is neat and more reasonable. – Imbecile