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.
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,
`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). Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.
- __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, 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; } }
Bootstrap Library
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">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
// 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.
- 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
// 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>
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 // 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 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:
- 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-captionattribute.
<?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>
No comments:
Post a Comment