Simple import / export to CSV for PHP & MySQL

During the development of the service for calculating inventory management statistics for online stores , the task arose of quickly organizing table import / export between different MySQL servers. Since it was necessary to do it simply and transparently - optimization will be ahead - I decided to use the author's recommendation from the MySQL 5.0 documentation.

As the data exchange format, I decided to adopt CSV precisely because of the simplicity of implementation.

In the end, it turned out two functions

Export table ( MySQL syntax description )

function export_csv( $table, //     $afields, //   -    $filename, //  CSV     // (   web-) $delim=',', //    CSV  $enclosed='"', //     $escaped='\\', //     $lineend='\\r\\n'){ //      CSV $q_export = "SELECT ".implode(',', $afields). " INTO OUTFILE '".$_SERVER['DOCUMENT_ROOT'].$filename."' ". "FIELDS TERMINATED BY '".$delim."' ENCLOSED BY '".$enclosed."' ". " ESCAPED BY '".$escaped."' ". "LINES TERMINATED BY '".$lineend."' ". "FROM ".$table ; //   ,      if(file_exists($_SERVER['DOCUMENT_ROOT'].$filename)) unlink($_SERVER['DOCUMENT_ROOT'].$filename); return mysql_query($q_export); } 

Comments

• The file can be created on the same host where MySQL is located. If the OS is configured with the ability to share a network drive, you can write to another server.
• If the field in the table is NULL , \ N will be displayed in the CSV file.
• To write a file to a local disk on a server, the user requires FILE rights not at the database level, but globally at the MySQL server level. Can be set via PHPMyAdmin or request

   GRANT FILE ON * . * TO 'username'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ; 

• To write a file to a directory, the directory must have write permissions for the mysql user or stand attributes 757 or 777 (recording is allowed for other users )

')
Import table ( MySQL syntax description )

  function import_csv( $table, //     $afields, //   -    $filename, //  CSV ,    // (   web-) $delim=',', //    CSV  $enclosed='"', //     $escaped='\\', //     $lineend='\\r\\n', //      CSV $hasheader=FALSE){ //    CSV if($hasheader) $ignore = "IGNORE 1 LINES "; else $ignore = ""; $q_import = "LOAD DATA INFILE '". $_SERVER['DOCUMENT_ROOT'].$filename."' INTO TABLE ".$table." ". "FIELDS TERMINATED BY '".$delim."' ENCLOSED BY '".$enclosed."' ". " ESCAPED BY '".$escaped."' ". "LINES TERMINATED BY '".$lineend."' ". $ignore. "(".implode(',', $afields).")" ; return mysql_query($q_import); } 

What do we have in the end?

1. Short and very fast functions, due to the fact that they are executed by one MySQL request.
2. A fairly flexible implementation - you can easily manage multiple parameters, including the list of fields.
   
   • For export : by changing the list of fields in an array of fields

      $afields 

     or using a subquery instead of the table name (then the array will contain the fields of this subquery) - for example,

      $atable 

     will look like this

      (select field1, field1 from table2) t 

   • To import : by using a custom variable to skip unnecessary fields — for example,

      array("column1", "@dummy", "column2", "@dummy", "column3") 

     will skip the second and fourth fields in the CSV file.

Thus, the issue of simplicity and speed of development resolved. And when the question of speed and efficiency arises, it will be possible to tackle optimization.

Ps. In fact, these MySQL commands have a richer syntax with additional settings, so the field for improving this code is limited only by necessity and fantasy.