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.

But wait, before file uploading this tutorial covered 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.

Learn More :




Editor and Server 

Notepad++  – Download.  All the codes I've written in this editor.

XAMPP Server – Download. Setup and execute this project on this server.

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  – Download. 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.

Here, below a few lengthy but not hard codes, jump to explanation codes that you'll easily understand the logic.


<?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 conditionCheck 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.

Again below codes a few lengthy but not hard, jump to explanation file edit codes you quickly understand the logic.

<?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

8 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