Showing posts with label How to Import and Export CSV Files Using PHP and MySQL. Show all posts
Showing posts with label How to Import and Export CSV Files Using PHP and MySQL. Show all posts

Mysql Fourth Maximum Salary And 5th Maximum Salary

Mysql Fourth Maximum Salary AND 5th Maximum Salary 

Hi Today Discussed Mysql Fourth Maximum Salary and 5th Maximum Salary How to Get Select Query is just a easy way select query and then count max salary follows query.

Mysql Four Maximum Salary And 5th Maximum Salary
Mysql Four Maximum Salary And 5th Maximum Salary 
Second Maximum Salary

SELECT 
    name, Salary
FROM
    emp a
WHERE
    1 = (SELECT 
            COUNT(name)
        FROM
            emp b
        WHERE
            b.Salary> a.Salary);
Third Maximum Salary

SELECT 
    name, Salary
FROM
    emp a
WHERE
    2 = (SELECT 
            COUNT(name)
        FROM
            emp b
        WHERE
            b.Salary> a.Salary);
Fourth Maximum Salary

SELECT 
    name, Salary
FROM
    emp a
WHERE
    3 = (SELECT 
            COUNT(name)
        FROM
            emp b
        WHERE
            b.Salary> a.Salary);
5th Maximum Salary
SELECT 
    name, Salary
FROM
    emp a
WHERE
    4 = (SELECT 
            COUNT(name)
        FROM
            emp b
        WHERE
            b.Salary> a.Salary);

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);  
 }