Export SQL Query to CSV file

Piece Code Snippets

Export SQL Query to CSV file

This function enables you to generate a CSV file based on any SQL query.

It returns the filename (including path) so you can decide to download it to your clients computer.

The $sql parameter contains your SQL Query.

The optional $del contains the delimiter string. It defaults to comma (,)

You can insert this function in the Before Form pieces of your form. Beware: every time this function is executed, a file is created in the exports directory of the Facile Forms component on your server. There's no mechanism in this function to clean-up that directory

function ff_export2csv($sql, $del)

// This function takes an SQL query and the field delimiter
// to send the result of the query to a file on the client's computer
  	
{
	global $database, $file, $ff_compath, $ff_version, $mosConfig_fileperms;
        // set the filename to a kind of unique
        // in the exports directory under com_facileforms
        $filename = $ff_compath.'/exports/export-'.date('YmdHis').'.csv';
        
        $existed = file_exists($filename); // test the file
	if ($existed) {
		$permission = is_writable($filename);
		if (!$permission) {
			echo "<script> alert('Exportfile not writeable!'); window.history.go(-1);</script>\n";
			exit();
		} // if
	} // if

	$file=fopen($filename, "w");
	
	$database->setQuery($sql);
  	$rows = $database->loadObjectList();
  	if ($database->getErrorNum()) {
		echo $database->stderr();
		return false;
	} // if

        if (!count($rows)) echo "<script> alert('No records found to export!');</script>\n";
  	
  	if (!($del)) $del=", "; // set delimiter to a default value if not set...

	$data=""; // var to hold the processed results
	// lines in the csv file
	foreach ($rows as $row) {
                // only the first field does not get a delimiter
		$writeDelimiter = FALSE;

	        // fields in the csv file
     		foreach ($row as $field) {
	     		

     			// Replaces a double quote with two double quotes
           		$field=str_replace("\"", "\"\"", $field);
           		
           		// Adds a delimiter before each field (except the first)
           		if($writeDelimiter) $data .= $del;
           		
     			$data .= $field;
     			$writeDelimiter = TRUE;
    		} // foreach
    		// add a newline to every line
    		$data .= "\n";
  	} // foreach
	
	fwrite($file, $data);
	fclose($file);

	if (!$existed) {
		$filemode = NULL;
		if (isset($mosConfig_fileperms)) {
			if ($mosConfig_fileperms!='') $filemode = octdec($mosConfig_fileperms);
			} else 	$filemode = 0644;
			if (isset($filemode)) @chmod($filename, $filemode);
	} // if

	
	return $filename;
	
} // end function

--Fvds 05:18, 1 September 2006 (EDT)