Showing posts with label CSV File Upload Import Database In php mysql. Show all posts
Showing posts with label CSV File Upload Import Database In php mysql. Show all posts

How to Import and Export CSV Files Using PHP and MySQL

How to Import and Export CSV Files Using PHP and MySQL


Hi Today discussed PHP Server side Scripting Language How to Import and Export CSV Files using PHP and mysql. Database normally insert and update and delete queries and bulk insert it's means csv file same column file data csv file data insert mysql. code follows.


How to Import and Export CSV Files Using PHP and MySQL
How to Import and Export CSV Files Using PHP and MySQL



First Create database and then create table.

CREATE DATABASE employee
Create table

CREATE TABLE employeedetils(
eid VARCHAR(50) UNSIGNED PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
create_date VARCHAR(50)
)
dbconnect

<?php
function getdb(){
$servername = "localhost";
$username = "root";
$password = "";
$db = "employee";

try {
   
    $conn = mysqli_connect($servername, $username, $password, $db);
     //echo "Connected successfully"; 
    }
catch(exception $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
    return $conn;
}
?>
PHP File Import function phpfiles.php

<?php


 if(isset($_POST["Import"])){
  
  $filename=$_FILES["file"]["tmp_name"];  


   if($_FILES["file"]["size"] > 0)
   {
     $file = fopen($filename, "r");
         while (($getData = fgetcsv($file, 10000, ",")) !== FALSE)
          {


            $sql = "INSERT into employeedetils(eid,firstname,lastname,email,create_date)                    values ('".$getData[0]."','".$getData[1]."','".$getData[2]."','".$getData[3]."','".$getData[4]."')";
                   $result = mysqli_query($con, $sql);
    if(!isset($result))
    {
     echo "<script type=\"text/javascript\">
       alert(\"Invalid File:Please Upload CSV File.\");
       window.location = \"index.php\"
        </script>";  
    }
    else {
       echo "<script type=\"text/javascript\">
      alert(\"CSV File has been successfully Imported.\");
      window.location = \"index.php\"
     </script>";
    }
          }
   
          fclose($file); 
   }
 }  


 ?>

View Page design bootstrap lib include

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>
View Page


<!DOCTYPE html>
<html lang="en">

<head>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>

</head>

<body>
    <div id="wrap">
        <div class="container">
            <div class="row">

                <form class="form-horizontal" action="phpfiles.php" method="post" name="upload_excel" enctype="multipart/form-data">
                    <fieldset>

                        <!-- Form Name -->
                        <legend>Form Name</legend>

                        <!-- File Button -->
                        <div class="form-group">
                            <label class="col-md-4 control-label" for="filebutton">Select File</label>
                            <div class="col-md-4">
                                <input type="file" name="file" id="file" class="input-large">
                            </div>
                        </div>

                        <!-- Button -->
                        <div class="form-group">
                            <label class="col-md-4 control-label" for="singlebutton">Import data</label>
                            <div class="col-md-4">
                                <button type="submit" id="submit" name="Import" class="btn btn-primary button-loading" data-loading-text="Loading...">Import</button>
                            </div>
                        </div>

                    </fieldset>
                </form>

            </div>
            <?php
               getdbrecords();
            ?>
        </div>
    </div>
</body>

</html>
view php records

function getdbrecords(){
    $con = getdb();
    $Sql = "SELECT * FROM employeedetils";
    $result = mysqli_query($con, $Sql);  


    if (mysqli_num_rows($result) > 0) {
     echo "<div class='table-responsive'><table id='myTable' class='table table-striped table-bordered'>
             <thead><tr><th>EMP ID</th>
                          <th>First Name</th>
                          <th>Last Name</th>
                          <th>Email</th>
                          <th>Registration Date</th>
                        </tr></thead><tbody>";


     while($row = mysqli_fetch_assoc($result)) {

         echo "<tr><td>" . $row['eid']."</td>
                   <td>" . $row['firstname']."</td>
                   <td>" . $row['lastname']."</td>
                   <td>" . $row['email']."</td>
                   <td>" . $row['create_date']."</td></tr>";        
     }
    
     echo "</tbody></table></div>";
     
} else {
     echo "you have no records";
}
}

Export MySQL to CSV With PHP

<div>
            <form class="form-horizontal" action="phpfiles.php" method="post" name="upload_excel"   
                      enctype="multipart/form-data">
                  <div class="form-group">
                            <div class="col-md-4 col-md-offset-4">
                                <input type="submit" name="Export" class="btn btn-success" value="export to excel"/>
                            </div>
                   </div>                    
            </form>           
 </div>
Export Functions

if(isset($_POST["Export"])){
   
      header('Content-Type: text/csv; charset=utf-8');  
      header('Content-Disposition: attachment; filename=data.csv');  
      $output = fopen("php://output", "w");  
      fputcsv($output, array('ID', 'First Name', 'Last Name', 'Email', 'Joining Date'));  
      $query = "SELECT * from employeedetils ORDER BY emp_id DESC";  
      $result = mysqli_query($con, $query);  
      while($row = mysqli_fetch_assoc($result))  
      {  
           fputcsv($output, $row);  
      }  
      fclose($output);  
 }  

CSV File Upload Import Database In php mysql

CSV File Upload Import Database In php mysql
http://www.developerinvention.in/2016/04/how-to-create-and-download-csv-file.html?m=1 More data csv format insert database import csv format data.
Create can file

CSV File Upload Import Database In php mysql

CSV File Upload Import Database In php mysql


<?php
include("includes/header.php");
function remostrtag($a)
{
    return mysql_escape_string(trim($a));
}
 $error_msg='';
function redvv($b)
{
    return str_replace(" ","",strtolower($b));
}
if (isset($_REQUEST['submit']) || isset($_REQUEST['submit_x'])) {
    
     $file_size = $_FILES['cfile']['size']; 
  // echo  "===>>>".$file_type = $_FILES['cfile']['type']; exit;
    
   
   $filecheck = basename($_FILES['cfile']['name']);
 $ext = substr($filecheck, strrpos($filecheck, '.') + 1);
   /*
 if (($file_size > 2097152)){      
        $message = 'File too large. File must be less than 2 megabytes.'; 
        echo '<script type="text/javascript">alert("'.$message.'");</script>'; 
    }
 */
 $repary=array();
  if(strtolower($ext)=='csv')
   {
$handle = fopen($_FILES['cfile']['tmp_name'], "r");
$hj=0;
while (($data = fgetcsv($handle, 4000, ",")) !== FALSE) {
//print_r($data).'<br/><br/>';
            $hj++;
            $sts=0;
              if($hj==1 && (redvv($data[0])!='sl.no' || redvv($data[1])!='name' || redvv($data[2])!='sex'  || redvv($data[3])!='yearofstudy' || redvv($data[4])!='class'   || redvv($data[5])!='presentposition'  || redvv($data[6])!='telephoneno' || redvv($data[7])!='emailid')){
                header("location:alumnicsvfile.php?fort");
   exit; 
            }
          
            
            if($hj>1){
                $qrychk=mysql_query("select * from tblmini where LOWER(`firstname`)='".strtolower(remostrtag($data[1]))."'");
               // if(mysql_num_rows($qrychk) =0)
                //{
//                   $grpid=getret_cln_grpid($data[3]); 
                   // if(strtolower($data[11])=='active')
                 //   {
                        $sts=1;
                 //   }
                    
                    $add=$data[5].','.$data[6];
$emapass = explode('@',$data[7]);
$passd = $emapass[0].'123';
// ECHO $passd.'<br/><br/>';
         $import="INSERT into tblmini(firstname,gender,batch,phone,email,password) values('$data[1]','$data[2]','$data[3]','$data[6]','$data[7]','$passd')";
//echo $import; 
mysql_query($import);
                //}else{
                //    $repary[]=$data[0];
               // }
}
        }
       //exit;
fclose($handle);
         if(!empty($repary))
        {
             $error_msg='S.no '.implode(",",$repary).' values are not inserted';
        }else{
   header("location:mnicsvfile.php?suc");
   exit;
        }
}else
{
    header("location:mnicsvfile.php?err");
   exit;
    $error_msg='Please upload Correct CSV file';
}
}
     ?>
 
<div id="content" class="app-content" role="main">
    <div class="bg-light lter b-b wrapper-md">
        <h1 class="m-n font-thin h3" style="font-size: 22px;">
            Upload <i class="fa fa-angle-double-right"></i> Alumni CSV File Upload
        </h1>
    </div>
    <div class="wrapper-md">
        <!-- toaster directive -->
        <toaster-container
            toaster-options="{'position-class': 'toast-top-right', 'close-button':true}"></toaster-container>
        <!-- / toaster directive -->
        <div class="panel panel-default">
            <div class="panel-heading" style="height: 3.5em">
                <span style="color:#F00;float:left;font-style: none">* Marked fields are mandatory</span>
              
            </div>
            <div class="panel-body">
                <form  method="post" action="" class="form-validation" enctype="multipart/form-data">
                    <div class="form-group">
<?php
if( $error_msg!=''){
     echo '<div class="col-sm-12 fromdel" >'.$error_msg.'</div>';
}else{
if (isset($_REQUEST['suc'])) {
    echo '<div class="col-sm-12 fromsuc" style="float:left; height:30px;">Successfully Imported</div>';
}
if (isset($_REQUEST['up'])) {
    echo '<div class="col-sm-12 fromupd" >Successfully Updated</div>';
} if (isset($_REQUEST['del'])) {
    echo '<div class="col-sm-12 fromdel" >Successfully Deleted</div>';
}
if (isset($_REQUEST['err'])) {
    echo '<div class="col-sm-12 fromdel" style="color:red">Please upload Correct CSV file</div>';
}
if (isset($_REQUEST['fort'])) {
    echo '<div class="col-sm-12 fromdel" style="color:red">Please upload Correct field name in CSV file</div>';
}
}
?>
                    </div>
                  
                    <div class="line line-dashed b-b line-lg pull-in"></div>
                    <div class="form-group">
                        <label class="col-sm-2 control-label">Csv File<span style="color:#F00">*</span></label>
                        <div class="col-sm-10">
                            <div class="btn-group">
                                <input type="file" name="cfile" />
                            </div>
                        </div>
                    </div>
                    <div class="line line-dashed b-b line-lg pull-in"></div>
                   
                    <footer class="panel-footer text-center bg-light lter">
 <button type="submit"
                                    class="btn m-b-xs btn-sm btn-success btn-addon"
                                    name="submit" value="submit" >
                                <i class="fa fa-save"></i>Upload
                            </button>
                    </footer>
                </form>
            </div>
        </div>
    </div>
</div>  
<?php
include("includes/footer.php");
?>