Adding deleting and displaying record with BLOB data in SQLite database table

In SQLite, a BLOB (Binary Large Object) column is used to store large amounts of binary data, such as images, audio files, videos, or any other file types.

The BLOB data is stored exactly as it is, without any modification or encoding.

In PHP, BLOBs are handled as strings, making it easy to store or retrieve files using PDO. You can insert or fetch BLOB data using prepared statements, which ensures security and prevents SQL injection. BLOBs are ideal for managing large media files within SQLite databases.

Creating table to store BLOB data

$sql=" CREATE TABLE IF NOT EXISTS student_profile (
  `profile_id`  INTEGER PRIMARY KEY,
  `student` TEXT NOT NULL,
  `profile_photo` blob 
);"; 

try {
$my_conn->exec($sql);
echo " Table student_profile created ";
}
catch (PDOException $e) {
    echo "Table Not created : " . $e->getMessage();
}

Adding record with image ( blob column )

We have kept 4 sample images inside our photos directory. You can use them or use your images to store inside blob column. To read the image file, update the path below.

$profile_id=1; // unique id for the profile 
$student='King'; // Name of the student 
// Set the path to your location of photos in your directory 
$photo= fopen('photos/1.png', 'rb'); // reading binary data
//$photo= file_get_contents('photos/1.png'); // reading binary data

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

if($stmt->execute()){

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

This PHP code inserts a student's profile data, including a photo, into an SQLite database.

The $profile_id and $student are set to unique values, and the photo is read as binary data using fopen().

The SQL query uses placeholders (:profile_id, :student, :profile_photo) for secure insertion via prepared statements. The bindParam() function binds each value to the corresponding SQL placeholder.

After executing the query, if successful, the script returns the last inserted record's ID. Otherwise, it prints an error message.

Displaying records with BLOB data

// Displaying all reocrds from student_profile table //
$query="SELECT * FROM student_profile;";
try{
echo "<table><tr><th>profile_id</th><th>Student</th>
	<th>Picture</th></tr>";
foreach($my_conn->query($query) as $row){
echo "<tr><td>$row[profile_id]</td><td>$row[student]</td>
	<td>
	<img src='data:image/jpeg;base64,".base64_encode($row['profile_photo'])."'/>
	</td></tr>";
}
echo "</table>";
}
catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

Key Components:

The query selects all records from the student_profile table.
The fetched records are displayed in an HTML table with columns for profile ID, student name, and profile photo.
Each profile photo is displayed by encoding it as a base64 string to render it as an image directly in the HTML.
If the query fails, an exception is caught and an error message is displayed.

Delete the table ( with all data )

$sql='DROP table student_profile';
try {
$my_conn->exec($sql);
echo "
Table student_profile deleted ";
}
catch (PDOException $e) {
    echo "Database error : " . $e->getMessage();
}

Linking to main student table and profile table to display complete data

Linking tables to display rows with pictures

$query = "SELECT student.id, student.name, student.class, 
	student.mark, student.gender, student_profile.profile_photo 
	FROM student 
	JOIN student_profile ON student.id = student_profile.profile_id;";

try {
    echo "<table><tr><th>Student ID</th><th>Student Name</th>
	<th>Class</th><th>Mark</th><th>Gender</th><th>Profile Photo</th></tr>";
    foreach($my_conn->query($query) as $row) {
        echo "<tr>
                <td>{$row['id']}</td>
                <td>{$row['name']}</td>
                <td>{$row['class']}</td>
                <td>{$row['mark']}</td>
                <td>{$row['gender']}</td>
                <td><img src='data:image/jpeg;base64,".base64_encode($row['profile_photo'])."'/></td>
              </tr>";
    }
    echo "</table>";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

This code performs a SQL JOIN between the student and student_profile tables, retrieving combined data from both tables:

Key Components:

The JOIN operation links the two tables based on the matching id and profile_id.
We retrieve student information including class, mark, and gender along with the profile_photo.
The profile photo is displayed using base64 encoding to render it as an image directly in the HTML.
If there is an error in the database connection or query execution, a PDOException will be caught, and an error message will be displayed.

This combined data is displayed in a structured HTML table.

Fetching and Displaying Student Details (single record) Using PHP Query String

Typically, a single page is used to display a student's full details, including their photo, by linking multiple tables. The page retrieves the student ID from the query string and uses it to fetch and display relevant details. Only the URL changes with different student IDs, while the core code remains unchanged for each request.

$student_id = $_GET['id'];  // Get student ID from query string
//$student_id=2; // for testing 
$query = "SELECT student.id, student.name, student.class, student.mark,
 student.gender, student_profile.profile_photo 
          FROM student 
          JOIN student_profile ON student.id = student_profile.profile_id
          WHERE student.id = :id";

$stmt = $my_conn->prepare($query);
$stmt->bindParam(':id', $student_id, PDO::PARAM_INT);
$stmt->execute();

if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "<table><tr><th>Student ID</th><th>Name</th><th>Class</th>
	<th>Mark</th><th>Gender</th><th>Profile Photo</th></tr>";
    echo "<tr><td>{$row['id']}</td><td>{$row['name']}</td><td>{$row['class']}</td>
          <td>{$row['mark']}</td><td>{$row['gender']}</td>
          <td><img src='data:image/jpeg;base64,".base64_encode($row['profile_photo'])."'/></td></tr>";
    echo "</table>";
} else {
    echo "No student found with ID: $student_id";
}

This PHP script retrieves a student’s details based on their ID passed via the query string. Here's how it works:

Retrieve Student ID: The student ID is obtained from the URL’s query string using $_GET['id'].
SQL Query with JOIN: The JOIN operation combines the student and student_profile tables, linking them through the id column to fetch related information such as name, class, mark, gender, and profile photo.
Prepared Statement: A prepared statement is used to safely execute the query with the student ID as a parameter, which is bound using bindParam().
Displaying Data: If the student is found, the details are displayed in an HTML table, including the profile photo, which is rendered using base64 encoding. If no matching record is found, an error message is shown.

Sample pictures

<img src=https://www.plus2net.com/php_tutorial/photos/1.png>
<img src=https://www.plus2net.com/php_tutorial/photos/2.png>
<img src=https://www.plus2net.com/php_tutorial/photos/3.png>
<img src=https://www.plus2net.com/php_tutorial/photos/4.png>

Podcast on SQLite database management using PHP PDO

MySQL Blob data

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: