Jump to content

Output csv straight to browser


Drongo_III

Recommended Posts

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);



?>

Link to comment
Share on other sites

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.
*/

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.