PHP Pdo Managing Binary data by using Blob data types of MySQL database table

BLOB : Binary Large Objects

While handling binary or unstructured data like image or multimedia files we use Blob Data type of MySQL table.
BLOB: binary large object
We will learn how to add binary data , update and display them by using sample image file as data.

Add, update & display Unstructured binary data like image or multimedia files in MySQL Blob columns

student table

Our student_profile table has three columns,

id int(2)
student varchar(10)
profile_photo blob

This can be linked with our student table and photos of the student can be displayed.

Adding image to a Blob column

We can read image data in binary mode and then use the same to add records to our table. Here config.php file is our database connection file.

include "config.php";
$id=8;
$student='Alex';
//$photo= fopen('photos/1.png', 'rb'); // reading binary data
$photo= file_get_contents('photos/1.png'); // reading binary data

$query="insert into student_profile(id,student,profile_photo)
 values(:id,:student,:profile_photo)";
$step=$dbo->prepare($query);
$step->bindParam(':id',$id,PDO::PARAM_INT, 3);
$step->bindParam(':student',$student,PDO::PARAM_STR, 10);
$step->bindParam(':profile_photo',$photo,PDO::PARAM_LOB);

if($step->execute()){

echo " Data with Photo is added ";
}
else{
echo " Not able to add data please contact Admin ";
print_r($step->errorInfo()); 
}

Displaying all records with images

We will collect the image from blob column and display the same by using base64_encode().

include "config.php";

$query="SELECT * FROM student_profile";
echo "<table><tr><th>ID</th><th>Name</th>
                <th>Profile</th></tr>";
foreach($dbo->query($query) as $row){
echo "<tr><td><a href=record-display.php?id=$row[id]>$row[id]</a></td>
<td>$row[student]</td><td>"
."<img src='data:image/jpeg;base64,".base64_encode($row[profile_photo]).
"'/></td>
</tr>";
}
echo "</table>";

Watch the line used to display the image using $row[profile_photo].

"<img src='data:image/jpeg;base64,".base64_encode($row[profile_photo])."'>"

Updating record with Blob column data

$id=1;
$student='Alex';
$photo= fopen('photos/2.png', 'rb');
$query="UPDATE student_profile SET profile_photo=:profile_photo
WHERE id=:id";
 
$step=$dbo->prepare($query);
$step->bindParam(':id',$id,PDO::PARAM_INT, 3);
$step->bindParam(':profile_photo',$photo,PDO::PARAM_LOB);
if($step->execute()){

echo " Data with Photo is Updated";
}
else{
echo " Not able to add data please contact Admin ";
print_r($step->errorInfo()); 
}

Uploading image and storing in Blob column

Most of the time we allow user to enter data and upload photo using a form and store the same in a Blob column.
Upload Image and store in Blob column of MySQL table
PDO References rowcount()

Subscribe to our YouTube Channel here

Managing PDO MySQL Blob Data type

student table

Adding image to a Blob column

Displaying all records with images

Updating record with Blob column data

Uploading image and storing in Blob column

Subscribe