Category: mysql

MySQL export to .csv

The elusive MySQL export to CSV *WITH* headers.

It took a while, review of a number of weblogs, support sites, and error message causes but export from MySQL to .csv with the column titles is finally working!

SELECT convert('Title' using utf8mb4) 'Title',
	convert('Year' using utf8mb4) 'Year',
	convert('Time' using utf8mb4) 'Time',
	convert('Rating' using utf8mb4) 'Rating',
	convert('Genre' using utf8mb4) 'Genre'  
UNION ALL
SELECT * from 
	(SELECT * from movies_i_want order by Title) a
INTO OUTFILE '/var/lib/mysql-files/movies_i_want.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"';

and the file it produces…

"Title","Year","Time","Rating","Genre"
"Amazon Women on the Moon","1987","85","R","Comedy,Sci-Fi"
"Blade of the Immortal","2017","140","R","Action,Drama"
"Bound","1996","108","NR","Crime,Romance,Thriller"
 ...

The first SELECT above produces a single row table with the column’s titles as the field values in the row. The table is not named.

mysql> SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre';
+-------+------+------+--------+-------+
| Title | Year | Time | Rating | Genre |
+-------+------+------+--------+-------+
| Title | Year | Time | Rating | Genre |
+-------+------+------+--------+-------+

The full SELECT expression uses convert('Title' using utf8mb4) 'Title'… for each field because UNION failed from incompatible collations between the first SELECT’s row and the “SELECT ... movies_i_want” statement.

Collations can be associated with one character set. A character set can be associated with many collations. For collations to match, both character sets must match. The purpose of the first SELECT is to emit the column titles for the .csv export. It produces only one row, the column titles. Converting that SELECT’s character set to match the character set used in the live data table permitted the collation the UNION was doing to work.

Once the collations were compatible it was necessary to get each column’s title into the output as the first row of the output. The view used for the second SELECT has the same column titles in the same order as the first select. Its data needs to be the 2nd and subsequent rows.

After many attempts, several ways to state the problem were found. Unfortunately none included the column titles in the output. The column titles are a must have for free wpDataTables to show headings on the tables it displays from reading the .csv files.

ignoring the comment about collation to simplify the statements...
The following both work except for no column titles

SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre'  
UNION ALL
SELECT * 
FROM movies_i_want order by Title;

SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre'  
UNION ALL
SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre' 
FROM movies_i_want order by Title;

To include the column titles in the output three SELECTs are needed. And be sure to include an alias for the ‘derived table’, the one created by the second SELECT.

SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre'  
UNION ALL
SELECT * FROM (SELECT * FROM movies_i_want order by Title) a;

Now the output can be created in the necessary format using native MySQL. The method is a bit dense but I think simpler than the PHP, Use PHP to export MySQL to .csv.

With this and the PHP method of exporting the data a trigger is need to cause it to happen, either method can run on demand, or either method can run on a schedule.

A trigger, in other words “only when needed”, is my preferred approach. MySQL can be triggered by INSERT, UPDATE, and DELETE.

Solutions:

A MySQL trigger runs a SQL user procedure to export the data.
Problem: export destinations are limited to a single location with very restrictive access and it is not the location where either .csv file for the web tables is.

A MySQL trigger runs an external program, the PHP, to export the data.
Problem: can a trigger launch an external program?

Use PHP to export MySQL to .csv

The wpDataTables update when the source .csv files change. Next, update the source .csv files when the movies database is updated.

To update the Movies tables the underlying .csv source files must be updated and the wpDataTables tables’ links with those files must be refreshed. Without the link refresh the table doesn’t update even if the webpage is refreshed.

The .csv source files for the “Movies I Have” and “Movies I Want” tables are monitored for changes. The wpDataTables links are refreshed when the files have changed. The table display will update the next time the page is refreshed or landed on.

Another step in the chain is a database update, (INSERT, UPDATE, or DELETE), triggering the .csv file to be updated. And to make that happen, need to be able to do several things

  • Figure out how to create and trigger an event
    • Trigger the the event after INSERT, UPDATE, or DELETE on movies database
  • Write trigger(s) to a) export the new data and hopefully b) update the .csv files
  • Figure out HOW? to update the .csv files
    • PL/SQL export, then use non SQL program to update files with the export
    • PHP program that extracts the data and overwrites the .csv files
    • Program (PHP, PL/SQL) extracts the data to monitored files. Another program updates the .csv files with the data when the monitored files change.

I’ve put together the PHP to extract the data. Also need to see what can be done with SELECT * INTO OUTFILE. PHP may be able to do the whole process, extract records and update .csv file. MySQL may be able to do it with INTO OUTFILE but I think that’s very limited the destinations it can write to.

<?php
 $server = "myserver.boba.org"; 
 $username = "fan";
 $password = xx; 
 $database_name = "the_movies"; 
 $link_myMovies = mysqli_connect($server, $username, $password, $database_name);
 $Views = array("movies_i_want","movies_i_have");
 $out_path = "/var/tmp/";
 
 foreach ($Views as $view)
 {
	$query_result = null;
	$columns_total = null;
 	$str_query = "SELECT * FROM $view";
 	$query_result = mysqli_query($link_myMovies, $str_query);
 	$columns_total = mysqli_num_fields($query_result);
 
 	$col_names = array();
 
 	for ($i = 0; $i < $columns_total; $i++) 
	 {
	 	$Heading = mysqli_fetch_field_direct($query_result, $i);
	 	array_push($col_names,$Heading->name);
	 }
 
	 $fileOut = fopen("$out_path$view.csv", 'w') or die("Unable open ./$out_path$view.csv"); 
	 fputcsv($fileOut, $col_names);

	 while ($row = mysqli_fetch_array($query_result, MYSQLI_NUM)) 
	 {
	 	fputcsv($fileOut, array_values($row)); 
	 }
	 
	 fclose($fileOut) or die("Unable to close ./$view.csv");
}
?>