How to Upload, Insert, Update and Delete File Using PHP PDO & MySQL - onlyxcodes

Thursday 12 April 2018

How to Upload, Insert, Update and Delete File Using PHP PDO & MySQL

In this tutorial, you will get codes of How to Upload, Insert, Update & Delete files using PHP PDO & MySQL. PHP makes it easy to upload any file in the Server folder path and store the file name uploaded to the database. We can upload Video, Word, PDF, Image, Excel, etc. files.


Before file uploading, this tutorial covered the beneficial validation hub of file uploading sees below.


File allowed extension required such as JPG, PNG, GIF, etc. We are not allowed to have the same files. Limited file size required. Verify whether the chosen new file exists from the server directory or not.


Additionally, the old file will permanently remove from the server folder path at the time of updating an existing file, including deleting MySQL. That features will save spaces in your server.


How to Upload, Insert, Update & Delete file using PHP PDO & MySQL Database.

Project Directory

See below the structure of the project directory located in the folder path C:\xampp\htdocs. Because I have the XAMPP server in C drive installed.


PHP PDO File Upload Project Directory Structure Within xampp/htdoc folder

Database and Table Design

Firstly, construct a database and table as seen below. You can build it by importing it to your PHPMyAdmin following the SQL command.


--
-- Database: `db_fileupload`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_file`
--

CREATE TABLE `tbl_file` (
  `id` int(11) NOT NULL,
  `name` varchar(12) NOT NULL,
  `image` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now we have only 4 PHP files to build that will perform our CRUD file operations and they are as follows.


connection.php

We build the configuration of a database in this file. Below PHP codes for open MySQL database connection wrap with try/catch block using PDO extension.



<?php
$db_host="localhost"; //localhost server 
$db_user="root"; //database username
$db_password=""; //database password   
$db_name="db_fileupload"; //database name

try
{
 $db=new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_password);
 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOEXCEPTION $e)
{
 $e->getMessage();
}

?>

Bootstrap  – I said in this tutorial I use the bootstrap package to develop form, buttons and alert messages using that package.


Note – But I only put main codes, all the codes in the project zip file go to a tutorial end and download a zip file.


add.php

Using bootstrap created HTML form and taking two fields to upload file name and input type file allow you to pick a file that you want to upload.


Here <form> tag contain two attributes.


  • method="post" – This attribute sends your request to the server for file upload using the HTTP POST method.

  • enctype="multipart/form-data" – Enctype is the type of encoding that specifies the form-data should be encoded as MIME multipart when submitting the form. Which is required to upload large amounts of differential data like video, images, etc.


<form method="post" class="form-horizontal" enctype="multipart/form-data">
     
 <div class="form-group">
 <label class="col-sm-3 control-label">Name</label>
 <div class="col-sm-6">
 <input type="text" name="txt_name" class="form-control" placeholder="enter name" />
 </div>
 </div>
         
 <div class="form-group">
 <label class="col-sm-3 control-label">File</label>
 <div class="col-sm-6">
 <input type="file" name="txt_file" class="form-control" />
 </div>
 </div>
         
 <div class="form-group">
 <div class="col-sm-offset-3 col-sm-9 m-t-15">
 <input type="submit"  name="btn_insert" class="btn btn-success " value="Insert">
 <a href="index.php" class="btn btn-danger">Cancel</a>
 </div>
 </div>
     
</form>

Add Form Visually like Below :


New File Upload Form

PHP New File Upload Codes :

The specifics of the code file will be inserted into the table including a store in the folder path by following. If any error occurred then a bootstrap alert message box will show the appropriate message.



<?php

require_once "connection.php";

if(isset($_REQUEST['btn_insert']))
{
 try
 {
  $name = $_REQUEST['txt_name']; //textbox name "txt_name"
   
  $image_file = $_FILES["txt_file"]["name"];
  $type  = $_FILES["txt_file"]["type"]; //file name "txt_file" 
  $size  = $_FILES["txt_file"]["size"];
  $temp  = $_FILES["txt_file"]["tmp_name"];
  
  $path="upload/".$image_file; //set upload folder path
  
  if(empty($name)){
   $errorMsg="Please Enter Name";
  }
  else if(empty($image_file)){
   $errorMsg="Please Select Image";
  }
  else if($type=="image/jpg" || $type=='image/jpeg' || $type=='image/png' || $type=='image/gif') //check file extension
  { 
   if(!file_exists($path)) //check file not exist in your upload folder path
   {
    if($size < 5000000) //check file size 5MB
    {
     move_uploaded_file($temp, "upload/" .$image_file); //move upload file temperory directory to your upload folder
    }
    else
    {
     $errorMsg="Your File To large Please Upload 5MB Size"; //error message file size not large than 5MB
    }
   }
   else
   { 
    $errorMsg="File Already Exists...Check Upload Folder"; //error message file not exists your upload folder path
   }
  }
  else
  {
   $errorMsg="Upload JPG , JPEG , PNG & GIF File Formate.....CHECK FILE EXTENSION"; //error message file extension
  }
  
  if(!isset($errorMsg))
  {
   $insert_stmt=$db->prepare('INSERT INTO tbl_file(name,image) VALUES(:fname,:fimage)'); //sql insert query     
   $insert_stmt->bindParam(':fname',$name); 
   $insert_stmt->bindParam(':fimage',$image_file);   //bind all parameter 
  
   if($insert_stmt->execute())
   {
    $insertMsg="File Upload Successfully........"; //execute query success message
    header("refresh:3;index.php"); //refresh 3 second and redirect to index.php page
   }
  }
 }
 catch(PDOException $e)
 {
  echo $e->getMessage();
 }
}

?>

New File Upload Codes Logic :

All codes explain inside in try/catch block.


Row no 10 to 15 – The attribute value of the txt_name store in the $name variable is the name of the text box field.


File field name attribute value of txt_file store in PHP superglobal array $_FILES [ ]. This array gets a name, type, size and temporary name of your uploaded file details.


$image_file = $_FILES["txt_file "][ "name "] :- This array value specifies the original name of the file.


$type = $_FILES["txt_file "][ "type "] :- This array value specifies the MIME type of the file like image, video, etc.


$size = $_FILES["txt_file "][ "size "] :- This value of the array specifies in bytes the file size.


$image_file = $_FILES["txt_file "][ "tmp_name "] :- It array gets the temporary location that is uploaded to the file. Use that details to move the uploaded file into the path of our folder.


$name = $_REQUEST['txt_name']; //textbox name "txt_name"
   
$image_file = $_FILES["txt_file"]["name"];
$type  = $_FILES["txt_file"]["type"]; //file name "txt_file" 
$size  = $_FILES["txt_file"]["size"];
$temp  = $_FILES["txt_file"]["tmp_name"]; 

Row no 17 – Declare the variable $path to save the absolute folder path where the server image will be stored.


$path="upload/".$image_file; //set upload folder path 

Row no 19 to 24 – if and else if condition, the empty() function checks that both form fields are not empty.


if(empty($name)){
 $errorMsg="Please Enter Name";
}
else if(empty($image_file)){
 $errorMsg="Please Select Image";
}

Row no 25 – else if condition, Check image must be the extension jpg, jpeg, png, and gif.


else if($type=="image/jpg" || $type=='image/jpeg' || $type=='image/png' || $type=='image/gif') //check file extension
{

Row no 27 – Here the function file_exists() checks whether the uploaded file exists on the server or not.


Row no 29 to 32 – if condition check file size must be 5 MB.


if above both conditions are true then move_uploaded_file() function move the file from the temporary location to the actual upload folder path.



if(!file_exists($path)) //check file not exist in your upload folder path
{
 if($size < 5000000) //check file size 5MB
 {
  move_uploaded_file($temp, "upload/" .$image_file); //move upload file temperory directory to your upload folder
 }
 else
 {
  $errorMsg="Your File To large Please Upload 5MB Size"; //error message file size not large than 5MB
 }
}
else
{ 
 $errorMsg="File Already Exists...Check Upload Folder"; //error message file not exists your upload folder path
} 

Row no 48 to 59 – if condition, The isset() function checks that the $errorMsg variable does not detect any errors, and applies PDO insert query in prepare() statements.


bindParam() function binds the place of the variable :fname and :fimage value in the query. Variable $name and variable $image_file carry both value.


Finally, the execute() function executes the statement, displays the file upload message successfully and the header() function keeps this message at 3 seconds and sends it to index.php page.


if(!isset($errorMsg))
{
 $insert_stmt=$db->prepare('INSERT INTO tbl_file(name,image) VALUES(:fname,:fimage)'); //sql insert query     
 $insert_stmt->bindParam(':fname',$name); 
 $insert_stmt->bindParam(':fimage',$image_file);   //bind all parameter 

 if($insert_stmt->execute())
 {
  $insertMsg="File Upload Successfully........"; //execute query success message
  header("refresh:3;index.php"); //refresh 3 second and redirect to index.php page
 }
} 

Note:- I've no else conditioned to explain you see that all else conditions detect specific error messages assign this message to the variable $errorMsg.


edit.php

Below constantly edit form the same as add form, if we modify particular data then we need to get picked data from the database. The value attribute displaying the file field image and also the name field of the text box.


 <form method="post" class="form-horizontal" enctype="multipart/form-data">
     
 <div class="form-group">
 <label class="col-sm-3 control-label">Name</label>
 <div class="col-sm-6">
 <input type="text" name="txt_name" class="form-control" value="<?php echo $name; ?>" required/>
 </div>
 </div>
     
  
 <div class="form-group">
 <label class="col-sm-3 control-label">File</label>
 <div class="col-sm-6">
 <input type="file" name="txt_file" class="form-control" value="<?php echo $image; ?>"/>
 <p><img src="upload/<?php echo $image; ?>" height="100" width="100" /></p>
 </div>
 </div>
     
     
 <div class="form-group">
 <div class="col-sm-offset-3 col-sm-9 m-t-15">
 <input type="submit"  name="btn_update" class="btn btn-primary" value="Update">
 <a href="index.php" class="btn btn-danger">Cancel</a>
 </div>
 </div>
     
</form>

Edit Form Visually Like Below :


File Edit or Updating Form

PHP File Edit Codes :

If we edit old image and name then delete the previous image on the server folder permanently below codes and upload a new image including detailed updated on the database.


The intention of the codes below to save your server directory space, because these codes permanently remove your old image at the time of image editing and that information.


<?php

require_once "connection.php";

if(isset($_REQUEST['update_id']))
{
 try
 {
  $id = $_REQUEST['update_id']; //get "update_id" from index.php page through anchor tag operation and store in "$id" variable
  $select_stmt = $db->prepare('SELECT * FROM tbl_file WHERE id =:id'); //sql select query
  $select_stmt->bindParam(':id',$id);
  $select_stmt->execute(); 
  $row = $select_stmt->fetch(PDO::FETCH_ASSOC);
  extract($row);
 }
 catch(PDOException $e)
 {
  $e->getMessage();
 }
 
}

if(isset($_REQUEST['btn_update']))
{
 try
 {
  $name =$_REQUEST['txt_name']; //textbox name "txt_name"
  
  $image_file = $_FILES["txt_file"]["name"];
  $type  = $_FILES["txt_file"]["type"]; //file name "txt_file"
  $size  = $_FILES["txt_file"]["size"];
  $temp  = $_FILES["txt_file"]["tmp_name"];
   
  $path="upload/".$image_file; //set upload folder path
  
  $directory="upload/"; //set upload folder path for update time previous file remove and new file upload for next use
  
  if($image_file)
  {
   if($type=="image/jpg" || $type=='image/jpeg' || $type=='image/png' || $type=='image/gif') //check file extension
   { 
    if(!file_exists($path)) //check file not exist in your upload folder path
    {
     if($size < 5000000) //check file size 5MB
     {
      unlink($directory.$row['image']); //unlink function remove previous file
      move_uploaded_file($temp, "upload/" .$image_file); //move upload file temperory directory to your upload folder 
     }
     else
     {
      $errorMsg="Your File To large Please Upload 5MB Size"; //error message file size not large than 5MB
     }
    }
    else
    { 
     $errorMsg="File Already Exists...Check Upload Folder"; //error message file not exists your upload folder path
    }
   }
   else
   {
    $errorMsg="Upload JPG, JPEG, PNG & GIF File Formate.....CHECK FILE EXTENSION"; //error message file extension
   }
  }
  else
  {
   $image_file=$row['image']; //if you not select new image than previous image sam it is it.
  }
 
  if(!isset($errorMsg))
  {
   $update_stmt=$db->prepare('UPDATE tbl_file SET name=:name_up, image=:file_up WHERE id=:id'); //sql update query
   $update_stmt->bindParam(':name_up',$name);
   $update_stmt->bindParam(':file_up',$image_file); //bind all parameter
   $update_stmt->bindParam(':id',$id);
    
   if($update_stmt->execute())
   {
    $updateMsg="File Update Successfully......."; //file update success message
    header("refresh:3;index.php"); //refresh 3 second and redirect to index.php page
   }
  }
 }
 catch(PDOException $e)
 {
  echo $e->getMessage();
 }
 
}
?>

File Edit Codes Logic :

Row no 5 to 19 – Before we edit an image and that details this codes getting specific record id from the GET request, this will determine which record we are going to edit.


Here we get the variable update_id through a hyperlink, which selects a particular table Id.


if(isset($_REQUEST['update_id']))
{
 try
 {
  $id = $_REQUEST['update_id']; //get "update_id" from index.php page through anchor tag operation and store in "$id" variable
  $select_stmt = $db->prepare('SELECT * FROM tbl_file WHERE id =:id'); //sql select query
  $select_stmt->bindParam(':id',$id);
  $select_stmt->execute(); 
  $row = $select_stmt->fetch(PDO::FETCH_ASSOC);
  extract($row);
 }
 catch(PDOException $e)
 {
  $e->getMessage();
 }
 
} 

Discussion codes Inside the try/catch block.


Row no 27 to 32 – See below I already explain $_FILES[] super global PHP array on new file upload logic. So I've skipped these codes explanation.


$name =$_REQUEST['txt_name']; //textbox name "txt_name"
  
$image_file = $_FILES["txt_file"]["name"];
$type  = $_FILES["txt_file"]["type"]; //file name "txt_file"
$size  = $_FILES["txt_file"]["size"];
$temp  = $_FILES["txt_file"]["tmp_name"]; 

Row no 34 – To save the server's absolute image path, declare the $path variable; it will verify that the same file name exists or not.


$path="upload/".$image_file; //set upload folder path 

Row no 36 – Create a $directory variable to save the absolute directory path where the server image will be stored.


It will be check uploaded new file or image already exists or not.


$directory="upload/"; //set upload folder path for update time previous file remove and new file upload for next use 

Row no 38 – if condition returns the true and false result of image file selection.


if($image_file)
{

Row no 40 – if condition, Scan image must include jpg, jpeg, png, and gif extension.


if($type=="image/jpg" || $type=='image/jpeg' || $type=='image/png' || $type=='image/gif') //check file extension
{

Row no 42 – Here the file_exists() function searches if the new file uploaded exists on the server or not.


Row no 44 – if condition check file size must be 5 MB.


If both conditions above are valid then unlink() function will permanently remove old file from the actual folder path and move_uploaded_file() function will move the file from the temporary path to the actual upload folder path.


if(!file_exists($path)) //check file not exist in your upload folder path
{
 if($size < 5000000) //check file size 5MB
 {
  unlink($directory.$row['image']); //unlink function remove previous file
  move_uploaded_file($temp, "upload/" .$image_file); //move upload file temperory directory to your upload folder 
 }
 else
 {
  $errorMsg="Your File To large Please Upload 5MB Size"; //error message file size not large than 5MB
 }
}
else
{ 
 $errorMsg="File Already Exists...Check Upload Folder"; //error message file not exists your upload folder path
} 

Row no 69 to 81 – The isset() function checks that the $errorMsg variable does not detect any error, and applies the PDO update query under prepare() statement.


The function bindParam() binds the value of :name up , :file_up and :id variable in the update query. A $name, $image_file, and $id variable keep all variable values.


Ultimately, the execute() function executes the update query statement, displays the file update message successfully and the header() function keeps this message at 3 seconds and sends it to index.php page.


if(!isset($errorMsg))
{
 $update_stmt=$db->prepare('UPDATE tbl_file SET name=:name_up, image=:file_up WHERE id=:id'); //sql update query
 $update_stmt->bindParam(':name_up',$name);
 $update_stmt->bindParam(':file_up',$image_file); //bind all parameter
 $update_stmt->bindParam(':id',$id);
    
 if($update_stmt->execute())
 {
  $updateMsg="File Update Successfully......."; //file update success message
  header("refresh:3;index.php"); //refresh 3 second and redirect to index.php page
 }
} 

Note:- Here I don't explain any else condition in edit codes, you see all else condition detecting different error messages and assigning this message in the variable $errorMsg.


index.php

We retrieve all records from tbl_file table in this file and set them up in the bootstrap table



<table class="table table-striped table-bordered table-hover">
     <thead>
         <tr>
            <th>Name</th>
            <th>File</th>
            <th>Edit</th>
            <th>Delete</th>
         </tr>
    </thead>
    <tbody>
    <?php
 $select_stmt=$db->prepare("SELECT * FROM tbl_file"); //sql select query
 $select_stmt->execute();
 while($row=$select_stmt->fetch(PDO::FETCH_ASSOC))
 {
    ?>
       <tr>
          <td><?php echo $row['name']; ?></td>
          <td><img src="upload/<?php echo $row['image']; ?>" width="100px" height="60px"></td>
          <td><a href="edit.php?update_id=<?php echo $row['id']; ?>" class="btn btn-warning">Edit</a></td>
          <td><a href="?delete_id=<?php echo $row['id']; ?>" class="btn btn-danger">Delete</a></td>
      </tr>
    <?php
 }
    ?>
    </tbody>
</table>

All Record Visually Awesome Below :


Multiple Images Records Retrieve from Database and Set Up in Bootstrap Table

PHP File Deleting Codes :

Before we delete an image under code which gets the image ID from the GET request, this will decide which image we will delete.


When you click Delete link unink() function will permanently remove image or file from the server and the PDO delete query will remove image information from the database.



 <?php

 require_once "connection.php";
 
 if(isset($_REQUEST['delete_id']))
 {
  // select image from db to delete
  $id=$_REQUEST['delete_id']; //get delete_id and store in $id variable
  
  $select_stmt= $db->prepare('SELECT * FROM tbl_file WHERE id =:id'); //sql select query
  $select_stmt->bindParam(':id',$id);
  $select_stmt->execute();
  $row=$select_stmt->fetch(PDO::FETCH_ASSOC);
  unlink("upload/".$row['image']); //unlink function permanently remove your file
  
  //delete an orignal record from db
  $delete_stmt = $db->prepare('DELETE FROM tbl_file WHERE id =:id');
  $delete_stmt->bindParam(':id',$id);
  $delete_stmt->execute();
  
  header("Location:index.php");
 }
 
?>

Download Codes

29 comments:

  1. change condition on edit codes

    if($type=="application/pdf")

    ReplyDelete
  2. HI I TRY TO UPLOAD FILE ZIP I HAVE PROBLEM OF SIZE WHEN I PUT FILE WITH 2 MO IT UPLOADED NORMAY AND WHEN I PUT 2 GO FILE I WONT I CHANGE SIZE
    if($size > 5000000) //check file size 5MB
    AND I VERIFY PHP.INI BUT STILL HAVE PROBLEM PLEASE HELP ME TO RESLOVE THIS AND IF YOU CAN HELP ME TO ADD PROGRESS BAR TO THIS CODE IT WILL BE VERY HELPFUL SIR THANKS

    ReplyDelete
    Replies
    1. Two thing first is if you upload zip file include following code our upload codes

      if($type='application/zip' || $type='application/rar') // check file extension zip or rar

      We upload different files and they files size are different, so remove this condition

      if($size>5000000) because that condition check validation from file is size 5 MB

      second, if you upload large size of file change below condition on your php.ini configuration file.

      below condition that increase your file size limit and restart the xampp server

      upload_max_filesize=128M

      max_file_uploads=300M

      Delete
  3. I cannot select any code, is this by purpose or unintentionally?

    ReplyDelete
    Replies
    1. Hi, Go to the end of the tutorial and download all the codes available in the source code zip file.

      Delete
  4. how to download the upload file?
    please help

    ReplyDelete
  5. hai can i know why if i change to video format using the same code. the video wont show on the system?

    ReplyDelete
    Replies
    1. you wanna show video on the system?

      Delete
    2. can u do a tutorial of upload,delete video ?

      Delete
  6. code not working not able to update image

    ReplyDelete
  7. when i am trying to update image it;s automatically delete from database

    ReplyDelete
    Replies
    1. the update code properly work.

      show your codes

      Delete
  8. it can edit the text file but not the file. help

    ReplyDelete
  9. Do you have source code foe update file using php mysqli?

    ReplyDelete
  10. Hello, how can i decide which files show or not in the client side. Is it possible? Regards

    ReplyDelete
    Replies
    1. Yes possible, include one a field in the table like name "status". In the insert code include two select option with name "enable" and "disable".

      Next in the insertion time select any one. In the client side include one condition only enable status file showing.

      If you not showing particular file apply status "disable" option in update and insert code.

      Thank you. keep visiting and like on Facebook page

      Delete
  11. while editing any post we fetch data from database and show it in edit form in that for how to show the name of image in file tag please guide me as soon as possible

    ReplyDelete
  12. How do I make a pdf add/update/delete?

    ReplyDelete
    Replies
    1. include this codes of your add, update code to make pdf.

      if($type=="application/pdf")

      Delete
    2. Thank you very much sir

      Delete
  13. hie, how do i upload multiple files, with different names like,

    input type="file" name="pic1"
    input type="file" name="pic2"
    input type="file" name="pic3"

    And i want to insert them in the database too, in different columns on 1 row.

    ReplyDelete
  14. hi sir, can you give an explanation on how to also add word document/pdf? and also is it possible for me to add a function to download to doc

    ReplyDelete
    Replies
    1. include this codes of your add, update code to make pdf and document file upload. if($type=="application/pdf" || $type=="application/doc")

      Delete
    2. thank you for the reply sir, it worked but the preview picture is blank is there any way i can fix it?

      Delete
    3. apply browser link to open your PDF file

      Delete

Post Bottom Ad