17 June, 2019

How to Import and Export CSV Files Using PHP and MySQL

                No comments   

<?php
session_start();
error_reporting(0);
include('includes/config.php');
include('includes/sessionout.php');
if(strlen($_SESSION['alogin'])==0)
    {  
header('location:index.php');
}
else{

$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
//$connect = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
// $success = 1 ;
// $error = 2;

$msg = "CSV Data Imported into the Database";
 $error = "Problem in Importing CSV Data";
 $errors = "No csv file selected";
if (isset($_POST["import"])) {
   

    $fileName = $_FILES["file"]["tmp_name"];
    if ($_FILES["file"]["size"] > 0) {
       
        $file = fopen($fileName, "r");
       
        while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
          $sqlInsert = "INSERT into lms_master (custid,FirstName ,EmailId,Gender,Dob,Address,    C_Address,City,state,pincode,Mobile,LastStatus,RegDate,LastCall,Remark,Csv_Date)
                   values ('" . $column[0] . "','" . $column[1] . "','" . $column[2] . "','" . $column[3] . "','" . $column[4] . "','" . $column[5] . "','" . $column[6] . "','" . $column[7] . "','" . $column[8] . "','" . $column[9] . "','" . $column[10] . "','" . $column[11] . "','" . $column[12 ] . "','" . $column[13] . "','" . $column[14] . "','" . $column[15] . "')";
            $result = mysqli_query($conn, $sqlInsert);
           
            if ($result) {
                $type = "success";
                header("Location: upload_data.php?success=1");
            } else {
                $type = "error";
                header("Location: upload_data.php?error=2");
            }
        }
    }else{
        $type = "error";
                $errors = "No csv file selected";
                header("Location: upload_data.php?errors=3");
    }
}

?>

<!DOCTYPE html>
<html lang="en">
    <head>       
        <!-- Title -->
        <title>Admin | Import Data</title>
        <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no"/>
        <meta charset="UTF-8">
        <meta name="description" content="Responsive Admin Dashboard Template" />
        <meta name="keywords" content="admin,dashboard" />
        <meta name="author" content="Steelcoders" />
        <!-- Styles -->
        <link type="text/css" rel="stylesheet" href="../assets/plugins/materialize/css/materialize.min.css"/>
        <link href="http://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
        <link href="../assets/plugins/material-preloader/css/materialPreloader.min.css" rel="stylesheet">
        <link href="../assets/css/alpha.min.css" rel="stylesheet" type="text/css"/>
        <link href="../assets/css/custom.css" rel="stylesheet" type="text/css"/>
       
        <style>
            .errorWrap {
            padding: 10px;
            margin: 0 0 20px 0;
            background: #fff;
            border-left: 4px solid #dd3d36;
            -webkit-box-shadow: 0 1px 1px 0 rgba(0,0,0,.1);
            box-shadow: 0 1px 1px 0 rgba(0,0,0,.1);
            }
            .succWrap{
                padding: 10px;
                margin: 0 0 20px 0;
                background: #fff;
                border-left: 4px solid #5cb85c;
                -webkit-box-shadow: 0 1px 1px 0 rgba(0,0,0,.1);
                box-shadow: 0 1px 1px 0 rgba(0,0,0,.1);
            }
        </style>
    </head>
    <body>
  <?php include('includes/header.php');?>
       <?php include('includes/sidebar.php');?>
            <main class="mn-inner">
                <div class="row">
                    <div class="col s12">
                        <div class="page-title"></div>
                    </div>
                    <div class="col s12 m12 l6">
                        <div class="card">
                            <div class="card-content">
                                <div class="row">
                                    <div class="imp_data">
                                    <span class="card-title"><a href="dashboard.php">Dashboard</a>&nbsp;>&nbsp;Employee&nbsp;|&nbsp;Import&nbsp;Data</span>

                                    </div>
                                    <form class="col s12" name="chngpwd" method="post" enctype="multipart/form-data">
                                          <?php if($_GET['error'] == 2 ){?>
                                                <div class="errorWrap">
                                                 <strong>ERROR</strong> : <?php echo htmlentities($error); ?>
                                                </div><?php }
                                                    else if($_GET['success'] == 1){?><div class="succWrap">
                                                    <strong>SUCCESS</strong> : <?php echo htmlentities($msg); ?>
                                                </div>
                                            <?php }?>

                                            <?php if($_GET['errors'] == 3 ){?>
                                                <div class="errorWrap">
                                                 <strong>ERROR</strong> : <?php echo htmlentities($errors); ?>
                                                </div><?php }
                                                    else if($_GET['success'] == 4){?><div class="succWrap">
                                                    <strong>SUCCESS</strong> : <?php echo htmlentities($msg); ?>
                                                </div>
                                            <?php }?>
                                           
                                           
                                        <div class="row">
                                            <div class="input-row">
                                                    <label class="col-md-4 control-label"></label> <input
                                                        type="file" name="file" id="file" accept=".csv">
                                                    <button type="submit" id="submit" name="import"
                                                        class="btn-submit">Import</button>
                                                    <br/>
                                                     <div id="labelError"></div>
                                                </div>
                                                <div id="labelError"></div>
                                            </div>   
                                        </div>
                                    </form>
                                </div>
                            </div>
                         <section>
                                <?php
                                        $sqlSelect = "SELECT * FROM lms_master";
                                        $result = mysqli_query($conn, $sqlSelect);
                                                   
                                        if (mysqli_num_rows($result) > 0) {
                                        ?>
                                        <table id='userTable'>
                                            <thead>
                                                <tr>
                                                    <th>First Name</th>
                                                    <th>EmailId</th>
                                                    <th>Gender</th>
                                                    <th>Dob</th>
                                                    <th>Address</th>
                                                    <th>C_Address</th>
                                                    <th>City</th>
                                                    <th>state</th>
                                                    <th>pincode</th>
                                                    <th>Mobile</th>
                                                    <th>LastStatus</th>
                                                    <th>LastCall</th>
                                                    <th>Remark</th>
                                                    <th>CSV Date</th>
                                                </tr>
                                            </thead>
                                            <?php
                                            while ($row = mysqli_fetch_array($result)) {
                                            ?>
                                            <tbody>
                                                <tr>
                                                    <td><?php  echo $row['FirstName ']; ?></td>
                                                    <td><?php  echo $row['EmailId ']; ?></td>
                                                    <td><?php echo $row['Gender']; ?></td>
                                                    <td><?php  echo $row['Dob']; ?></td>
                                                    <td><?php  echo $row['Address']; ?></td>
                                                    <td><?php  echo $row['C_Address']; ?></td>
                                                    <td><?php  echo $row['City']; ?></td>
                                                    <td><?php  echo $row['state']; ?></td>
                                                    <td><?php  echo $row['pincode']; ?></td>
                                                    <td><?php  echo $row['Mobile']; ?></td>
                                                    <td><?php  echo $row['LastStatus']; ?>
                                                    </td>        
                                                    <td><?php  echo $row['LastCall']; ?></td>
                                                    <td><?php  echo $row['Remark']; ?></td>
                                                    <td><?php  echo $row['Csv_Date']; ?></td>
                                                </tr>
                                             <?php
                                             }
                                             ?>
                                            </tbody>
                                        </table>
                                        <?php } ?>
                            </section> 
                        </div>
                    </div>
                    <!-- start second part right -->
                    <section>
                    </section>
                </div>
            </main>
        </div>
        <div class="left-sidebar-hover"></div>
        <!-- Javascripts -->
        <script src="../assets/plugins/jquery/jquery-2.2.0.min.js"></script>
        <script src="../assets/plugins/materialize/js/materialize.min.js"></script>
        <script src="../assets/plugins/material-preloader/js/materialPreloader.min.js"></script>
        <script src="../assets/plugins/jquery-blockui/jquery.blockui.js"></script>
        <script src="../assets/js/alpha.min.js"></script>
        <script src="../assets/js/pages/form_elements.js"></script>
        <!-- Import CSV File -->
        <script type="text/javascript">
            <script type="text/javascript">
        $(document).ready(
            function() {
            $("#frmCSVImport").on(
            "submit",
            function() {
            $("#response").attr("class", "");
            $("#response").html("");
            var fileType = ".csv";
            var regex = new RegExp("([a-zA-Z0-9\s_\\.\-:])+("
                    + fileType + ")$");
            if (!regex.test($("#file").val().toLowerCase())) {
                $("#response").addClass("error");
                $("#response").addClass("display-block");
                $("#response").html(
                        "Invalid File. Upload : <b>" + fileType
                                + "</b> Files.");
                return false;
            }
            return true;
        });
    });
</script>
        </script>
    </body>
</html>
<?php } ?>


<!-- start csv file
 -->
 <?php 
$connect = mysqli_connect("localhost", "root", "", "testing");
if(isset($_POST["submit"]))
{
 if($_FILES['file']['name'])
 {
  $filename = explode(".", $_FILES['file']['name']);
  if($filename[1] == 'csv')
  {
   $handle = fopen($_FILES['file']['tmp_name'], "r");
   while($data = fgetcsv($handle))
   {
    $item1 = mysqli_real_escape_string($connect, $data[0]); 
                $item2 = mysqli_real_escape_string($connect, $data[1]);
                $query = "INSERT into excel(excel_name, excel_email) values('$item1','$item2')";
                mysqli_query($connect, $query);
   }
   fclose($handle);
   echo "<script>alert('Import done');</script>";
  }
 }
}
?> 
<!DOCTYPE html> 
<html> 
 <head> 
  <title>Webslesson Tutorial</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script> 
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
 </head> 
 <body> 
  <h3 align="center">How to Import Data from CSV File to Mysql using PHP</h3><br />
  <form method="post" enctype="multipart/form-data">
   <div align="center"> 
    <label>Select CSV File:</label>
    <input type="file" name="file" />
    <br />
    <input type="submit" name="submit" value="Import" class="btn btn-info" />
   </div>
  </form>
 </body> 
</html>

Related Posts:

0 comments:

Post a Comment

Thanks

Meta

Popular Posts

Categories

Social Media Links

Blog Archive