Single Image CRUD Operations using PHP & MYSQL

by

Performing Single image CRUD operations using PHP and MySQL involves handling file uploads, storing file paths, and creating the necessary database operations.

In simple terms, CRUD operations (Create, Read, Update, Delete) on a single image allow you to do the following:

  1. Create: Upload an image.
  2. Read: View the uploaded image.
  3. Update: Modify or replace the existing image.
  4. Delete: Remove the image.

These operations are essential for building applications that involve working with individual images, like photo-sharing apps or websites where users can upload, view, edit, and remove their pictures.

Steps to Filter Data with Checkbox using PHP, MySQL

Learn Also –

Preview Image before uploading using PHP

Upload Multiple File to store in the Database using PHP

Ajax File Uploading using PHP

1. Create a Directory Structure

First of all, You should create the following directory structure to filter data by category.

source-code/
   |__public/images/
   |__database.php
   |__index.php
   |__script.php
   |__table.php
   |

2. Create a MySQL Table

Now, Create a MySQL database

CREATE DATABASE myproject;

Create a table with the name of the singleImage

CREATE TABLE `singleImage` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `image` varchar(255) DEFAULT NOT NULL,
);

3. Connect to MySQL Database

Now, You have to connect your PHP to the MySQL database with the following PHP script –

File Name – database.php

<?php

$host = "localhost";
$user = "root";
$password = "";
$database = "myproject";

$conn = new mysqli($host, $user, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

Upload Single Image with Form

This code seems to be part of a simple image upload and update system where users can add new images or update existing ones through a web form. The image data is likely stored in a database, and the CRUD operations (Create, Read, Update, Delete) are handled by the SingleImageCrud class.

File Name – index.php

<!DOCTYPE html>
<html>
<body>
<a href="table.php">Image List</a>
<br><br>

<?php
require_once('database.php');
require_once('script.php');
$singleImageCrud = new SingleImageCrud($conn);


if(isset($_POST['submit']) && !isset($_GET['id'])) {
    $create = $singleImageCrud->create();
    $msg = $create['success'] ? 'Image saved successfully' : $create['uploadSingleImage'];
    echo $msg;
}

if(isset($_POST['submit']) && isset($_GET['id'])) {
    $id= $_GET['id'];
    $create = $singleImageCrud->updateById($id);
    $msg = $create['success'] ? 'Image updated successfully' : $create['uploadSingleImage'];
    echo $msg;
}
if(isset($_GET['id'])){
    $id= $_GET['id'];
    $getImage = $singleImageCrud->getById($id);
    
 }
?>

<form method="post" enctype="multipart/form-data">
  <label>Single Image</label><br>
  <input type="file" name="image" />
  <br>
  <?php 
  if(isset($getImage['image'])) { ?>
  <img src="public/images/<?= $getImage['image']; ?>"  width="50px" />
  <?php } ?>
  <br>  <br>
  <input type="submit" name="submit" />
</form>

</body>
</html>

Explanation –

HTML Structure:

  • <!DOCTYPE html> declares the HTML version.
  • <html> and <body> tags define the main structure of the HTML document.
  • There’s a link (<a>) to “table.php” labeled “Image List.”

PHP Section:

  • The code starts with PHP tags <?php and includes external files using require_once.
  • It initializes an instance of SingleImageCrud class, presumably a class responsible for CRUD (Create, Read, Update, Delete) operations on single images.
  • The script checks if the form is submitted ($_POST['submit']) and whether an image ID ($_GET['id']) is set.

Image Upload Handling:

  • If the form is submitted and no image ID is provided (!isset($_GET['id'])), it attempts to create a new image record using the create() method of SingleImageCrud. It then displays a success or error message.
  • If the form is submitted with an image ID set (isset($_GET['id'])), it updates the existing image record using the updateById() method of SingleImageCrud. Again, it displays a success or error message.

Image Retrieval:

  • If an image ID is present in the URL (isset($_GET['id'])), it fetches the image details using the getById() method of SingleImageCrud.

HTML Form:

  • A form is displayed with the method set to “post” and enctype attribute set to “multipart/form-data” (used for file uploads).
  • It includes an input field of type “file” for selecting an image file.
  • If an image is associated with the current record ($getImage['image'] is set), it displays the image.

Submit Button:

A submit button is provided with the name “submit.”

Displaying Messages:

The success or error messages from image creation or update operations are displayed.

Displaying Image:

If an image exists for the current record, it displays the image below the file input.

CRUD Operations on Single Image

This PHP code defines a class named SingleImageCrud, which appears to be a simple CRUD (Create, Read, Update, Delete) implementation for managing single image records in a database.

File Name – script.php

<?php

class SingleImageCrud {

    private $conn;
    private $tableName = 'singleImage';
    

    public function __construct($conn) {
        $this->conn = $conn;
    }


    public function uploadSingleImage($id= null) {
  
            $error = false;
            $msg = null;
           
            $uploadTo = "public/images/"; 

            $allowFileType = array('jpg','png','jpeg');

            $fileName = $_FILES['image']['name'];
            $tempPath = $_FILES["image"]["tmp_name"];
        
            $basename = basename($fileName);
            $originalPath = $uploadTo.$basename; 
            $fileType = pathinfo($originalPath, PATHINFO_EXTENSION); 
         
            if(!empty($fileName)){ 
               if(in_array($fileType, $allowFileType)){ 

                 if(!move_uploaded_file($tempPath, $originalPath)){ 
                    $msg = 'Image Not uploaded ! try again';
                    $error = true;
                }
             }else{  
                $msg = 'Image type is not allowed'; 
                $error = true;
             }
           } else {
                 $msg = 'Image is required'; 
                 $error = true;
           }  
         
        $imageInfo = [
            "error" => $error, 
            "msg" => $msg, 
            "imageName" => $fileName
        ];

        return  $imageInfo;
    }

    public function create() {

            $uploadSingleImage = $this->uploadSingleImage();
            $success = false;
            if (!$uploadSingleImage['error']) {
                //  table name for admin profiles
                $query = "INSERT INTO " . $this->tableName;
                $query .=  " (image) VALUES (?)";

                $stmt = $this->conn->prepare($query);
    
                $stmt->bind_param("s", $uploadSingleImage['imageName']);
    
                if ($stmt->execute()) {
                    $success = true;
                    $stmt->close();
                }
            }
    
        $data = [
            'uploadSingleImage' => $uploadSingleImage['msg'] ?? 'Unable to upload profile due to other fields facing errors',
            'success' => $success
        ];
    
        return $data;
    }
    

    public function get() {

        $data = [];
    
        $query = "SELECT id, image FROM ";
        $query .= $this->adminTable;

        $result = $this->conn->query($query);
        
        if ($result) {
            while ($row = $result->fetch_assoc()) {
                $data[] = $row;
            }
            $result->free();
        }
    
        return $data;
    }

    public function getById($id) {

        $data = [];
    
        $query = "SELECT image FROM ";
        $query .= $this->adminTable;
        $query .= " WHERE id = ?";

        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("i", $id);
       
        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $data = $result->fetch_assoc();
            $stmt->close();
        } 

        return $data;
    }

    public function updateById($id) {
      
        $success = false;

        $uploadSingleImage = $this->uploadSingleImage($id);
        
        if (!$uploadSingleImage['error']) {
        // Replace 'content' with the correct table name for admin profiles
            $query = "UPDATE " . $this->adminTable;
            $query .= " SET image = ? ";
            $query .= " WHERE id = ?";
            $stmt = $this->conn->prepare($query);
        
            $stmt->bind_param("si", $uploadSingleImage['imageName'], $id);
        
            if ($stmt->execute()) {
                $success = true;
                
            } 
        }
        
        
        $data = [
            'success' => $success,
            'uploadSingleImage' => $uploadSingleImage['msg'] ?? 'Unable to upload profile due to other fields facing errors',
        ];

        
        return $data;
    }
    
    
    public function deleteById($id) {

        $query = "DELETE FROM ";
        $query .= $this->adminTable;
        $query .= " WHERE id = ?";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("i", $id);
       
        if ($stmt->execute()) {
            $stmt->close();
            return true;
        } else {
            $stmt->close();
            return false;
        }
    }
    
}



?>

Explanation –

Class Properties:

  • $conn: A private property to store the database connection.
  • $adminTable: A private property representing the table name for storing single image records.

Constructor:

  • __construct($conn): A constructor that takes a database connection as a parameter and initializes the $conn property.

Upload Function:

uploadSingleImage($id = null): Handles the file upload process for a single image. It checks if the image is of an allowed file type (jpg, png, jpeg) and moves it to a specified directory (public/images/).

The function returns an array containing information about the upload, including any errors and the uploaded image name.

Create Function:

create(): Calls the uploadSingleImage function and, if successful, inserts the image name into the database table. Returns an array with information about the upload process and the success status.

Get Functions:

  • get(): Retrieves all records from the database table, including the image IDs and names.
  • getById($id): Retrieves a single record based on the provided ID.

Update Function:

updateById($id): Calls uploadSingleImage for updating an existing record based on the provided ID. If the image upload is successful, it updates the corresponding record in the database.

Delete Function:

deleteById($id): Deletes a record from the database table based on the provided ID.

Notes:

  • The code uses prepared statements to prevent SQL injection.
  • The actual database connection is assumed to be established outside this class and passed to the constructor.
  • Error handling is implemented, and the functions return arrays with information about the operations.

Get All Uploaded Images to display, edit & delete

This PHP code is part of a web application for managing images. It displays a table of images with options to edit and delete each. Images are retrieved from a database, and there’s a link to upload new images. The code also handles image deletion and provides a success message when an image is deleted. The actual database connection and CRUD operations are likely implemented in external files (`database.php` and `script.php`).

File Name – table.php

<!DOCTYPE html>
<html>
<body>


<a href="form.php">Upload Image</a>
<br><br>
<?php
require_once('database.php');
require_once('script.php');
$singleImageCrud = new SingleImageCrud($conn);

if(isset($_GET['id'])){
   $id= $_GET['id'];
   $deleteImage = $singleImageCrud->deleteById($id);
   if($deleteImage){
    echo "Image is deleted successfully";
    
   }
}




?>

<table border="1" cellspacing="0" cellpadding="5" width="40%">
    <tr>
        <th>
            S.N
        </th>
        <th>
            Image
        </th>
        <th colspan="2">
            Action
        </th>
    </tr>
    <?php
    $sn = 1;
    $getSingleImage = $singleImageCrud->get();
    foreach($getSingleImage as $image) {
    ?>
    <tr>
        <td><?= $sn; ?></td>
        <td><img src="public/images/<?=$image['image']; ?>" width="100px"/></td>
        <td><a href="form.php?id=<?=$image['id']; ?>">Edit</a></td>
        <td><a href="table.php?id=<?=$image['id']; ?>">Delete</a></td>
    </tr>
    <?php $sn++; } ?>
</table>

</body>
</html>

Explanation –

HTML Structure:

  • The code starts with the standard HTML document structure.
  • It contains a link <a> that points to a file named form.php and displays the text “Upload Image.”
  • Below the link, there is a line break <br><br> for spacing.

PHP Section:

  • require_once('database.php'); and require_once('script.php'); are including external PHP files named database.php and script.php.
  • $singleImageCrud = new SingleImageCrud($conn); creates an instance of the SingleImageCrud class, presumably defined in the included script.php file. This instance is used to interact with the database.

Image Deletion:

  • The code checks if the id parameter is set in the URL using isset($_GET['id']).
  • If id is set, it retrieves the value and attempts to delete the image with that ID from the database using the deleteById method of the SingleImageCrud class.

Table Display:

  • A table is created with three columns: S.N (serial number), Image, and Action.
  • Inside the table, there is a loop (foreach) that iterates over an array of images obtained from the database using the get method of the SingleImageCrud class.
  • For each image, a table row (<tr>) is created with three cells: S.N, an image displayed using an <img> tag, and two action links for editing and deleting.

Image Display:

  • The image file paths are assumed to be in the “public/images/” directory, and the file names are retrieved from the database.
  • The image files are displayed in the table using the <img> tag with the src attribute set to the corresponding file path.

Action Links:

  • The “Edit” link (<a href="form.php?id=<?=$image['id']; ?>">Edit</a>) includes the image ID in the URL and presumably redirects to the form.php page for editing.
  • The “Delete” link (<a href="table.php?id=<?=$image['id']; ?>">Delete</a>) includes the image ID in the URL and appears to be intended for deleting the corresponding image.

Feedback Message:

  • If an image is successfully deleted (based on the earlier deletion attempt), a success message “Image is deleted successfully” is echoed.