PHP Header Attachment Alternative (convert to CSV file)

[j.smith1981]

Hello,

 

I have a problem with the following code:

 

<?php

$connect = mysql_connect('localhost', 'xcart', 'xcart0r'); // Connects to MySQL Server
$db_select = mysql_select_db('xcart', $connect); // Selects X Cart db

$filename = "products_Orders_" . date('HidmY') . ".csv"; // Makes the file name variable with date!

function parseCSVComments($comments) {
$comments = str_replace('"', '""', $comments); // Escape all " and replace with ""
if(eregi(",", $comments) or eregi("\n", $comments)) { // Checks if there's any commas or new lines
return '"'.$comments.'"'; // If there's any commas or new lines escape them!
} else {
return $comments; // If no new lines or commas just return the value
}
}

$sql = mysql_query("SELECT A4.value AS adventcode, A1.productcode, A1.product, SUM(A2.price) AS cost_price, SUM(A1.amount) AS qty, SUM(A2.price * A1.amount) AS grand_total, DATE_FORMAT(FROM_UNIXTIME(A3.date), '%e/%c/%Y') AS order_date
FROM xcart_order_details A1
LEFT JOIN xcart_pricing A2 ON A1.productid = A2.productid
LEFT JOIN xcart_orders A3 ON A1.orderid = A3.orderid
LEFT JOIN xcart_extra_field_values A4 ON A1.productid = A4.productid
WHERE A2.membershipid = '2' AND A3.status = 'P' AND  A4.fieldid = '3'
GROUP BY A1.productid
ORDER BY A1.orderid AND A1.productid ASC");

$numberFields = mysql_num_fields($sql); // Set out number of fields we are actually fetching should be (4) with any luck!

if($numberFields) { // Checks if we can output anything or we need to?
for($i=0; $i<$numberFields; $i++) {
	$head[] = mysql_field_name($sql, $i); // Create the headers for each column, this is the field name in the database
}
$headers = join(',', $head)."\n"; // Makes the header row in the CSV file

while($info = mysql_fetch_object($sql)) {
	foreach($head as $fieldName) {
	$row[] = parseCSVComments($info->$fieldName);
	} // Ends foreach loop
	$data .= join(',', $row)."\n"; // Create a new row of data and append it to the last row
	$row = ''; // Clear the contents of the $row variable to start a new row
}
// Start out actual output of the CSV file!
header("Content-type: application/x-msdownload");
//	header("Content-Disposition: attachment; filename=purchase_Report.csv"); // OLD FILENAME!
header("Content-Disposition: attachment; filename=\"$filename\""); // New file name with date added automatically!
header("Pragma: no-cache");
header("Expires: 0");
echo $headers.$data;
} else {
// Nothing needed to be output. Put an error message here or something.
echo 'No data available for this CSV.';
}
?>

 

Is there an easier way of when a user clicks the button to make the csv file save to the users hard drive?

 

I am really stuck with this, basically I am going to be wanting to automate this so a user can click on an application exe, run a macro that saves the above report as a csv file on their computer, so its seamless basically, is this possible using maybe some other language aswell?

 

I am not exactly sure how to go about this so any help is greatly appreciated.

 

Thanks,

Jeremy.

[syed]

When you say you have a problem, what exactly is the problem.

[j.smith1981]

Its probably more to do with VBA actually.

 

But I wondered if I could get it at both angles to work this one out really.

 

Basically that query, brings out a CSV file in the header of the page and asks the user to save (from an attachment with html).

 

Is there anyway of making a program to make it save to a users local computer to keep (as it where)?

[tomo11]

This script prompts the user and asks where to save the .csv file:

 

<?php

$host="localhost";

$user="xcart";

$password="xcart0r";

$database="xcart";

//$table="table_name";

 

$connect = mysql_connect($host,$user,$password);

if (!$connect)

  {

  die('Could not connect: ' . mysql_error());

  }

mysql_select_db($database, $connect);

 

  header($outtype);

  $outtype = 'Content-disposition: attachment; filename="x.csv"';

   

$result = mysql_query("SELECT A4.value AS adventcode, A1.productcode, A1.product, SUM(A2.price) AS cost_price, SUM(A1.amount) AS qty, SUM(A2.price * A1.amount) AS grand_total, DATE_FORMAT(FROM_UNIXTIME(A3.date), '%e/%c/%Y') AS order_date

FROM xcart_order_details A1

LEFT JOIN xcart_pricing A2 ON A1.productid = A2.productid

LEFT JOIN xcart_orders A3 ON A1.orderid = A3.orderid

LEFT JOIN xcart_extra_field_values A4 ON A1.productid = A4.productid

WHERE A2.membershipid = '2' AND A3.status = 'P' AND  A4.fieldid = '3'

GROUP BY A1.productid

ORDER BY A1.orderid AND A1.productid ASC");

 

 

 

while($row = mysql_fetch_array($result))

  {

  echo $row['date'] . "," . ;  //put your desired columns here to show

  echo "\n";  //changes line/row in

  }

 

$fname = ('testFile.csv');

$fp = fopen($fname,'w');

fwrite($fp,"");  // $csvdata --> "" (empty also works)

fclose($fp);

 

 

header('Content-type: application/csv');    // /octet-stream for /csv works as well

header("Content-Disposition: attachment; filename=".$fname);  //inline --> attachment

readfile($fname);

     

 

mysql_close($connect);

?>

 

[bossman]

good stuf