PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO Tutorial

Furqan Aziz | 28 Nov 2013 | Tutorials

PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO Tutorial
Excel FileClick Here
CSV FileClick Here
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. 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

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

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

  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.
    Table Structure for statstrackertemp Table
  4. configPDO.php file use a PDO (PHP Data Objects) for connection to MySQL Database to 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.

Social Box

LATEST TUTORIALS
PHP MySQL Simple Delete using PDO in Bootstrap Tutorial
Convert Simple Text To 3D Text using Adobe Fireworks CS6 Tutorial
PHP Simple Update using PDO in Bootstrap Tutorial
PHP Simple Insert using PDO in Bootstrap Tutorial
PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO Tutorial
PHP Easy Sign-in Using PDO Prepared Statement Tutorial
Easy PHP Email With Attachments using Swift Mailer Tutorial
Easy Pagination with PDO in PHP plus Export to Excel Tutorial
PHP PDO to Connect MySQL Database Tutorial
Best CSS Frameworks Collection For Rapid Web Development
PHP-MySQL Ajax Jquery-UI Autocomplete in Bootstrap Tutorial
PHP Simple Sign-in Tutorials
Jquery Raty Usage via PHP
PHP Dynamic Select options Via Ajax
CSV Importer in MySQL
PHP File Load Via Jquery into DIV
© 2014 CreWow Network - All Rights Reserved.