image uploads but won't display php-CodePudding

I successfully can upload the image into MySQL but when trying to display the image from the MySQL they appear broken.

  $image = $_FILES['image']['tmp_name'];
   $sql = "INSERT INTO images (image,id) VALUES(?,?)";
   $statement = $conn->prepare($sql);
   $statement->bind_param('si', $image, $id);
   $statement->execute();
   
 $check = mysqli_stmt_affected_rows($statement);
 if($check == 1){
   $msg = 'Image was uploaded';
 }else{
   $msg = 'Something went wrong!';
 }
}
?>
<form action="" method="post" enctype="multipart/form-data">
    <input type="file" name="image" />
    <button>Upload</button>
</form>
<?php
    echo $msg;
?>
<?php

$sql = "SELECT image_id, image, id FROM images WHERE id = ?";
$statement = $conn->prepare($sql);
$statement->bind_param('i', $id);
$statement->execute();
$result = $statement->get_result();

foreach($result as $row){
   echo '<img src="data:image/jpg;base64,'.base64_encode($row['image'] ).'" height="200" width="200"/>';
}

Not sure what I did wrong any help would be much appreciated. Just playing around with this type of thing not a production product or I'd have put the form away from the code.

EDIT! database screenshot

So I edited my code as suggested...now the image isn't being saved as a blob at all the blob section is empty which is a issue.

    $msg = '';
$id = $_SESSION['id'];
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
    $tmpName = $_FILES['image']['tmp_name'];

  // Read the file 
  $fp      = fopen($tmpName, 'r');
  $image = fread($fp, filesize($tmpName));
  fclose($fp);
   $sql = "INSERT INTO images (image,id) VALUES(?,?)";
   $statement = $conn->prepare($sql);
   $statement->bind_param('bi', $image, $id);
   $statement->execute();
   
 $check = mysqli_stmt_affected_rows($statement);
 if($check == 1){
   $msg = 'Image was uploaded';
 }else{
   $msg = 'Something went wrong!';
 }
}
?>
<form action="" method="post" enctype="multipart/form-data">
    <input type="file" name="image" />
    <button>Upload</button>
</form>
<?php
    echo $msg;
?>
<?php

$sql = "SELECT image_id, image, id FROM images WHERE id = ?";
$statement = $conn->prepare($sql);
$statement->bind_param('i', $id);
$statement->execute();
$result = $statement->get_result();

foreach($result as $row){
   echo '<img src="data:image/jpg;base64,'.base64_encode($row['image'] ).'" height="200" width="200"/>';
}
?>

CodePudding user response：

Please use fread (or file_get_contents) to get the binary data uploaded and
Please specify "b" (blob) for binary data when using bind_param
For uploading graphic (which for sure is not too small in size), use send_long_data().

Reason:

If data size of a variable exceeds max. allowed packet size (max_allowed_packet), you have to specify b in types and use mysqli_stmt_send_long_data() to send the data in packets.

The above is quoted from the following official documentation: https://www.php.net/manual/zh/mysqli-stmt.bind-param.php

Hence change

 $image = $_FILES['image']['tmp_name'];
 $sql = "INSERT INTO images (image,id) VALUES(?,?)";
 $statement = $conn->prepare($sql);
 $statement->bind_param('si', $image, $id);
 $statement->execute();

  $tmpName = $_FILES['image']['tmp_name'];

  // Read the file 
  $fp      = fopen($tmpName, 'r');
  $image = fread($fp, filesize($tmpName));
  fclose($fp);

  // alternative method
  //$image = file_get_contents($tmpName); 

   $sql = "INSERT INTO images (image,id) VALUES(?,?)";
   $statement = $conn->prepare($sql);
   
   $null = NULL; 

   $statement->bind_param('bi', $null, $id);
   $statement->send_long_data(0, $image); 
   $statement->execute();

Note:

The $null variable is needed, because bind_param() always wants a variable reference for a given parameters. In this case the "b" (as in blob) parameter. So $null is just a dummy, to make the syntax work.

In the next step we need to "fill" the blob parameter with the actual data. This is done by send_long_data(). The first parameter of this method indicates which parameter to associate the data with. Parameters are numbered beginning with 0. The second parameter of send_long_data() contains the actual data to be stored.

So, for your case, you may use the following sample code (tested - 100% working):

<?php
session_start();

$servername = "localhost";
$username = "xxxxxx";
$password = "xxxxxxxxxxxx";
$dbname = "xxxxxxx";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

$msg = '';
$id = $_SESSION['id'];

$id=1234;  // I set this value for testing

if($_SERVER['REQUEST_METHOD'] == 'POST')
{
    $tmpName = $_FILES['image']['tmp_name'];

  // Read the file 

  $fp      = fopen($tmpName, 'r');
  $image = fread($fp, filesize($tmpName));
  fclose($fp);


   $sql = "INSERT INTO images (image,id) VALUES(?,?)";

   $statement = $conn->prepare($sql);
   
   $null = NULL; 

   $statement->bind_param('bi', $null, $id);

   $statement->send_long_data(0, $image); 
   $statement->execute();
   
 $check = mysqli_stmt_affected_rows($statement);
 if($check == 1){
   $msg = 'Image was uploaded';
 }else{
   $msg = 'Something went wrong!';
 }
}
?>

<form action="#" method="post" enctype="multipart/form-data">
<input type="file" name="image" />
<button>Upload</button>
</form>

<?php
    echo $msg;
?>
<?php

$sql = "SELECT image_id, image, id FROM images WHERE id = ?";
$statement = $conn->prepare($sql);
$statement->bind_param('i', $id);
$statement->execute();
$result = $statement->get_result();

foreach($result as $row){

echo '<img src="data:image/jpg;base64,'.base64_encode($row['image']).'" height="200" width="200"/>';
echo "<br>";
}

?>