Insert image from its path to mysql and retrieve it into tag using php

Question

I tried to insert image from its path into mysql database using php and I did it but when I need to retrieve the image from mysql database into tag the image doesn't appear.

my database

CREATE TABLE IF NOT EXISTS `tbl_images` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `name` blob NOT NULL,  
  PRIMARY KEY (`id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

index.php

<?php  
 $connect = mysqli_connect("localhost", "root", "", "test");  
 if(isset($_POST["insert"]))  
 {  
    $path='C:\\xampp\\htdocs\\test_img\\4.jpg';
      $query = "INSERT INTO tbl_images(name) VALUES ('LOAD_FILE($path)')";  
      if(mysqli_query($connect, $query))  
      {  
           echo '<script>alert("Image Inserted into Database")</script>';  
      }  
 } 
 
 ?>  
 <!DOCTYPE html>  
 <html>  
      <head>  
      </head>  
      <body>  
                <form method="post" enctype="multipart/form-data">  
                    <input type="submit" name="insert" id="insert" value="Insert"  />  
                </form>  
                <table >  
                <?php  
                $query = "SELECT * FROM tbl_images ";  
                $result = mysqli_query($connect, $query);  
                while($row = mysqli_fetch_array($result))  {  
                    echo '  
                        <tr>  
                            <td> <img src="data:image/jpeg;base64,'.base64_encode($row['name'] ).'" height="200" width="200" /> </td>  
                        </tr>  
                    ';  
                }  
                ?>  
                </table> 
      </body>  
 </html>

Edit: the insert doesn't work correctly. it insert the string LOAD_FILE(C:xampphtdocs est_img4.jpg) instead of the image itself.

Have you verified the image is actually stored in the database? It looks like you're inserting the string `LOAD_FILE(C:\xampp\htdocs\test_img\4.jpg)` into the database — Joni, Feb 19 '19 at 22:29
MyISAM is a very old storage engine that doesn't support any of the modern features expected a database like transactions or a journal for data integrity. If you can avoid, and you almost always can, that's something you should do. — tadman, Feb 19 '19 at 22:34
@Joni right I check it now and I realize that I inserting the string LOAD_FILE(C:\xampp\htdocs\test_img\4.jpg) instead of the image itself.I will edit the problem — shiva, Feb 19 '19 at 22:35
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add any data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or data *of any kind* directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Feb 19 '19 at 22:39
Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and ideally should not be used in new code. — tadman, Feb 19 '19 at 22:39

tadman · Accepted Answer · 2019-02-19T22:42:37.083

BLOB columns can only store 64KB of data, so any images larger than that, which is going to be most, will get truncated and can't be displayed.

You'll want to use LONGBLOB if you want to store these in the database.

You also can't insert using LOAD_FILE unless the file's present on the same server as the MySQL server process. This is not always the case. If it is present then you should be doing:

$stmt = $connect->prepare("INSERT INTO tbl_images(name) VALUES (LOAD_FILE(?))");
$stmt->bind_param('s', $path);
$stmt->execute();

Where the path is quoted, not the LOAD_FILE call itself. You're inserting a plain string and the literal text "LOAD_FILE(...)" is not valid image data.

Remember, storing images in the database is usually a bad idea. Store a reference to the image instead, like a file path or URL to an object-store.

You're also serving up images as inline data:base64 data which is extremely inefficient. Not only does it increase the amount of data required to transmit the image by about 33% but it means the images must be sent on each page load and cannot be cached. For mobile users this will burn through data fast.

Insert image from its path to mysql and retrieve it into tag using php

1 Answers1