Getting URL for Image stored in MySQL as a blob-CodePudding

My wife's family is big into jigsaw puzzles and I am using PHP and MySQL to build them a site so they can see what puzzles they already own. This should prevent issues of buying the same puzzle twice.

When someone adds a puzzle, I will get them to upload an image of the box art that will then be stored as a blob in the database. I have all the puzzles in the database appearing in a styled table and I am happy with how it looks, but there is one more piece of functionality that I would like to have.

The table displays the uploaded blob/image, and I use CSS to reduce it to thumbnail size - a future revision will show a true thumbnail to save data but that comes later, for now a CSS reduced blob/image is fine.

What I want to do is also have the thumbnail image be a link that can be clicked to open the image full size, but I don't know how to grab a URL from a blob stored in MySQL and hoping someone can help. What I currently have is below. Thanks in advance for your help!

$conn = new mysqli($configs->host, $configs->username, $configs->password, $configs->database);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT puzzles.name, puzzles.photo, manufacturers.name as manufacturer, owners.name as owner FROM puzzles LEFT JOIN manufacturers ON puzzles.manufacturer = manufacturers.id LEFT JOIN owners ON puzzles.owner = owners.id ORDER BY puzzles.id";
$result = $conn->query($sql);
?>

<?php
echo '<table id="puzzleTable">
<tr>
<th>Puzzle Name</th>
<th>Manufacturer</th>
<th>Owner</th>
<th>Puzzle Photo</th>
<th>Options</th>
</tr>';

while ($row = $result->fetch_assoc())
  {
    echo "<tr>";
    echo "<td>" . $row['name'] . "</td>";
    echo "<td>" . $row['manufacturer'] . "</td>";
    echo "<td>" . $row['owner'] . "</td>";
    echo "<td><a href='NEED BLOB IMAGE URL HERE'><img src='data:image/jpeg;base64,".base64_encode($row['photo'])."'/></a></td>";
    echo "<td><a href='#'>Edit</a> | <a href='#'>Delete</a></td>";
  }
echo "</tr></table>";

CodePudding user response：

You should create a link to a php page. A parameter for the ID of the record related to the database should be sent to it. In the php page, set the header like this: header("Content-type: imageType).

mysql blob using php

CodePudding user response：

for build your app that return your URL and you can show this image, you should to do a insert in your db with your local storage folder or internet URL. In new column. Blob return only object image, but I recommend you that not use blob, think that if your app it grows, you db will be very weight and will delay in return data.

you can to do:

query for url internet

$sql = "INSERT INTO puzzles(column, column, column, url)
VALUES ('xxx', 'xxx', 'xxx', 'https://urlInternet')";

if ($conn->query($sql) === TRUE) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

query for url local

$sql = "INSERT INTO puzzles(column, column, column, url)
    VALUES ('xxx', 'xxx', 'xxx', '/images/'.$_POST["img"])";
    
    if ($conn->query($sql) === TRUE) {
      echo "New record created successfully";
    } else {
      echo "Error: " . $sql . "<br>" . $conn->error;
    }

When you return your data:

while ($row = $result->fetch_assoc())
  {
    echo "<tr>";
    echo "<td>" . $row['name'] . "</td>";
    echo "<td>" . $row['manufacturer'] . "</td>";
    echo "<td>" . $row['owner'] . "</td>";
    echo "<td><a href='".$row['URL']"'><img src='data:image/jpeg;base64,".base64_encode($row['photo'])."'/></a></td>";
    echo "<td><a href='#'>Edit</a> | <a href='#'>Delete</a></td>";
  }