THE EXAMPLE SCENARIO

In this guide, we will be using a very simple user database as an example. We will be working on how to import users from a CSV file, as well as how to export users from the users table.

WHAT IS CSV?

CSV stands for comma separated values, and we can call it a “spreadsheet” in the layman terms. But actually, CSV files are just plain text files that look like this:

hello,world,foo,bar
goodbye,world,doge,cate

Every comma in the CSV file stands for a column, and every new line is a row in the “spreadsheet”. But unlike the “legit spreadsheets”, CSV files do not store the column widths nor text styles… So yep, don’t be surprised to see your “bold” and column width adjustments go missing even after you have properly saved the file.

DUMMY USERS TABLE

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_email` (`user_email`),
  ADD KEY `user_name` (`user_name`);

ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

SAMPLE CSV FILE

[email protected],Jane Doe
[email protected],Joe Doe
[email protected],John Doe
[email protected],Julie Doe
[email protected],Johan Doe
[email protected],Joanne Doe
[email protected],Juliet Doe
[email protected],June Doe
[email protected],Juan Doe
[email protected],Jamir Doe
[email protected],Jaden Doe
[email protected],James Doe
[email protected],Janus Doe
[email protected],Jason Doe
[email protected],Jay Doe
[email protected],Jeff Doe
[email protected],Jenn Doe
[email protected],Joah Doe
[email protected],Joyce Doe
[email protected],Joy Doe
[email protected],Juke Doe
[email protected],Johnnie Doe
[email protected],Jim Doe
[email protected],Jess Doe
[email protected],Jabril Doe

CONFIG FILE

First, create a config file to safely put all the database settings and stuff into – Do remember to change these settings to your own.

DATABASE LIBRARY

pdo = new PDO(
        $str, DB_USER, DB_PASSWORD, [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
          PDO::ATTR_EMULATE_PREPARES => false
        ]
      );
      return true;
    }

    // ERROR - DO SOMETHING HERE
    // THROW ERROR MESSAGE OR SOMETHING
    catch (Exception $ex) {
      print_r($ex);
      die();
    }
  }

  function __destruct() {
  // __destruct() : close connection when done

    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }

  function exec($sql, $data=null) {
  // exec() : run insert, replace, update, delete query
  // PARAM $sql : SQL query
  //       $data : array of data
 
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($data);
      $this->lastID = $this->pdo->lastInsertId();
    } catch (Exception $ex) {
      $this->error = $ex;
      return false;
    }
    $this->stmt = null;
    return true;
  }

  function fetch($sql, $cond=null, $key=null, $value=null) {
  // fetch() : perform select query
  // PARAM $sql : SQL query
  //       $cond : array of conditions
  //       $key : sort in this $key=>data order, optional
  //       $value : $key must be provided, sort in $key=>$value order

    $result = false;
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($cond);
      if (isset($key)) {
        $result = array();
        if (isset($value)) {
          while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
            $result[$row[$key]] = $row[$value];
          }
        } else {
          while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
            $result[$row[$key]] = $row;
          }
        }
      } else {
        $result = $this->stmt->fetchAll();
      }
    } catch (Exception $ex) {
      $this->error = $ex;
      return false;
    }
    $this->stmt = null;
    return $result;
  }
}
?>

Next, we create a database library that will do all the database heavy lifting.

USERS IMPORT/EXPORT LIBRARY

error = "Invalid file type - only CSV files are allowed";
      return false;
    }

    /* (2) READ UPLOADED FILE + IMPORT TO DATABASE */
    // !! IMPORTANT - Do not try to read the entire file into a string
    // !! That will kill the server on insane large uploads
    // !! Read line-by-line instead
    $handle = fopen($_FILES["file-upload"]["tmp_name"], "r");
    if ($handle) {
      while (($line = fgetcsv($handle)) !== false) {  
        // Use "REPLACE INTO" if you want to override the existing entries instead
        $sql = "INSERT INTO `users` (`user_name`, `user_email`) VALUES (?,?)";
        $data = [$line[0], $line[1]];
        $pass = $this->exec($sql, $data);

        // Remove these lines if you want the import operation to be "silent"
        print_r($line);
        echo "
";
        echo $pass ? "OK" : $this->error;
        echo "
";
      }
      fclose($handle);
      return true;
    } else {
      $this->error = "Error reading uploaded file!";
      return false;
    }
  }

  function importAlt () {
  /* THIS IS AN ALTERNATIVE VERSION OF IMPORT
   * Bad idea to run a single INSERT query on every line read.
   * Can potentially run a few thousand times on large file imports.
   * This version will "gather" the data into temporary array, and only run the SQL on every 20 lines.
   * This should ease the round trip time, and speed up the entire import process. */

    /* (1) CHECK FILE TYPE */
    if (strtoupper(pathinfo($_FILES['file-upload']['name'], PATHINFO_EXTENSION)) != "CSV") {
      $this->error = "Invalid file type - only CSV files are allowed";
      return false;
    }

    /* (2) READ UPLOADED FILE + IMPORT TO DATABASE */
    $handle = fopen($_FILES["file-upload"]["tmp_name"], "r");
    if ($handle) {
      $perrun = 20; $thisrun = 0;
      while (($line = fgetcsv($handle)) !== false) {  
        if ($thisrun==0) {
          $sql = "INSERT INTO `users` (`user_name`, `user_email`) VALUES ";
          $data = [];
        }
        $sql .= "(?,?),";
        $data[] = $line[0];
        $data[] = $line[1];
        $thisrun++;
        if ($thisrun==$perrun) {
          $sql = substr($sql, 0, -1) . ";";
          $pass = $this->exec($sql, $data);
          $thisrun = 0;

          // Remove these lines if you want the import operation to be "silent"
          print_r($data);
          echo "
";
          echo $pass ? "OK" : $this->error;
          echo "
";
        }
      }
      if ($thisrun!=0) {
        $sql = substr($sql, 0, -1) . ";";
        $pass = $this->exec($sql, $data);

        // Remove these lines if you want the import operation to be "silent"
        print_r($data);
        echo "
";
        echo $pass ? "OK" : $this->error;
      }
      fclose($handle);
      return true;
    } else {
      $this->error = "Error reading uploaded file!";
      return false;
    }
  }

  function export () {
    /* (1) CREATE NEW CSV FILE */
    $handle = fopen("export.csv", "w");

    if ($handle) {
      /* (2) GET ENTRIES FROM DATABASE */
      $sql = "SELECT * FROM `users`";
      $data = null;
      if ($_POST['search']!="") {
        $sql .= " WHERE `user_name` LIKE ? OR `user_email` LIKE ?";
        $data = ["%" . $_POST['search'] . "%", "%" . $_POST['search'] . "%"];
      }
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($data);

      /* (3) WRITE TO CSV FILE */
      // !! IMPORTANT - Might crash server if fetch everything at one go
      // This will fetch from the database and write line-by-line
      while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
        fputcsv($handle, [$row['user_id'], $row['user_email'], $row['user_name']]);
        // Remove these lines if you want the import operation to be "silent"
        print_r($row);
        echo "
";
      }

      /* (4) RETURN RESULTS */
      fclose($handle);
      return true;
    } else {
      $this->error = "Failed to create export.csv";
      return false;
    }
  }

  function exportDL () {
    /* (1) GET ENTRIES FROM DATABASE */
    $sql = "SELECT * FROM `users`";
    $data = null;
    if ($_POST['search']!="") {
      $sql .= " WHERE `user_name` LIKE ? OR `user_email` LIKE ?";
      $data = ["%" . $_POST['search'] . "%", "%" . $_POST['search'] . "%"];
    }
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);

    /* (2) SERVE HTTP HEADERS */
    header('Content-Type: application/octet-stream');
    header("Content-Transfer-Encoding: Binary"); 
    header("Content-disposition: attachment; filename=\"export.csv\""); 

    /* (3) SERVE DATA */
    while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
      echo implode(",", [$row['user_id'], $row['user_email'], $row['user_name']]) . "\r\n";
    }
  }
}

Finally, we create the user library that will do the import and export.

Please do take note that there is no checking done in the import examples – The users will have to make sure that the CSV file is properly formatted. For example, the first column must be the name, and second is the email… This script will just blindly import, although you can add your own checks if you want.

IMPORT PAGE

  
    

      
    import();
      // Check out this alternate version too
      // $pass = $libUsr->importAlt();
      echo $pass ? "Import OK" : $libUsr->error ;
    } ?>
  

The import page should be very straightforward, there are 2 parts to it:

• The top part is nothing but a very simple CSV file upload form.
• When the form is submitted, the bottom part will use the library and call the import function to handle the upload.

EXPORT PAGE

0) {
  // If you are expecting a large export, increase the timeout limit
  // Time in seconds. 0 is unlimited... But bad if this script hangs
  set_time_limit(0);
  require "lib-db.php";
  require "lib-users.php";
  $libUsr = new Users;

  // This version will write to a file on the server
  // $pass = $libUsr->export();
  // echo $pass ? "Export OK" : $libUsr->error ;

  // This version will force a download
  $libUsr->exportDL();
}

/* (B) SHOW EXPORT FORM */ 
else { ?>


  
    

      Search for user name or email (optional)
      
  

This export page is pretty much the same as the import page and has 2 parts to it.

• The top part will handle and process the export request using the library.
• The bottom part will show the HTML export form.

SOURCE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

THE SUMMARY – BUILDING YOUR OWN

Now that you have downloaded all the example scripts, what is the next move? How to implement this into your own project? Remember the 3 steps in this guide, deal with them one at a time, and you will do just fine:

• The Database – Create the tables for your project if you have not already done so.
• Server-side Scripts – Build the library files and import/export functions for your project. Feel free to modify the example libraries to fit your own.
• Client-side Scripts – Finally, create the import/export HTML pages.

LARGE CSV FILE UPLOAD

Having trouble uploading a large CSV file? Here is how to do it:

3 Steps to Upload Large Files With PHP and Javascript

The post 3 Steps to Import Export Csv With PHP MySQL appeared first on Code Boxx.