j.smith1981 Posted September 21, 2009 Share Posted September 21, 2009 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. Quote Link to comment Share on other sites More sharing options...
syed Posted September 21, 2009 Share Posted September 21, 2009 When you say you have a problem, what exactly is the problem. Quote Link to comment Share on other sites More sharing options...
j.smith1981 Posted September 22, 2009 Author Share Posted September 22, 2009 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)? Quote Link to comment Share on other sites More sharing options...
tomo11 Posted November 17, 2009 Share Posted November 17, 2009 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); ?> Quote Link to comment Share on other sites More sharing options...
bossman Posted November 17, 2009 Share Posted November 17, 2009 good stuf Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.