Managing PDO MySQL Blob Data type

PHP PDO Blob MySQL

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

* indicates required
Subscribe to plus2net

    plus2net.com







    Post your comments , suggestion , error , requirements etc here




    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer