CODEWITHSUNDEEP
phphtmlmysql
Kamanda
Kamanda

Reputation: 305

Displaying BLOB image from Mysql database into dynamic div in html

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

Upvotes: 6

Views: 65823

Answers (2)

thundernhut
thundernhut

Reputation: 11

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.

Upvotes: 0

Blag
Blag

Reputation: 5894

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'];
}

Upvotes: 24

Related Questions