Data to CSV Function

[PCHomepage]

I am working on a small function to create CSV output from different mysqli queries and it is giving no errors itself but it seems to, for some strange reason, generate a JPGraph error. This function has nothing to do with JPGraph so did I inadvertently use a reserved word for that library?

Also, the resulting CSV seems to contain the HTML content of the page calling the function so clearly something is wrong.

As it is now it just prompts to save the file but I need it to provide a link for doing so.

Any ideas?

function Data_2CSV($Query, $headerDisplayed = false, $mysqli) {
	$fileName = 'DataDump.csv';

	header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
	header('Content-Description: File Transfer');
	header("Content-type: text/csv");
	header("Content-Disposition: attachment; filename={$fileName}");
	header("Expires: 0");
	header("Pragma: public");
 
	$fh = @fopen( 'php://output', 'w' );

	if ($result = $mysqli->query("$Query")):
		while ($row = $result->fetch_array()):
			if ( !$headerDisplayed ):
				fputcsv($fh, array_keys($data));
				$headerDisplayed = true;
			endif;
    		fputcsv($fh, $data);
		endwhile;
    endif;
}

 

[jpadie]

that's a bit confused.
instead of opening a php stream, open a normal file stream. write to the file and then once that has finished use this as the last line of the script

readfile( $fileName );
exit; //always good to kill the stream expressly

 

[PCHomepage]

Yes, it came from several unrelated functions that I had created through the years for various things and, as I've gotten so rusty in PHP from lack of using it, it was just a starting point.

Although I think I understand, I'm not totally sure as there is no physical file and I don't want to save one anywhere until the user wants it. When they do, I want them to be prompted for where they want it to be saved.

 

[jpadie]

actually this morning I think that your original way of using php://output (or similar) is a good one.

so try this instead

[b][COLOR=#0000FF]function[/color][/b] [b][COLOR=#000000]Data_2CSV[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$Query[/color][COLOR=#990000],[/color] [COLOR=#009900]$mysqli[/color][COLOR=#990000])[/color] [COLOR=#FF0000]{[/color]
[tab][tab][COLOR=#009900]@ob_end_clean[/color][COLOR=#990000]();[/color]
[tab][COLOR=#009900]$fileName[/color] [COLOR=#990000]=[/color] [COLOR=#FF0000]'DataDump.csv'[/color][COLOR=#990000];[/color]
[tab][b][COLOR=#000000]header[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]"Cache-Control: must-revalidate, post-check=0, pre-check=0"[/color][COLOR=#990000]);[/color]
[tab][b][COLOR=#000000]header[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]'Content-Description: File Transfer'[/color][COLOR=#990000]);[/color]
[tab][b][COLOR=#000000]header[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]"Content-Type: text/csv"[/color][COLOR=#990000]);[/color]
[tab][b][COLOR=#000000]header[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]"Content-Disposition: attachment; filename=\"$fileName\""[/color][COLOR=#990000]);[/color]
[tab][tab][b][COLOR=#000000]header[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]"Content-Transfer-Encoding: binary"[/color][COLOR=#990000])[/color]
[tab][b][COLOR=#000000]header[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]"Expires: 0"[/color][COLOR=#990000]);[/color]
[tab][b][COLOR=#000000]header[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]"Pragma: public"[/color][COLOR=#990000]);[/color]
 
[tab][COLOR=#009900]$fh[/color] [COLOR=#990000]=[/color] [COLOR=#009900]@fopen[/color][COLOR=#990000]([/color] [COLOR=#FF0000]'php://output'[/color][COLOR=#990000],[/color] [COLOR=#FF0000]'wb'[/color] [COLOR=#990000]);[/color]
[tab][COLOR=#009900]$headerDisplayed[/color] [COLOR=#990000]=[/color] false[COLOR=#990000];[/color]
[tab][b][COLOR=#0000FF]if[/color][/b] [COLOR=#990000]([/color][COLOR=#009900]$result[/color] [COLOR=#990000]=[/color] [COLOR=#009900]$mysqli[/color][COLOR=#990000]->[/color][b][COLOR=#000000]query[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]"$Query"[/color][COLOR=#990000])):[/color]
[tab][tab][b][COLOR=#0000FF]while[/color][/b] [COLOR=#990000]([/color][COLOR=#009900]$row[/color] [COLOR=#990000]=[/color] [COLOR=#009900]$result[/color][COLOR=#990000]->[/color][b][COLOR=#000000]fetch_assoc[/color][/b][COLOR=#990000]()):[/color] [i][COLOR=#9A1900]//you need this to be associative only[/color][/i]
[tab][tab][tab][b][COLOR=#0000FF]if[/color][/b] [COLOR=#990000]([/color] [COLOR=#990000]![/color][COLOR=#009900]$headerDisplayed[/color] [COLOR=#990000]):[/color]
[tab][tab][tab][tab][b][COLOR=#000000]fputcsv[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$fh[/color][COLOR=#990000],[/color] [b][COLOR=#000000]array_keys[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$row[/color][COLOR=#990000]));[/color] [i][COLOR=#9A1900]// this should reference $row[/color][/i]
[tab][tab][tab][tab][COLOR=#009900]$headerDisplayed[/color] [COLOR=#990000]=[/color] true[COLOR=#990000];[/color]
[tab][tab][tab][b][COLOR=#0000FF]endif[/color][/b][COLOR=#990000];[/color]
[tab][tab][tab][tab][b][COLOR=#000000]fputcsv[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$fh[/color][COLOR=#990000],[/color] [COLOR=#009900]$row[/color][COLOR=#990000]);[/color] [i][COLOR=#9A1900]// this should also reference $row as $data is not in scope[/color][/i]
[tab][tab][b][COLOR=#0000FF]endwhile[/color][/b][COLOR=#990000];[/color]
[tab][tab][tab][tab][i][COLOR=#9A1900]//close the stream[/color][/i]
[tab][tab][tab][tab][COLOR=#009900]@fclose[/color][COLOR=#990000]([/color][COLOR=#009900]$fh[/color][COLOR=#990000]);[/color]
[tab][tab][tab][tab]exit;[i][COLOR=#9A1900]//explicitly exit the script to prevent extra characters[/color][/i]
[tab][b][COLOR=#0000FF]endif[/color][/b][COLOR=#990000];[/color]
[COLOR=#FF0000]}[/color]

If you are still getting extraneous html in the output then you need to look elsewhere in your script for output. however, if that were true, then you'd also be getting big errors when you call header(). It should be screaming "Headers already sent ..." at you each line.

 

[PCHomepage]

Thank you and I'll give it a try when I'm back in the office shortly. No, there were no header errors but it was surprising to see the PHP-generated HTML source code in the CSV. I realized also that I forgot to close the dataset: [bold]$result->close();[/bold]

In the meantime, I am looking for a way to create a link or form button of some sort at the bottom of the page that contains the graph where the user can click to create and download the CSV that this function generates rather than just simply having it run. I spent a good deal of time researching online but have found nothing that works. I had thought of submitting the query through a POST which might do it. What do you think?

 

[PCHomepage]

With the $Query value hard-coded into the document containing the function, it gives a server error until I remark out the first line [bold]@ob_end_clean();[/bold] so it seems it doesn't like it. With it remarked out, it prompts to save the file but the resulting file has a .php extension and contains only the query itself. Any ideas?

 

[PCHomepage]

Oops! My mistake as it wasn't getting the $mysqli connection. It is now prompting to save or open the file and it contains the proper data but Excel complains that the data is not in a format to match the .CSV file extension. It says it is an SYLK file and apparently the error is to do with the fact that the first header cell contains "ID" in upper case. Apparently it is an Excel bug but it's one I had never come across before.

 

[jpadie]

change this then

[b][COLOR=#000000]fputcsv[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$fh[/color][COLOR=#990000],[/color] [b][COLOR=#000000]array_keys[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$row[/color][COLOR=#990000]));[/color] [i][COLOR=#9A1900]// this should reference $row[/color][/i]

to

[b][COLOR=#000000]fputcsv[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$fh[/color][COLOR=#990000],[/color] [b][COLOR=#000000]array_map[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]'strtolower'[/color][COLOR=#990000],[/color] [b][COLOR=#000000]array_keys[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$row[/color][COLOR=#990000])[/color] [COLOR=#990000])[/color] [COLOR=#990000]);[/color] [i][COLOR=#9A1900]// this should reference $row[/color][/i]