Use PHP to export MySQL to .csv

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