Example 1: download csv php mysql

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(80) NOT NULL,
  `name` varchar(50) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `email` varchar(70) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

=========================================================
// this config.php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
}


=================================================================
// prints contents of db to a table with an export button
<?php 
include "config.php"; // Database connection file
?>

<div class="container">
 
 <form method='post' action='download.php'>
  <input type='submit' value='Export' name='Export'>
 
  <table border='1' style='border-collapse:collapse;'>
    <tr>
     <th>IDth>
     <th>Usernameth>
     <th>Nameth>
     <th>Genderth>
     <th>Emailth>
    tr>
     
     $query = "SELECT * FROM users ORDER BY id asc";
     $result = mysqli_query($con,$query);
     $user_arr = array();
     while($row = mysqli_fetch_array($result)){
      $id = $row['id'];
      $uname = $row['username'];
      $name = $row['name'];
      $gender = $row['gender'];
      $email = $row['email'];
      $user_arr[] = array($id,$uname,$name,$gender,$email);
   ?>
      <tr>
       <td> echo $id; ?>td>
       <td> echo $uname; ?>td>
       <td> echo $name; ?>td>
       <td> echo $gender; ?>td>
       <td> echo $email; ?>td>
      tr>
   
    }
   ?>
   table>
    
    $serialize_user_arr = serialize($user_arr);
   ?>
  <textarea name='export_data' style='display: none;'> echo $serialize_user_arr; ?>textarea>
 form>
div>
    
    
    
    =============================================================
  //Create a new download.php file -- code below//  
    
    
$filename = 'users.csv';
$export_data = unserialize($_POST['export_data']);

// file creation
$file = fopen($filename,"w");

foreach ($export_data as $line){
 fputcsv($file,$line);
}

fclose($file);

// download
header("Content-Description: File Transfer");
header("Content-Disposition: attachment; filename=".$filename);
header("Content-Type: application/csv; "); 

readfile($filename);

// deleting file
unlink($filename);
exit();

Example 2: how to export php mysql data to csv through php


/*
* iTech Empires:  Export Data from MySQL to CSV Script
* Version: 1.0.0
* Page: Index
*/
 
// Database Connection
require("db_connection.php");
 
// List Users
$query = "SELECT * FROM users";
if (!$result = mysqli_query($con, $query)) {
    exit(mysqli_error($con));
}
 
if (mysqli_num_rows($result) > 0) {
    $number = 1;
    $users = '
    ';while($row=mysqli_fetch_assoc($result)){$users.='';$number++;}$users.='
No. First Name Last Name Email
'.$number.' '.$row['first_name'].' '.$row['last_name'].' '.$row['email'].'
'
; } ?>
doctype html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Export Data from MySQL to CSV Tutorial | iTech Empirestitle> <link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css"/> head> <body> <div class="container"> <div class="row"> <div class="col-md-12"> <h2>Export Data from MySQL to CSVh2> div> div> <div class="form-group"> echo $users ?> div> <div class="form-group"> <button onclick="Export()" class="btn btn-primary">Export to CSV Filebutton> div> <script> function Export() { var conf = confirm("Export users to CSV?"); if(conf == true) { window.open("export.php", '_blank'); } } script> div> body> html>

Example 3: import data from csv to db php


$query = <<<eof
    LOAD DATA INFILE '$fileName'
     INTO TABLE tableName
     FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\n'
     IGNORE 1 LINES
    (field1,field2,field3,etc)
eof;

$db->query($query);
?>