PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO Tutorial

PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO is a tutorial in which you will learn how to import data in MySQL Database via CSV File while using PDO (PHP Data Objects) Prepared statements. We are using Bootstrap as CSS Framework in this tutorial for creating CSV File Upload Form and other functionality. for Demo we will make excel file in the format given in sample excel file.

For Source Code Please Click Here

 Then we will save it as csv (comma separated values) file and press CSV Upload Button and choose test.csv file and Press Save CSV Button. Data will be moved in the temporary table and after upload we will redirected to index.php page where message will be shown for success or failure. In index.php it will also show your uploaded data in a table. We will learn

• How to make a Form for CSV File Upload.
• How to create a Temporary Table.
• How to make a Connection Page
• How to make an Insertion Page using PDO Queries
• How to show a success and Warning message.

Source Code Folder contains following files and folders, below is the explanation of each.

• sql.txt contains full database structure along with sample data.
• index.php is file from where you can upload CSV File and view results.
• Uploadmypdo.php is file in which code of getting CSV File and Upload using PDO queries written.
• configPDO.php is a file which will create connection with database.
• Footer.php is a file which contains footer bar and form for CSV Upload.
• assets folder contains necessary css, icon, images and js files.

We use following for developing PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO Tutorial.

• PHP as Server Side Scripting Language
• MySQL as a Database.
• Bootstrap as CSS Framework.
• JQuery as a service.
• jqBootstrapValidation for validating sign-in form.
• phpMyAdmin for creating database and tables.
• Sublime Text 2+ as a code editor.
• XAMPP as a package for Apache web server, PHP and MySQL.
• Google Chrome as a browser for testing.

1. First we will make a upload CSV button in index.php page and use data-toggle as modal and give href value as #fileupload

• <a href="#fileupload" data-toggle="modal" class"btn btn-primary btn-large">Upload CSV</a>
  

2. When we press upload CSV button, modal will be open which will display below form for Upload CSV. Form code is in footer.php page and its a included in index.php page.

• <div id="fileupload" class="modal hide fade" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
              <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
                <h3 id="myModalLabel">Upload Data via CSV File</h3>
              </div>
              <div class="modal-body">
  <form class="form-horizontal" enctype="multipart/form-data" method="post" action="Uploadmypdo.php">
              <div class="control-group">
                <label class="control-label" for="inputName">Save CSV File</label>
                <div class="controls">
                <input type="hidden" name="MAX_FILE_SIZE" value="9999999" />
                <input class="btn btn-info" name="file" type="file" id="file" onchange="showCode()" onblur="showCode()" onclick="showCode" required="required"  />
                </div>
              </div>
              <div class="control-group">
                <div class="controls">
                  <button type="submit" class="btn">Save CSV</button>
                </div>
              </div>
            </form>
  
      </div>
  </div>

3. Now its time for creating sample database csvsignindb using sql.txt file which contains following table create queries and sample data for demo and test.
• statstrackertemp table contains fields like ServerName, HiMemUti, AvgMemUti, HiCpuUti, AvgCpuUti, HiIOPerSec, AvgIOPerSec, HiDiskUsage, AvgDsikUsage.

4. configPDO.php file use a PDO (PHP Data Objects) for connection to MySQL Databaseto avoid SQL injections.

• <?php
  // mysql hostname
  $hostname = 'localhost';
  // mysql username
  $username = 'root';
  // mysql password
  $password = '';
  // Database Connection using PDO
  try {
  $dbh = new PDO("mysql:host=$hostname;dbname=csvsignindb", $username, $password);
      }
  catch(PDOException $e)
      {
      echo $e->getMessage();
      }
  ?>

5. For inserting CSV Data in MySQL Database using PDO (PHP Data Objects) Prepared queries we will write code like below with explanation using comments.

• <?php 
   session_start();
  // mysql hostname
   $hostname = 'localhost';
  // mysql username
   $username = 'root';
  // mysql password
   $password = '';
  // Get Extension of the File.
   $extension= end(explode(".", basename($_FILES['file']['name'])));
  // isset Determines if a variable is set and is not NULL. Set Size Limit less then 10 MB=10485760 bytes. Extension must be CSV.
   if (isset($_FILES['file']) && $_FILES['file']['size'] < 10485760 && $extension== 'csv')
   {  
  // We will get csv file and save it in a $file
      $file = $_FILES['file']['tmp_name']; 
  //$handle is a valid file pointer to a file successfully opened by fopen(), popen(), or fsockopen(). fopen() used to open file.
      $handle = fopen($file, "r"); 
  // We will use try{} Catch() statements here.
       try { 
  // Database Connection using PDO
    $dbh = new PDO("mysql:host=$hostname;dbname=csvsignindb", $username, $password);
  // Truncate Table before insertion
    $STMR = $dbh->prepare("TRUNCATE TABLE statstrackertemp");
    $STMR->execute();
  // Prepare the statement for the insertion in the table
          $STM = $dbh->prepare('INSERT INTO statstrackertemp (ServerName, HiMemUti, AvgMemUti, HiCpuUti, AvgCpuUti, HiIOPerSec, AvgIOPerSec, HiDiskUsage, AvgDsikUsage) VALUES (?, ?, ?, ?, ?,?, ?, ?, ? )');
  //Check handel is True or False
     if ($handle !== FALSE) 
     {
  // fgets() Gets a line from file pointer and read the first line from $handle and ignore it.   
            fgets($handle);
  // While loop used here and  fgetcsv() parses the line it reads for fields in CSV format and returns an array containing the fields read.
            while (($data = fgetcsv($handle, 1000, ',')) !== FALSE)
      {
  // For Executing prepared statement we will use below function
               $STM->execute($data);
            }       
  //The file pointed to by handle will be closed.
            fclose($handle);
      
  // Closing MySQL database connection
         $dbh = null; 
  // If data inserted successfully we will redirect this page to index.php and show success message there with code 77083368
      header( "location:index.php?ServerStatsAdded=77083368"); 
      
        
     }
  
       }
  // Exceptions error will be thrown if Database not connected. 
    catch(PDOException $e)
    {
          die($e->getMessage());
       }
  
  
   }
   else 
   {
  // Error mesage id File type is not CSV or File Size is greater then 10MB.
      header( "location:index.php?ServerStatsAdded=37767");
   }
  ?>

6. After processing of CSV File it will redirected to index.php page where we will show success of failure message using codes return from Uploadmypdo.php

• if($_GET["ServerStatsAdded"]==77083368)  
  {
  echo "<div class='alert alert-success'>"; 
  echo "<button type='button' class='close' data-dismiss='alert'>×</button>"; 
  echo "Server Stats added in System."; 
  echo "</div>";
  }
  if($_GET["ServerStatsAdded"]==37767)  
  {
  echo "<div class='alert alert-block'>"; 
  echo "<button type='button' class='close' data-dismiss='alert'>×</button>";
  echo "<h4>Warning!</h4>"; 
  echo "Many Reasons for not adding stats. it can be wrong file format, file size greater then 10 MB, data already in system."; 
  echo "</div>"; 
  }

Thanks for reading. Enjoy and share with friends.

We hope you will find PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO Tutorial very helpful and easy. PDO (PHP Data Objects) Prepared statements used in this tutorial along with Bootstrap CSS Framework for better user experience. For any query/suggestions please post a comment on our Facebook Page.