Image Gallery CRUD with PHP and MySQL

CRUD operations are the essential functionality for any dynamic web application. It helps to manage data between the application and the database. The CRUD (Create, Read, Update and Delete) functionality can be easily implemented using PHP and MySQL. With PHP CRUD operations not only the data but also the image upload functionality can be managed. You can manage (add, edit, and delete) the images in the gallery using PHP CRUD with MySQL.

If you want to create dynamic image gallery, CRUD functionality helps to manage the images with the database. Image Gallery CRUD is very useful to integrate dynamic image gallery in the website with admin panel. In this tutorial, we will show you how to upload, add, edit, and delete images with dynamic gallery using PHP and MySQLi.

The following functionality will be implemented in the image gallery CRUD script.

• Fetch the images data from the database and listed on the web page.
• Upload image and add data to the database.
• Edit and update image data in the database.
• Delete image data from the database.
• Dynamic image gallery with lightbox.

Before getting started to create an Image Gallery CRUD application with PHP, take a look at the file structure.

image_gallery_crud_with_php/
├── config.php
├── DB.class.php
├── index.php
├── manage.php
├── addEdit.php
├── postAction.php
├── uploads/
└── assets/
    ├── fancybox/
    ├── bootstrap/
    ├── css/
    ├── js/
    └── images/

Create Database Table

To store the file information a table needs to be created in the database. The following SQL creates an images table with some basic fields in the MySQL database.

CREATE TABLE `images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Active, 0=Inactive',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Configurations (config.php)

In the config.php file, constant variables of the basic and database settings are defined.

Site Constants:

• UPLOAD_PATH – Set the folder path where the uploaded image files will be stored.

Database Constants:

• DB_HOST – Specify the database host.
• DB_USERNAME – Specify the database username.
• DB_PASSWORD – Specify the database password.
• DB_NAME – Specify the database name.

<?php 
 
// Basic site settings 
define('UPLOAD_PATH', 'uploads/images/');  
 
// Database configuration 
define('DB_HOST', 'localhost');   
define('DB_USERNAME', 'root');   
define('DB_PASSWORD', 'root');   
define('DB_NAME', 'codexworld_db'); 
 
// Start session 
if(!session_id()){ 
    session_start(); 
} 
 
?>

Database Class (DB.class.php)

The DB class handles all the database related operations (connect, select, insert, update, and delete).

• __construct() – Connect to the database with PHP and MySQLi Extension.
• getRows() – Fetch records from the database based on the specified conditions.
• insert() – Insert data into the database.
• update() – Update data into the database.
• delete() – Delete data from the database.

<?php 
/* 
 * DB Class 
 * This class is used for database related (connect, select, insert, update, and delete) operations 
 * @author    CodexWorld.com 
 * @url        http://www.codexworld.com 
 * @license    http://www.codexworld.com/license 
 */ 
 
// Include configuration file 
include_once "config.php"; 
 
class DB{ 
    private $dbHost     = DB_HOST; 
    private $dbUsername = DB_USERNAME; 
    private $dbPassword = DB_PASSWORD; 
    private $dbName     = DB_NAME; 
    private $tblName    = 'images'; 
     
    public function __construct(){ 
        if(!isset($this->db)){ 
            // Connect to the database 
            $conn = new mysqli($this->dbHost, $this->dbUsername, $this->dbPassword, $this->dbName); 
            if($conn->connect_error){ 
                die("Failed to connect with MySQL: " . $conn->connect_error); 
            }else{ 
                $this->db = $conn; 
            } 
        } 
    } 
     
    /* 
     * Returns rows from the database based on the conditions 
     * @params array select, where, order_by, limit and return_type conditions 
     */ 
    public function getRows($params = array()){ 
        $sql = 'SELECT '; 
        $sql .= array_key_exists("select",$params)?$params['select']:'*'; 
        $sql .= ' FROM '.$this->tblName; 
        if(array_key_exists("where", $params)){ 
            $sql .= ' WHERE '; 
            $i = 0; 
            foreach($params['where'] as $key => $value){ 
                $pre = ($i > 0)?' AND ':''; 
                $sql .= $pre.$key." = '".$value."'"; 
                $i++; 
            } 
        } 
         
        if(array_key_exists("order_by", $params)){ 
            $sql .= ' ORDER BY '.$params['order_by'];  
        }else{ 
            $sql .= ' ORDER BY id DESC ';  
        } 
         
        if(array_key_exists("start", $params) && array_key_exists("limit", $params)){ 
            $sql .= ' LIMIT '.$params['start'].','.$params['limit'];  
        }elseif(!array_key_exists("start", $params) && array_key_exists("limit", $params)){ 
            $sql .= ' LIMIT '.$params['limit'];  
        } 
         
        $result = $this->db->query($sql); 
         
        if(array_key_exists("return_type", $params) && $params['return_type'] != 'all'){ 
            switch($params['return_type']){ 
                case 'count': 
                    $data = $result->num_rows; 
                    break; 
                case 'single': 
                    $data = $result->fetch_assoc(); 
                    break; 
                default: 
                    $data = ''; 
            } 
        }else{ 
            if($result->num_rows > 0){ 
                while($row = $result->fetch_assoc()){ 
                    $data[] = $row; 
                } 
            } 
        } 
        return !empty($data)?$data:false; 
    } 
     
    /* 
     * Insert data into the database 
     * @param string name of the table 
     * @param array the data for inserting into the table 
     */ 
    public function insert($data){ 
        if(!empty($data) && is_array($data)){ 
            $columns = ''; 
            $values  = ''; 
            $i = 0; 
            if(!array_key_exists('created',$data)){ 
                $data['created'] = date("Y-m-d H:i:s"); 
            } 
            if(!array_key_exists('modified',$data)){ 
                $data['modified'] = date("Y-m-d H:i:s"); 
            } 
            foreach($data as $key=>$val){ 
                $pre = ($i > 0)?', ':''; 
                $columns .= $pre.$key; 
                $values  .= $pre."'".$this->db->real_escape_string($val)."'"; 
                $i++; 
            } 
            $query = "INSERT INTO ".$this->tblName." (".$columns.") VALUES (".$values.")"; 
            $insert = $this->db->query($query); 
            return $insert?$this->db->insert_id:false; 
        }else{ 
            return false; 
        } 
    } 
     
    /* 
     * Update data into the database 
     * @data array the data for updating into the table 
     * @params array where condition on updating data 
     */ 
    public function update($data, $params){ 
        if(!empty($data) && is_array($data)){ 
            $colvalSet = ''; 
            $whereSql = ''; 
            $i = 0; 
            if(!array_key_exists('modified',$data)){ 
                $data['modified'] = date("Y-m-d H:i:s"); 
            } 
            foreach($data as $key=>$val){ 
                $pre = ($i > 0)?', ':''; 
                $colvalSet .= $pre.$key."='".$this->db->real_escape_string($val)."'"; 
                $i++; 
            } 
            if(!empty($params)&& is_array($params)){ 
                $whereSql .= ' WHERE '; 
                $i = 0; 
                foreach($params as $key => $value){ 
                    $pre = ($i > 0)?' AND ':''; 
                    $whereSql .= $pre.$key." = '".$value."'"; 
                    $i++; 
                } 
            } 
            $query = "UPDATE ".$this->tblName." SET ".$colvalSet.$whereSql; 
            $update = $this->db->query($query); 
            return $update?$this->db->affected_rows:false; 
        }else{ 
            return false; 
        } 
    } 
     
    /* 
     * Delete data from the database 
     * @params array where condition on deleting data 
     */ 
    public function delete($params){ 
        $whereSql = ''; 
        if(!empty($params)&& is_array($params)){ 
            $whereSql .= ' WHERE '; 
            $i = 0; 
            foreach($params as $key => $value){ 
                $pre = ($i > 0)?' AND ':''; 
                $whereSql .= $pre.$key." = '".$value."'"; 
                $i++; 
            } 
        } 
        $query = "DELETE FROM ".$this->tblName.$whereSql; 
        $delete = $this->db->query($query); 
        return $delete?true:false; 
    } 
} 
 
?>

Bootstrap Library

We will use the Bootstrap library to style the image gallery, list, table data, form fields, and links in HTML. So, the Bootstrap CSS file should be included in all the web pages.

<link rel="stylesheet" href="assets/bootstrap/bootstrap.min.css">

Images List (manage.php)

Initially, all the images data is retrieved from the database and listed in tabular view with CRUD links.

• The Add link allows performing the image upload operation.
• The Edit link allows performing the image update operation.
• The Delete link allows performing the image delete operation.
• The Status badge allows controlling the visibility of the images in the gallery.

<?php 
// Include and initialize DB class 
require_once 'DB.class.php'; 
$db = new DB(); 
 
// Fetch the images from database 
$db_images = $db->getRows(); 
 
// Get session data 
$sessData = !empty($_SESSION['sessData'])?$_SESSION['sessData']:''; 
 
// Get status message from session 
if(!empty($sessData['status']['msg'])){ 
    $statusMsg = $sessData['status']['msg']; 
    $statusMsgType = $sessData['status']['type']; 
    unset($_SESSION['sessData']['status']); 
} 
?>

<div class="row">
    <div class="col-md-12 mb-3 top">
        <h4>Images List</h4>

        <!-- Add link -->
        <div class="float-end">
            <a href="addEdit.php" class="btn btn-success"><i class="plus"></i> New Image</a>
        </div>
    </div>

    <!-- Display status message -->
    <?php if(!empty($statusMsg)){ ?>
    <div class="col-xs-12">
        <div class="alert alert-<?php echo $statusMsgType; ?>"><?php echo $statusMsg; ?></div>
    </div>
    <?php } ?>
    
    <!-- List the images -->
    <table class="table table-striped">
        <thead class="table-dark">
            <tr>
                <th width="5%">ID</th>
                <th width="18%">Image</th>
                <th width="40%">Title</th>
                <th width="14%">Created</th>
                <th width="8%">Status</th>
                <th width="15%">Action</th>
            </tr>
        </thead>
        <tbody>
            <?php 
            if(!empty($db_images)){ 
                foreach($db_images as $row){ 
                    $statusLink = ($row['status'] == 1)?'postAction.php?action_type=block&id='.$row['id']:'postAction.php?action_type=unblock&id='.$row['id']; 
                    $statusTooltip = ($row['status'] == 1)?'Click to Inactive':'Click to Active'; 
            ?>
            <tr>
                <td><?php echo '#'.$row['id']; ?></td>
                <td><img src="<?php echo UPLOAD_PATH.$row['file_name']; ?>" alt="..." /></td>
                <td><?php echo $row['title']; ?></td>
                <td><?php echo $row['created']; ?></td>
                <td>
                    <a href="<?php echo $statusLink; ?>" title="<?php echo $statusTooltip; ?>"><span class="badge <?php echo ($row['status'] == 1)?'text-bg-success':'text-bg-danger'; ?>"><?php echo ($row['status'] == 1)?'Active':'Inactive'; ?></span></a>
                </td>
                <td>
                    <a href="addEdit.php?id=<?php echo $row['id']; ?>" class="btn btn-warning btn-sm">edit</a>
                    <a href="postAction.php?action_type=delete&id=<?php echo $row['id']; ?>" class="btn btn-danger btn-sm" onclick="return confirm('Are you sure to delete data?')?true:false;">delete</a>
                </td>
            </tr>
            <?php } }else{ ?>
            <tr><td colspan="6">No image(s) found...</td></tr>
            <?php } ?>
        </tbody>
    </table>
</div>

Upload Image & Update Data (addEdit.php)

The addEdit.php file holds the HTML form to receive input from the user for add and edit image data.

• Initially, the form data is submitted to the PHP script (postAction.php) to insert file info and form data in the images table.
• If the ID parameter exists on the URL,
  • The existing image data will be retrieved from the database and the data is pre-filled in the input fields.
  • The data is submitted to the PHP script (postAction.php) to update the existing record in the images table.

<?php 
// Include and initialize DB class 
require_once 'DB.class.php'; 
$db = new DB(); 
 
// Retrieve session data  
$sessData = !empty($_SESSION['sessData'])?$_SESSION['sessData']:'';  
 
// Get status message from session 
if(!empty($sessData['status']['msg'])){ 
    $statusMsg = $sessData['status']['msg']; 
    $statusMsgType = $sessData['status']['type']; 
    unset($_SESSION['sessData']['status']); 
} 
 
// Get image data from database 
$imgData = array(); 
if(!empty($_GET['id'])){ 
    $conditions['where'] = array( 
        'id' => $_GET['id'] 
    ); 
    $conditions['return_type'] = 'single'; 
    $imgData = $db->getRows($conditions); 
} 
 
// Get posted data from session 
$postData = !empty($sessData['postData'])?$sessData['postData']:$imgData;  
unset($_SESSION['sessData']['postData']); 
 
// Define action 
$actionLabel = !empty($_GET['id'])?'Edit':'Add'; 
?>

<!-- Display status message -->
<?php if(!empty($statusMsg)){ ?>
<div class="col-xs-12">
    <div class="alert alert-<?php echo $statusMsgType; ?>"><?php echo $statusMsg; ?></div>
</div>
<?php } ?>

<div class="col-md-6">
    <form method="post" action="postAction.php" enctype="multipart/form-data">
        <div class="mb-3">
            <label class="form-label">Image</label>
            <?php if(!empty($imgData['file_name'])){ ?>
                <img src="<?php echo UPLOAD_PATH.$imgData['file_name']; ?>" alt="...">
            <?php } ?>
            <input type="file" name="image" class="form-control" <?php echo !empty($imgData['id'])?'':'required'; ?>>
        </div>
        <div class="mb-3">
            <label class="form-label">Title</label>
            <input type="text" name="title" class="form-control" placeholder="Enter title" value="<?php echo !empty($postData['title'])?$postData['title']:''; ?>" required>
        </div>
        
        <div class="mb-3">
            <input type="hidden" name="id" value="<?php echo !empty($imgData['id'])?$imgData['id']:''; ?>">

            <a href="manage.php" class="btn btn-secondary">Cancel</a>
            <input type="submit" name="imgSubmit" class="btn btn-primary" value="Submit">
        </div>
    </form>
</div>

Upload Image and Add, Edit, & Delete Records (postAction.php)

This file handles file upload and the CRUD operations using PHP and MySQL.

Add / Edit Form Submit:

• The submitted form data is validated to check the empty value.
• Check and validate the file extension using pathinfo() function and PHP.
• Upload image to server using move_uploaded_file() function in PHP.
• Insert or update image file name and form data in the MySQL database using PHP.
• The insert() and update() methods of the DB class is used to add/update data in the database.

Block Image (action_type=block):

• Update and set image status to 0 in the database.
• The update() method of the DB class is used to update status field value in the database.

Activate Image (action_type=unblock):

• Update and set image status to 1 in the database.
• The update() method of the DB class is used to update status field value in the database.

Delete Image (action_type=delete):

• Remove file from the directory of the server.
• Delete image data from the database.
• The delete() method of the DB class is used to delete image info from the database.

After the data manipulation, the status is stored in SESSION and redirect to the respective page.

<?php 
// Include and initialize DB class 
require_once 'DB.class.php'; 
$db = new DB(); 
 
// Allow file formats 
$allowTypes = array('jpg', 'png', 'jpeg', 'gif'); 
 
// Set default redirect url 
$redirectURL = 'manage.php'; 
 
$sessData = array(); 
 
if(isset($_POST['imgSubmit'])){ 
    // Get submitted data 
    $id    = $_POST['id']; 
    $image_file    = $_FILES['image']; 
    $title = trim(strip_tags($_POST['title'])); 
 
    $id_str = !empty($id)?'?id='.$id:''; 
    if(!empty($image_file['name'])){ 
        $fileName = basename($image_file["name"]); 
        $targetFilePath = UPLOAD_PATH . $fileName; 
        $fileType = pathinfo($targetFilePath, PATHINFO_EXTENSION); 
    } 
 
    // Input fields validation  
    $errorMsg = ''; 
    if(empty($id) && empty($image_file['name'])){ 
        $errorMsg .= 'Please select an image file to upload.<br>';  
    }elseif(!empty($image_file['name']) && !in_array($fileType, $allowTypes)){ 
        $errorMsg .= 'Sorry, only '.implode("/", $allowTypes).' files are allowed to upload.<br>';  
    } 
 
    if(empty($title)){  
        $errorMsg .= 'Please enter title.<br>';  
    } 
 
    // Submitted form data  
    $imgData = array(  
        'title'  => $title 
    );  
      
    // Store the submitted field value in the session  
    $sessData['postData'] = $imgData;  
      
    // Submit the form data  
    if(empty($errorMsg)){ 
        // Upload image to the server 
        if(!empty($image_file)){ 
            if(move_uploaded_file($image_file["tmp_name"], $targetFilePath)){ 
                $imgData['file_name'] = $fileName; 
            }else{ 
                $sessData['status']['type'] = 'danger'; 
                $sessData['status']['msg'] = 'Sorry, there was an error uploading your file.'; 
            } 
        } 
 
        if(!empty($id)){ 
            // Previous file info 
            $conditions['where'] = array( 
                'id' => $_GET['id'] 
            ); 
            $conditions['return_type'] = 'single'; 
            $prevData = $db->getRows($conditions); 
             
            // Update image data 
            $condition = array('id' => $id); 
            $update = $db->update($imgData, $condition); 
              
            if($update){  
                // Remove old file from server 
                if(!empty($prevData['file_name'])){ 
                    @unlink(UPLOAD_PATH.$prevData['file_name']); 
                } 
 
                $sessData['status']['type'] = 'success';  
                $sessData['status']['msg'] = 'Image data has been updated successfully.';  
                  
                // Remove submitted fields value from session  
                unset($sessData['postData']);  
            }else{  
                $sessData['status']['type'] = 'danger';  
                $sessData['status']['msg'] = 'Something went wrong, please try again.';  
                  
                // Set redirect url  
                $redirectURL = 'addEdit.php'.$id_str;  
            }  
        }else{  
            // Insert image data  
            $insert = $db->insert($imgData);  
              
            if($insert){  
                $sessData['status']['type'] = 'success';  
                $sessData['status']['msg'] = 'Image data has been added successfully.';  
                  
                // Remove submitted fields value from session  
                unset($sessData['postData']);  
            }else{  
                $sessData['status']['type'] = 'danger';  
                $sessData['status']['msg'] = 'Something went wrong, please try again.';  
                  
                // Set redirect url  
                $redirectURL = 'addEdit.php'.$id_str;  
            }  
        }  
    }else{  
        $sessData['status']['type'] = 'danger';  
        $sessData['status']['msg'] = 'Please fill all the mandatory fields:<br>'.trim($errorMsg, '<br>');  
          
        // Set redirect url  
        $redirectURL = 'addEdit.php'.$id_str;  
    } 
     
    // Store status into the session  
    $_SESSION['sessData'] = $sessData;  
}elseif(($_REQUEST['action_type'] == 'block') && !empty($_GET['id'])){ 
    // Update data 
    $imgData = array('status' => 0); 
    $condition = array('id' => $_GET['id']); 
    $update = $db->update($imgData, $condition); 
    if($update){ 
        $sessData['status']['type'] = 'success';  
        $sessData['status']['msg'] = 'Image data has been blocked successfully.';  
    }else{ 
        $sessData['status']['type'] = 'danger';  
        $sessData['status']['msg'] = 'Something went wrong, please try again.'; 
    } 
     
    // Store status into the session  
    $_SESSION['sessData'] = $sessData;  
}elseif(($_REQUEST['action_type'] == 'unblock') && !empty($_GET['id'])){ 
    // Update data 
    $imgData = array('status' => 1); 
    $condition = array('id' => $_GET['id']); 
    $update = $db->update($imgData, $condition); 
    if($update){ 
        $sessData['status']['type'] = 'success';  
        $sessData['status']['msg'] = 'Image data has been activated successfully.'; 
    }else{ 
        $sessData['status']['type'] = 'danger';  
        $sessData['status']['msg'] = 'Something went wrong, please try again.'; 
    } 
     
    // Store status into the session  
    $_SESSION['sessData'] = $sessData;  
}elseif(($_REQUEST['action_type'] == 'delete') && !empty($_GET['id'])){ 
    // Previous file name 
    $conditions['where'] = array( 
        'id' => $_GET['id'] 
    ); 
    $conditions['return_type'] = 'single'; 
    $prevData = $db->getRows($conditions); 
                 
    // Delete data 
    $condition = array('id' => $_GET['id']); 
    $delete = $db->delete($condition); 
    if($delete){ 
        // Remove old file from server 
        @unlink(UPLOAD_PATH.$prevData['file_name']); 
 
        $sessData['status']['type'] = 'success';  
        $sessData['status']['msg'] = 'Image data has been deleted successfully.';  
    }else{ 
        $sessData['status']['type'] = 'danger';  
        $sessData['status']['msg'] = 'Something went wrong, please try again.'; 
    } 
     
    // Store status into the session  
    $_SESSION['sessData'] = $sessData;  
} 
     
// Redirect to the respective page  
header("Location: ".$redirectURL);  
exit();  
?>

Dynamic Images Gallery View

In the Gallery View, we will fetch the data from the database and list the images with the title. To make the gallery user-friendly, the lightbox popup functionality will be integrated into the dynamic photo gallery using the fancyBox jQuery plugin.

fancyBox plugin:
The jQuery fancyBox plugin is used to show the image gallery on the popup. Include the jQuery library and CSS & JS files of the fancyBox library.

<!-- jQuery library -->
<script src="assets/js/jquery.min.js"></script>

<!-- Fancybox CSS & JS library -->
<link rel="stylesheet" href="assets/fancybox/fancybox.css">
<script src="assets/fancybox/fancybox.umd.js"></script>

Fetch all the images from the database and list them in gallery view using HTML and PHP.

• The image file data is fetched from the database using getRows() method of DB class.
• The images are retrieved from the server based on the respective file name.
• Specify additional attributes in the anchor tag of the images to enable the fancyBox popup.
  • Specify the large image file path in the href attribute.
  • Add data-fancybox="gallery" attribute.
  • Specify the image caption in data-caption attribute.

<?php 
// Include and initialize DB class 
require_once 'DB.class.php'; 
$db = new DB(); 
 
// Fetch the images from database  
$conditions = array( 
    'where' => array( 
        'status' => 1 
    ) 
); 
$db_images = $db->getRows($conditions); 
?>

<?php 
if(!empty($db_images)){ 
    foreach($db_images as $row){ 
        $imageURL = UPLOAD_PATH.$row["file_name"]; 
?>
<div class="col-sm-3 mb-3 mb-sm-0">
    <div class="card">
        <a href="<?php echo $imageURL; ?>" data-fancybox="gallery" data-caption="<?php echo $row["title"]; ?>" >
            <img src="<?php echo $imageURL; ?>" class="card-img-top" alt="...">
        </a>
        <div class="card-body">
            <h5 class="card-title"><?php echo $row["title"]; ?></h5>
        </div>
    </div>
</div>
<?php  
    } 
}  
?>

Use Fancybox.bind() method to initialize the fancyBox plugin and attach it to the HTML element in the image gallery.

<!-- Initialize Fancybox -->
<script>
Fancybox.bind("[data-fancybox]");
</script>

PHP CRUD Operations without Page Refresh using jQuery, Ajax, and MySQL

Conclusion

Image Gallery CRUD with PHP is very useful for building image gallery management functionality in the web application. Our example script helps you to integrate photo gallery manager with a lightbox gallery view using PHP and MySQL. You can easily enhance the functionality to make Image Gallery management system as per your needs.