CRUD operations are the crucial functionality for any dynamic web application. Therefore, it helps to accomplish data amongst the application and the database. So, the CRUD (Create, Read, Update, and Delete) functionality can be applied using PHP and MySQL. Firstly, with PHP CRUD operations, You can not only manage the data but also you can manage the image upload functionality. Secondly, you can add, edit, and delete the images in the gallery using PHP CRUD with MySQL.
If you want to create a dynamic image gallery, CRUD functionality helps to manage the images with the database. Likewise, Image Gallery CRUD is very convenient to assimilate dynamic image galleries on the website with the admin panel. So, with the help of this tutorial, we will explain to you how to upload, add, edit, and delete images with a dynamic gallery using PHP and MySQLi.
We will implement the functionality in the image gallery CRUD script.
- Fetch the images data from the database and listed on the web page.
- Add data to the database and upload an image.
- Now, edit and update image data in the database.
- After then, remove image data from the database.
- Dynamic image gallery with lightbox.
Make Database Table
It would be best if you create a table in the database to store the file information. Therefore, the following SQL generates an image table with some essential 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;
Database Class (DB.class.php)
The DB class handles all the database related operations (connect, insert, update, and delete). So, stipulate the database host ($dbHost), username ($dbUsername), password($dbPassword), and name ($dbName) as per your MySQL database credentials.
- __construct() – To connect to the database with PHP and MySQLi Extension.
- getRows() – To fetch records from the database based on the specified conditions.
- insert() – To Insert data.
- update() – To update data.
- delete() – To delete data.
<?php
/*
* DB Class
* This class is used for database related (connect, insert, update, and delete) operations
* @author Allsweb.com
* @url http://www.allsweb.com
* @license http://www.allsweb.com/license
*/
class DB{
private $dbHost = "localhost";
private $dbUsername = "root";
private $dbPassword = "root";
private $dbName = "allsweb";
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;
}
}
Bootstrap Library
We can use the Bootstrap library to style the table, list, form fields, and links. Attach the CSS file of the Bootstrap 4 library.
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css">
SEE ALSO: Multiple Image Upload with View, Edit and Delete in PHP
Images List (manage.php)
Primarily, all the images data is recovered from the database and listed in a tabular view with CRUD links.
- Add link permits you to perform the image upload operation.
- Edit link permits you to perform the image update operation.
- Delete link permits you to perform the image delete operation.
- Status badge permits controlling the visibility of the images in the gallery.
<?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>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.
- Firstly, the form data is submitted to the PHP script (postAction.php) to insert file info and form data in the images table.
- Secondly, if the ID parameter exists on the URL,
- Retrieve the existing image data 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
// 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>SEE ALSO: Simple User Registration & Login Script in PHP and MySQLi
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.
- You can use the insert() and update() methods of the DB class to add/update data in the database.
Block Image (action_type => block):
- Update and set image status to 0 in the database.
- You can use the update() method of the DB class to update the status field value in the database.
Activate Image (action_type => unblock):
- Update and set image status to 1 in the database.
- You can use the update() method of the DB class to update the status field value in the database.
Delete Image (action_type => delete):
- Firstly, remove the file from the directory of the server.
- Secondly, delete image data from the database.
- Additionaly, you can use the delete() method of the DB class to delete image info from the database.
After data manipulation, the status is stored in the session and redirected to the corresponding 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();
?>Dynamic Images Gallery View
In the gallery view, we will create data from the database and list the images with the title. Make the gallery user-friendly, and integrate the Lightbox popup functionality into the dynamic photo gallery using the Fancybox jQuery plugin.
PHP & HTML:
- By using
getRows()method of DB class we can fetch the image file data - Retrieve the images from the server grounded on the above 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.
- Now, Add data-fancybox=”gallery” attribute.
- Specify the image caption in the 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:
You can use the jQuery fancyBox plugin to show the image gallery on the popup. Therefore, 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 stipulate a selector to bind the fancyBox event in the image gallery.
<!-- Initialize fancybox -->
<script>
$("[data-fancybox]").fancybox();
</script>Conclusion
Firstly, the image Gallery CRUD with PHP is very beneficial to figure the image management functionality in the web application. Secondly, our example script benefits you to integrate photo gallery manager with a lightbox gallery view using PHP and MySQL. Therefore, you can improve the functionality to make the Image Gallery management system according to your requirements.
Also, read our previous blog- Upload and Add Watermark to Image using PHP
