Any dynamic web application has to be able to perform CRUD activities. It aids in the coordination of data between the application and the database. PHP and MySQL make it simple to implement CRUD (Create, Read, Update, and Delete) capabilities. Not only the data, but also the picture upload functionality, may be maintained using PHP CRUD operations. Using PHP CRUD with MySQL, you may manage the images in the gallery (add, modify, and delete).
If you want to make a dynamic image gallery, you can use the CRUD functionality to manage the images in the database. Image Gallery CRUD is a handy tool for integrating a dynamic image gallery into a website’s admin panel. In this lesson, we’ll teach you how to use PHP and MySQLi to upload, add, modify, and delete photographs in a dynamic gallery.
In the image gallery CRUD script, the following features will be implemented.
Obtain image data from the database and display it on the web page.
Add data to the database and upload an image.
In the database, edit and update image data.
Delete the database’s picture data.
With a lightbox, this is a dynamic image gallery.
Make a database table.
A table in the database must be built to store the file information. In the MySQL database, the following SQL creates a pictures table with some basic fields.
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 Type (DB.class.php)
The DB class is in charge of all database operations (connect, insert, update, and delete). According to your MySQL database credentials, specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName).
__construct() — Use PHP and the MySQLi Extension to connect to a database.
getRows() — Get records from a database based on the conditions you specify.
Insert data into the database via insert().
update() — Inserts new information into the database.
delete() — Removes information from a 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;
}
}The Bootstrap Library is a collection of HTML5 and CSS3
The table, list, form fields, and links are all styled with Bootstrap. Include the Bootstrap 4 library’s CSS file.
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css">List of Photographs (manage.php)
Initially, the database is queried for all image data, which is then shown in a tabular format with CRUD links.
The Add link allows you to execute an image upload.
The Edit link allows you to execute an image update.
The Delete link allows you to remove an image from your computer.
The Status badge allows you to decide which photographs in the gallery are seen.
<?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>Update Data & Upload Image (addEdit.php)
The HTML form for receiving user input for adding and editing picture data is stored in the addEdit.php file.
The form data is initially submitted to the PHP script (postAction.php), which inserts file information and form data into the pictures table.
The existing picture data will be retrieved from the database and pre-filled in the input fields if the ID parameter is present on the URL.
The data is sent to the postAction.php PHP script, which updates an existing record in the pictures 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>Add, Edit, and Delete Records with an Image (postAction.php)
Using PHP and MySQL, this file handles file upload and CRUD activities.
Submit: Add / Edit Form
The data from the submitted form is checked for empty values.
Using the pathinfo() function with PHP, check and validate the file extension.
Using PHP’s move uploaded file() method, upload a picture to the server.
PHP is used to insert or change image file names and form data in the MySQL database.
The DB class’s insert() and update() methods are used to add and update data in the database.
Image of a block (action type => block):
In the database, update and change the image status to 0.
The DB class’s update() method is used to update the database’s status field value.
(action type => unblock) Activate Image:
In the database, update and set picture status to 1.
The DB class’s update() method is used to update the database’s status field value.
Image deletion (action type => delete):
Remove the file from the server’s directory.
Delete the database’s picture data.
The DB class’s delete() method is used to remove picture data from the database.
Following the data manipulation, the status is saved in SESSION and the user is redirected to the appropriate 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();
?>View the Gallery of Dynamic Images
We’ll get data from the database and list the images with titles in the Gallery View. To make the gallery more user-friendly, the fancyBox jQuery plugin will be used to integrate the lightbox popup capability into the dynamic photo gallery.
HTML & PHP:
The data for the picture file is retrieved from the database using the DB class’s getRows() method.
The photos are obtained from the server using the file names as a guide.
To enable the fancyBox popup, add additional attributes to the anchor tag of the pictures.
In the href attribute, specify the location of the huge picture file.
The data-fancybox=”gallery” element should be added.
In the data-caption attribute, specify the image caption.
<?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>The image gallery on the popup is displayed using JavaScript and the jQuery fancyBox plugin. As a result, include the fancyBox plugin’s CSS and JS libraries.
<!-- 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>To bind the fancyBox event in the image gallery, use the fancybox() method and supply a selector.
<!-- Initialize fancybox -->
<script>
$("[data-fancybox]").fancybox();
</script>Note :
Image Gallery CRUD in PHP is quite handy for creating image management capabilities in a web site. Using PHP and MySQL, our example script shows how to connect a picture gallery manager with a lightbox gallery view. You may easily customise the features to create an Image Gallery management system that meets your specific requirements.