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.

PHP Mail Form Tutorial

PHP mail form Tutorial will show you how to make a feedback form on your website. you just need to make a form and use PHP Mail Function. This Tutorial is based on W3 Schools Tutorial. This tutorial will show how to send email when form is filled and how to show from when its empty. We will use isset() Function which Determine if a variable is set and is not NULL.

Below are the steps for making the PHP Mail form.

• Create contact.php page for this tutorial.
• Write Below Code in contact.php because Below code will check if the email address is filled or not. if its filled then it will get data from form and Send Email. otherwise it will move to last step and else condition will execute. please see last step.

• After if condition put below code , if email address is not filled below code will show the form for Suggestion.

Website Link for this Tutorial

CSS Layout Basic Tutorial

CSS Basic Layout Tutorial is a very good for begineers who want to design their frist website using DIV and CSS. it will teach how to make header , content area, right side bar and footer using CSS. it contains following pages and folder in source.

Source File of This Tutorial

• Index.php
• cufon-yui.js
• caviar-dreams.cufonfonts.js
• jquery-1.10.1.js
• Style Folder
• Images
• CSS Folder

After Downloading Source File, you can copy the folder in www root directory of your and then try in Browser. if you do nat have PHP then you can change the index.php file to index.html and it can be run without apache server. Enjoy!