To Upload Store Retrieve and Delete Images to and from the database by using procedural PHP and MySQLi is shown in this tutorial. To understand the code make sure you have gone through our previous tutorial basic image upload to the folder. Uploading images in MySQL database is not a good idea because you first need skills to convert that images into blob (Binary Large Object) and then you can upload it in MySQL and when you are going to retrieve it you need to again convert it in original form. So In this tutorial we are providing you efficient way to upload images on your server and save it’s with format in database when you want to retrieve the image just fetch the image name from database and link with your path.
1. Configure ‘php.ini’ File
Before moving to the coding part , You must have to configure your php.ini file (location of this file varies as per platform). In your php.ini file, search for the ‘file_uploads’ directive, and check whether is it set to On. This control whether to allow HTTP file uploads.
2. Create Database
To store the image title and image name a table need to create in the database. So lets create the database by using following queries. The following SQL create image table which consist of id, title, imagename, time details.
CREATE TABLE `image` ( `id` int(11) NOT NULL, `title` varchar(500) NOT NULL, `imagename` text NOT NULL, `time` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
3.Database Configuration
First of all, to make it convenient, we will create a new PHP file for database configuration named dbconfig.php that holds all configured parameters:
<?php
$con = mysqli_connect("localhost","root","","technopoints");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>
4. Create User Interface
Create HTML Form to Upload Image
Now Its time to Create an HTML form index.html to allow the user to choose a Image they want to upload
Ensure <form> contains the following attributes.
- method=”post”
- enctype=”multipart/form-data”
- and input type=”file”
<!DOCTYPE>
<html>
<title>IMAGE UPLOADING PAGE</title>
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-compatible" content="IE-edge">
<meta name="viewport" content="width=device-width">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script src="index.css"></script>
<style>
input[type=text] {
width: 95%;
padding: 7px 15px;
margin: 3px 0;
display: inline-block;
border: 1px solid #ccc;
border-radius: 4px;
box-sizing: border-box;
}
input[type=submit]{
padding: 10px 20px;
background-color: white;
color: black;
border: 2px solid #555555;
}
input[type=submit]:hover {
align:center;
background-color: #555555;
color: white;
}
.cck {
display: block;
margin-right: auto;
margin-left: auto;
}
body {
background-color:rgba(100,200,255,0.5);
}
</style>
</head>
<body><div style="background-color:rgba(100,200,255,0.5);" align="center"><h2></h2></div>
<br><br>
<div style="background-color:rgba(100,200,255,0.5);" align="center">
<form action="uploading.php" method="post" enctype="multipart/form-data">
<h2>Image Uploading Section |<a href="">Technopoints</a></h2><br><br>
<table>
<tr><td height="70" width="100">Title</td><td><input type="text" required="required" name="ntitle" class="resizedTextbox"><br><br></td></tr>
<tr><td colspan="2"><input type="file" class="cck" name="sfile" id="fileName" accept="image/*"><br><br></td></tr>
<tr><td colspan="2"><input class="cck" type="submit" name="notification" value="Upload"/></td></tr>
</table>
</form>
</div>
</body>
</html>
this file index.php will send data to uploading.php to upload the image data into the database.
CSS for the HTML Form
Add styling to the HTML form (index.html) by using CSS to make the content pretty and save the file by name index.css
input[type=text] {
width: 95%;
padding: 7px 15px;
margin: 3px 0;
display: inline-block;
border: 1px solid #ccc;
border-radius: 4px;
box-sizing: border-box;
}
input[type=submit]{
padding: 10px 20px;
background-color: white;
color: black;
border: 2px solid #555555;
}
input[type=submit]:hover {
align:center;
background-color: #555555;
color: white;
}
.cck {
display: block;
margin-right: auto;
margin-left: auto;
}
body {
background-color:rgba(100,200,255,0.5);
}
Now place the two files in the same folder. Remember to save the files with the right extensions (respectively “.htm” and “.css”) . The combined view of the both HTML and CSS is illustrate in the following snapshot.
5. Upload Store Image into Server
Now its time to process the data uploaded by the user, this code is written in uploading.php It will store the uploaded file in a upload folder on permanent basis as well as implement some basic security check like file type and file size to ensure that users upload the correct file type and within the allowed limit.
<?php
require 'dbconfig.php';
session_start();
$_SESSION['firstname'] = "Technopoints";
if($con == FALSE)
{
echo "connection is not done";
}
if(isset($_POST['notification'])){
$title = $_POST['ntitle'];
$uploadtime = date("Y-m-d H:i:s");
$cname = $_FILES['sfile']['name'];
}
if(!empty($cname)) {
$tname = $_FILES['sfile']['tmp_name'];
$tname = $_FILES['sfile']['tmp_name'];
$size = $_FILES['sfile']['size'];
$name = $_SESSION['firstname'].date("his");
$fext = pathinfo($cname, PATHINFO_EXTENSION);
$fire = pathinfo($name,PATHINFO_FILENAME);
$savename = $fire.".".$fext;
$finalfile = "upload/$savename";
}
if(!empty($cname)){
if($size < 500000){
check = move_uploaded_file($tname,$finalfile);
if($check){
$qry1 = "INSERT INTO `image`(`title`, `imagename`, `time`) VALUES ('$title','$savename','$uploadtime')";
$test = mysqli_query($con,$qry1);
if($test = TRUE){?>if(confirm("File Uploaded Sucessfully!!!"))
{ document.location = 'index.php';
}
else
{
document.location = 'index.php';
}<?php
}
else {?>if(confirm("file is not uploaded"))
{ document.location = 'index.php';
}
else
{
document.location = 'index.php';
}<?php
//echo "file is not uploaded";
}
}
}
else{
echo "file size is too large";
}
}else
{
echo "Please select an file to upload";
}
?>
You might be wondering what this code is all about, So here is the explanation of the code:
- $_FILES[‘sfile’][‘size’]; checks whether the file is larger than 500KB, If it is, an error message is displayed.
- pathinfo($cname, PATHINFO_EXTENSION); function is use to get the file extension and to validate the file format to check whether the user selects an image file.
- pathinfo($name,PATHINFO_FILENAME); function is use to set filename.
- move_uploaded_file() function is used to uploads the selected file to the selected location. In our code the image is uploading to upload folder.
- date() function is concatenates with the name to set unique file name each time user uploads the image.
6. Retrieve And View Images From the Server
Now retrieve the uploaded images from the server based on the file names in the database and display images with its title in the web page.
<html>
<?php require 'dbconfig.php'; ?>
<title></title>
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<div>
<table class="table table-bordered" border="2">
<tr> <td colspan="5"><center><h3>Previously Uploaded | <a href="https://technopoints.co.in/">Technopoints</a></h3></center></td></tr>
<tr>
<tr><center><th>Description</th><th>Banner Thumbnail</th><th>Action</th></center></tr>
<?php $fetchqry = "SELECT * FROM `image` ORDER BY time DESC";
$result=mysqli_query($con,$fetchqry);
$num=mysqli_num_rows($result);
if($num > 0){
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){ ?>
<tr><td><p><?php echo $row['title'];?></p></td><td><img src="upload/<?php echo $row['imagename']?>" width="450" height="200" /> </td>
<td width="10%"><input type="button" class="btn" value="Delete" id="button" onclick="deleteme(<?php echo $row['id']; ?>)"></td></tr>
<script language="javascript">
function deleteme(delid)
{
window.location.href='deleteimage.php?del_id='+delid+'';
return true;
}
</script>
<?php
}
}
?>
</tr>
</table>
</div>
</body>
</html>
User can able to delete the image entry from the database by clicking the delete button provided in an interface As shown in the snapshot below:
7. Deleting image entries from the database
To delete the particular image entry from the database we use the id attribute, $_GET method is used for the previous page input to identify which delete button is clicked by the user. After successfully deleting the data user got notified by pop-up and redirect to the show.php interface again
<?php
include 'dbconfig.php';
$fetchqry = "delete from `image` where id='".$_GET['del_id']."'";
$result=mysqli_query($con,$fetchqry);
if($result==TRUE){ ?>
alert("Data deleted!!!");
window.location = "show.php";
<?php
}
?>
That’s it. Finally You Learn to Upload Store Retrieve and Delete Images to and from the database by using procedural PHP and MySQLi. If you have any doubts regarding to this tutorials feel free to ask in the comment section. To catch even more knowledgeble and easy understandable tutorials please subcribe to our email notification.
6 thoughts on “Upload Store Retrieve and Delete Images using PHP and MySQLi”