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.
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,
`file_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT 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;
The DB class handles all the database related operations (connect, insert, update, and delete). Specify the database host ($dbHost
), username ($dbUsername
), password ($dbPassword
), and name ($dbName
) as per your MySQL database credentials.
<?php /* * DB Class * This class is used for database related (connect, insert, update, and delete) operations * @author CodexWorld.com * @url http://www.codexworld.com * @license http://www.codexworld.com/license */ class DB{ private $dbHost = "localhost"; private $dbUsername = "root"; private $dbPassword = "root"; private $dbName = "codexworld"; 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 * @param string name of the table * @param array select, where, order_by, limit and return_type conditions */ public function getRows($table, $conditions = array()){ $sql = 'SELECT '; $sql .= array_key_exists("select",$conditions)?$conditions['select']:'*'; $sql .= ' FROM '.$table; if(array_key_exists("where",$conditions)){ $sql .= ' WHERE '; $i = 0; foreach($conditions['where'] as $key => $value){ $pre = ($i > 0)?' AND ':''; $sql .= $pre.$key." = '".$value."'"; $i++; } } if(array_key_exists("order_by",$conditions)){ $sql .= ' ORDER BY '.$conditions['order_by']; }else{ $sql .= ' ORDER BY id DESC '; } if(array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){ $sql .= ' LIMIT '.$conditions['start'].','.$conditions['limit']; }elseif(!array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){ $sql .= ' LIMIT '.$conditions['limit']; } $result = $this->db->query($sql); if(array_key_exists("return_type",$conditions) && $conditions['return_type'] != 'all'){ switch($conditions['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($table, $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 ".$table." (".$columns.") VALUES (".$values.")"; $insert = $this->db->query($query); return $insert?$this->db->insert_id:false; }else{ return false; } } /* * Update data into the database * @param string name of the table * @param array the data for updating into the table * @param array where condition on updating data */ public function update($table, $data, $conditions){ 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($conditions)&& is_array($conditions)){ $whereSql .= ' WHERE '; $i = 0; foreach($conditions as $key => $value){ $pre = ($i > 0)?' AND ':''; $whereSql .= $pre.$key." = '".$value."'"; $i++; } } $query = "UPDATE ".$table." SET ".$colvalSet.$whereSql; $update = $this->db->query($query); return $update?$this->db->affected_rows:false; }else{ return false; } } /* * Delete data from the database * @param string name of the table * @param array where condition on deleting data */ public function delete($table, $conditions){ $whereSql = ''; if(!empty($conditions)&& is_array($conditions)){ $whereSql .= ' WHERE '; $i = 0; foreach($conditions as $key => $value){ $pre = ($i > 0)?' AND ':''; $whereSql .= $pre.$key." = '".$value."'"; $i++; } } $query = "DELETE FROM ".$table.$whereSql; $delete = $this->db->query($query); return $delete?true:false; } }
The Bootstrap is used to style the table, list, form fields, and links. Include the CSS file of the Bootstrap 4 library.
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css">
Initially, all the images data is retrieved from the database and listed in tabular view with CRUD links.
<?php
// Start session
session_start();
// Include and initialize DB class
require_once 'DB.class.php';
$db = new DB();
// Fetch the users data
$images = $db->getRows('images');
// 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']);
}
?>
<!-- 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="row">
<div class="col-md-12 head">
<h5>Images</h5>
<!-- Add link -->
<div class="float-right">
<a href="addEdit.php" class="btn btn-success"><i class="plus"></i> New Image</a>
</div>
</div>
<!-- List the images -->
<table class="table table-striped table-bordered">
<thead class="thead-dark">
<tr>
<th width="5%"></th>
<th width="12%">Image</th>
<th width="45%">Title</th>
<th width="17%">Created</th>
<th width="8%">Status</th>
<th width="13%">Action</th>
</tr>
</thead>
<tbody>
<?php
if(!empty($images)){
foreach($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 'uploads/images/'.$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)?'badge-success':'badge-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">edit</a>
<a href="postAction.php?action_type=delete&id=<?php echo $row['id']; ?>" class="btn btn-danger" 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>
The addEdit.php
file holds the HTML form to receive input from the user for add and edit image data.
<?php
// Start session
session_start();
$postData = $imgData = array();
// 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']);
}
// Get posted data from session
if(!empty($sessData['postData'])){
$postData = $sessData['postData'];
unset($_SESSION['sessData']['postData']);
}
// Get image data
if(!empty($_GET['id'])){
// Include and initialize DB class
require_once 'DB.class.php';
$db = new DB();
$conditions['where'] = array(
'id' => $_GET['id'],
);
$conditions['return_type'] = 'single';
$imgData = $db->getRows('images', $conditions);
}
// Pre-filled data
$imgData = !empty($postData)?$postData:$imgData;
// 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="row">
<div class="col-md-6">
<form method="post" action="postAction.php" enctype="multipart/form-data">
<div class="form-group">
<label>Image</label>
<?php if(!empty($imgData['file_name'])){ ?>
<img src="uploads/images/<?php echo $imgData['file_name']; ?>">
<?php } ?>
<input type="file" name="image" class="form-control" >
</div>
<div class="form-group">
<label>Title</label>
<input type="text" name="title" class="form-control" placeholder="Enter title" value="<?php echo !empty($imgData['title'])?$imgData['title']:''; ?>" >
</div>
<a href="manage.php" class="btn btn-secondary">Back</a>
<input type="hidden" name="id" value="<?php echo !empty($imgData['id'])?$imgData['id']:''; ?>">
<input type="submit" name="imgSubmit" class="btn btn-success" value="SUBMIT">
</form>
</div>
</div>
This file handles file upload and the CRUD operations using PHP and MySQL.
Add / Edit Form Submit:
Block Image (action_type => block):
Activate Image (action_type => unblock):
Delete Image (action_type => delete):
After the data manipulation, the status is stored in SESSION and redirect to the respective page.
<?php // Start session session_start(); // Include and initialize DB class require_once 'DB.class.php'; $db = new DB(); $tblName = 'images'; // File upload path $uploadDir = "uploads/images/"; // Allow file formats $allowTypes = array('jpg','png','jpeg','gif'); // Set default redirect url $redirectURL = 'manage.php'; $statusMsg = ''; $sessData = array(); $statusType = 'danger'; if(isset($_POST['imgSubmit'])){ // Set redirect url $redirectURL = 'addEdit.php'; // Get submitted data $image = $_FILES['image']; $title = $_POST['title']; $id = $_POST['id']; // Submitted user data $imgData = array( 'title' => $title ); // Store submitted data into session $sessData['postData'] = $imgData; $sessData['postData']['id'] = $id; // ID query string $idStr = !empty($id)?'?id='.$id:''; // If the data is not empty if((!empty($image['name']) && !empty($title)) || (!empty($id) && !empty($title))){ if(!empty($image)){ $fileName = basename($image["name"]); $targetFilePath = $uploadDir . $fileName; $fileType = pathinfo($targetFilePath,PATHINFO_EXTENSION); if(in_array($fileType, $allowTypes)){ // Upload file to server if(move_uploaded_file($image["tmp_name"], $targetFilePath)){ $imgData['file_name'] = $fileName; }else{ $statusMsg = "Sorry, there was an error uploading your file."; } }else{ $statusMsg = "Sorry, only JPG, JPEG, PNG, & GIF files are allowed to upload."; } } if(!empty($id)){ // Previous file name $conditions['where'] = array( 'id' => $_GET['id'], ); $conditions['return_type'] = 'single'; $prevData = $db->getRows('images', $conditions); // Update data $condition = array('id' => $id); $update = $db->update($tblName, $imgData, $condition); if($update){ // Remove old file from server if(!empty($imgData['file_name'])){ @unlink($uploadDir.$prevData['file_name']); } $statusType = 'success'; $statusMsg = 'Image data has been updated successfully.'; $sessData['postData'] = ''; $redirectURL = 'manage.php'; }else{ $statusMsg = 'Some problem occurred, please try again.'; // Set redirect url $redirectURL .= $idStr; } }elseif(!empty($imgData['file_name'])){ // Insert data $insert = $db->insert($tblName, $imgData); if($insert){ $statusType = 'success'; $statusMsg = 'Image has been uploaded successfully.'; $sessData['postData'] = ''; $redirectURL = 'manage.php'; }else{ $statusMsg = 'Some problem occurred, please try again.'; } } }else{ $statusMsg = 'All fields are mandatory, please fill all the fields.'; } // Status message $sessData['status']['type'] = $statusType; $sessData['status']['msg'] = $statusMsg; }elseif(($_REQUEST['action_type'] == 'block') && !empty($_GET['id'])){ // Update data $imgData = array('status' => 0); $condition = array('id' => $_GET['id']); $update = $db->update($tblName, $imgData, $condition); if($update){ $statusType = 'success'; $statusMsg = 'Image data has been blocked successfully.'; }else{ $statusMsg = 'Some problem occurred, please try again.'; } // Status message $sessData['status']['type'] = $statusType; $sessData['status']['msg'] = $statusMsg; }elseif(($_REQUEST['action_type'] == 'unblock') && !empty($_GET['id'])){ // Update data $imgData = array('status' => 1); $condition = array('id' => $_GET['id']); $update = $db->update($tblName, $imgData, $condition); if($update){ $statusType = 'success'; $statusMsg = 'Image data has been activated successfully.'; }else{ $statusMsg = 'Some problem occurred, please try again.'; } // Status message $sessData['status']['type'] = $statusType; $sessData['status']['msg'] = $statusMsg; }elseif(($_REQUEST['action_type'] == 'delete') && !empty($_GET['id'])){ // Previous file name $conditions['where'] = array( 'id' => $_GET['id'], ); $conditions['return_type'] = 'single'; $prevData = $db->getRows('images', $conditions); // Delete data $condition = array('id' => $_GET['id']); $delete = $db->delete($tblName, $condition); if($delete){ // Remove old file from server @unlink($uploadDir.$prevData['file_name']); $statusType = 'success'; $statusMsg = 'Image data has been deleted successfully.'; }else{ $statusMsg = 'Some problem occurred, please try again.'; } // Status message $sessData['status']['type'] = $statusType; $sessData['status']['msg'] = $statusMsg; } // Store status into the session $_SESSION['sessData'] = $sessData; // Redirect the user header("Location: ".$redirectURL); exit(); ?>
In the Gallery View, we will fetch the data from the database and listed the images with the title. Make the gallery user-friendly, the lightbox popup functionality will be integrated into the dynamic photo gallery using the fancyBox jQuery plugin.
PHP & HTML:
getRows()
method of DB class.data-fancybox="gallery"
attribute.data-caption
attribute.<?php
// Include and initialize DB class
require_once 'DB.class.php';
$db = new DB();
// Fetch the images data
$condition = array('where' => array('status' => 1));
$images = $db->getRows('images', $condition);
?>
<div class="gallery">
<?php
if(!empty($images)){
foreach($images as $row){
$uploadDir = 'uploads/images/';
$imageURL = $uploadDir.$row["file_name"];
?>
<div class="col-lg-3">
<a href="<?php echo $imageURL; ?>" data-fancybox="gallery" data-caption="<?php echo $row["title"]; ?>" >
<img src="<?php echo $imageURL; ?>" alt="" />
<p><?php echo $row["title"]; ?></p>
</a>
</div>
<?php }
} ?>
</div>
JavaScript:
The jQuery fancyBox plugin is used to show the image gallery on the popup. So, include the CSS and JS library of the fancyBox plugin.
<!-- Fancybox CSS library -->
<link rel="stylesheet" type="text/css" href="fancybox/jquery.fancybox.css">
<!-- Stylesheet file -->
<link rel="stylesheet" type="text/css" href="css/style.css">
<!-- jQuery library -->
<script src="js/jquery.min.js"></script>
<!-- Fancybox JS library -->
<script src="fancybox/jquery.fancybox.js"></script>
Call the fancybox()
method and specify a selector to bind the fancyBox event in the image gallery.
<!-- Initialize fancybox -->
<script>
$("[data-fancybox]").fancybox();
</script>
PHP CRUD Operations without Page Refresh using jQuery, Ajax, and MySQL
Image Gallery CRUD with PHP is very useful to build the image 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.
Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request
can i use it for videos
Hi,
I really dont need the columns “created” and “modified”, what should i remove from DB.class.php?
How is your css file? Thanks
Download the source code, all files are included in it including CSS file.
Hello! How do I add this into a Codeigniter based site?