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>

0 comments:

Post a Comment

Thanks

Meta

Popular Posts

  • Crawl and Index Your Website with Laravel Site Search
      Laravel Site Search   is a package by Spatie to create a full-text search index by crawling your site. You can think of it as a private Go...
  • JqueryUI - Show
    JqueryUI - Show, JqueryUI,  This chapter will discuss the show() method, which is one of the methods used to manage jQueryUI visual effe...
  • SpinLock VB.Net Example From MSDN Possibly Produces Incorrect Behaviour
    The code below is part of an example from MSDN. It is the example on how to use SpinLock but to my eye there is a race condition in it. Why ...
  • WordPress Table
    WordPress Table WordPress table is an easy way to show the data in the table format. In the past, we had used the HTML code or table plugin ...
  • Python exec() Function
    Python exec() Function The python  exec()  function is used for the dynamic execution of Python program which can either be a string or obje...

Categories

Social Media Links

Blog Archive

Laravel News

Loading...