Output csv straight to browser

[Drongo_III]

Hi Guys

 

Another noob question.

 

I have written a script to create a csv file from a mysql query. Bread and butter you might think...

 

The script i've written (based on reading a few bits and a few bits there) creates the csv file in the same directory as the php script. I don't really want this to happen because the data it's pulling is a little sensitive.

 

So  my questions:

 

[*]How can i stop the csv from storing itself locally?

[*]How can i output the csv directly to the browser - i.e. to initiate a download automatically

[*]You'll notice that in the code I have a while loop within which i run a function to trim the data results as they are pulled down before placing them in the csv. I suspect that recursively calling this function is probably not the most efficient way of doing it. Any suggestions on how this should be done? I don't expect you to code it (unless you really want to) just an explanation to point me in the right direction would be fab.

 

Any help would be very appreciated.

 

Drongo

 

<?php


require_once("config.php");



$select = "SELECT * FROM members"; 

$export = mysql_query ( $select )
or die ( "Sql error : " . mysql_error( ) ); 


$fields = mysql_num_fields ( $export ); 

for ( $i = 0; $i < $fields; $i++ )

{   
$header .= mysql_field_name( $export , $i ) . ","; }


echo $header;

$headings = explode(",", $header);


//Set headers in first line of csv.

$fp = fopen('test.csv', 'w');

    fputcsv($fp, $headings);
fclose($fp);








// Function to trim all values. Remember the pass by reference to change original value.

function trim_all(&$value)
{
  if (is_array($value))
  { 
array_walk_recursive($value, 'trim_all');
  }
  else
  { 
$value = trim(str_replace("\r\n", "\n", $value));
  }
}





//open file for writing
$dp = fopen('test.csv', 'a+');
                                 
                              // ignore this line
mysql_data_seek($export, 0);

// while loop runs trim on data and stores each array as csv row
while($rows = mysql_fetch_row($export))
  {
  array_walk_recursive($rows, 'trim_all');
  print_r($rows);
  fputcsv($dp, $rows);
  }
 
fclose($dp);



?>

[btellez]

To have the CSV file sent to the user as a download you can specify some headers in php

//info.csv is the file name the browser will suggest to save file as its not a reference to any existing file
header('Content-Disposition: attachment; filename="info.csv"');
/*
then simply echo your csv contents instead of writing them to a file.
*/

[Drongo_III]

Thank Btellez

 

Explored a bit more into headers after your suggestion and got it working along what you said

 

  
header("Content-type: text/csv");
		header("Content-Disposition: attachment; filename=file.csv");
		header("Pragma: no-cache");
		header("Expires: 0");

		echo  $header . "\n" . $mydata;

 

 

One thing I wanted to clarify.  Is it ok to called functions from within while loops.  For instance in my script I call a function from the while loop to trim the data - is that ok or do you think it's bad practice as it'll consume resources?

 

Drongo

[btellez]

I believe your safe calling the array_walk_recursive function from within your while loop to trim your lines.

Your dealing with text, it really shouldn't be using that many resources unless your file has thousands upon thousands of lines being processed.

 

Note: I don't know if this function is implemented in a recursive manner, or whether the name refers to it repeating aspect; In any case I would think its been optimized to work in PHP since its a built in function, but I may be wrong.

[PFMaBiSmAd]

array_walk_recursive is recursive for arrays. The function definition that you pass it will never receive an array as data, so you don't need the extra is_array logic inside the trim_all function.

[Drongo_III]

So besides my dubious logic inside the trim function (thanks for explaining that bit) it's ok to call a function from a while loop like this?

 

 

array_walk_recursive is recursive for arrays. The function definition that you pass it will never receive an array as data, so you don't need the extra is_array logic inside the trim_all function.

[PFMaBiSmAd]

If you want to apply the function to each row of data that is retrieved, of course you would call it inside the loop.

[Drongo_III]

ok...  :'(

 

 

 

If you want to apply the function to each row of data that is retrieved, of course you would call it inside the loop.