Adding, displaying & deleting BLOB data

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 )

$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()); 
}

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

$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

$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>