• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

MySql CSV Export Orders table with blank line between orders

Hi,

I have a MySql database with customer orders.  I am trying to export this table into CSV format, which is working ok.  I need to add a blank line in the CSV between each order ID.  Can anyone reccommend the best way of doing this?

Should I use a for each loop inside the while loop?
while($row_rsOrders = mysql_fetch_assoc($rsOrders)) {
 
$content_file .= $row_rsOrders['fldCustomerLastName'] . ',' . $row_rsOrders['fldCustomerFirstName'] . ',' . $row_rsOrders['fldOrderShipAddress1'] . ',' . $row_rsOrders['fldOrderShipAddress2'] . ',' .  '' . ',' .  '' . ',' .  'X' . ',' .  $row_rsOrders['fldOrderID'] . ',' .   $row_rsOrders['fldOrderDate'] . ',' .  '' . ',' . '' . ',' .  'P' . ',' .  $row_rsOrders['fldDetailItemID'] . ',' .  $row_rsOrders['fldDetailQuantity'] . ',' .  '' . ',' . $row_rsOrders['fldDetailPrice'] . ',' .  '0' . ',' .  $row_rsOrders['fldLineTotal'] . ',' . $row_rsOrders['fldLineTotal'] . ',' . '' . ',' . '' . ',' . 'Sales Orders from website' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' .'GST' . ',' . '' . ',' . $row_rsOrders['fldOrderTax'] . ',' . '' . ',' . $row_rsOrders['fldOrderShipping'] . ',' . $row_rsOrders['fldOrderShipping'] . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . 'O' . ',' . '' . ',' . '' . ',' . '' . ',' . $row_rsOrders['fldOrderDisc'] . ',' . '' . ',' . $row_rsOrders['fldOrderTotal'] . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . "\r\n";
}
$output_file = 'Orders.csv';
@ob_end_clean();
@ini_set('zlib.output_compression', 'Off');
header('Pragma: public');
header('Last-Modified: '.gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: no-store, no-cache, must-revalidate');
header('Cache-Control: pre-check=0, post-check=0, max-age=0');
header('Content-Transfer-Encoding: none');
//This should work for IE & Opera
header('Content-Type: application/octetstream; name="' . $output_file . '"');
//This should work for the rest
header('Content-Type: application/octet-stream; name="' . $output_file . '"');
header('Content-Disposition: inline; filename="' . $output_file . '"');
echo $content_file;
exit();

Open in new window

0
Pro-TechIT
Asked:
Pro-TechIT
  • 2
  • 2
1 Solution
 
Michael701Commented:
where you have this at the end of your line

. "\r\n";


just add more

. "\r\n\r\n";
0
 
Pro-TechITAuthor Commented:
Thanks for your reply Michael,

I may have two or more lines with the same OrderId if there are more than one product oredred.  i need a line between each uniqu fld OrderID.

I was also thinkning of using an if statement to check if a variable = the current fldOredrID inside the while loop and if it does then add the second line break.
while($row_rsOrders = mysql_fetch_assoc($rsOrders)) {
 
$lastOrder = '';
 
if ($row_rsOrders['fldOrderID'] == $lastOrder){
$content_file .= $row_rsOrders['fldCustomerLastName'] . ',' . $row_rsOrders['fldCustomerFirstName'] . ',' . $row_rsOrders['fldOrderShipAddress1'] . ',' . $row_rsOrders['fldOrderShipAddress2'] . ',' .  '' . ',' .  '' . ',' .  'X' . ',' .  $row_rsOrders['fldOrderID'] . ',' .   $row_rsOrders['fldOrderDate'] . ',' .  '' . ',' . '' . ',' .  'P' . ',' .  $row_rsOrders['fldDetailItemID'] . ',' .  $row_rsOrders['fldDetailQuantity'] . ',' .  '' . ',' . $row_rsOrders['fldDetailPrice'] . ',' .  '0' . ',' .  $row_rsOrders['fldLineTotal'] . ',' . $row_rsOrders['fldLineTotal'] . ',' . '' . ',' . '' . ',' . 'Sales Orders from website' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' .'GST' . ',' . '' . ',' . $row_rsOrders['fldOrderTax'] . ',' . '' . ',' . $row_rsOrders['fldOrderShipping'] . ',' . $row_rsOrders['fldOrderShipping'] . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . 'O' . ',' . '' . ',' . '' . ',' . '' . ',' . $row_rsOrders['fldOrderDisc'] . ',' . '' . ',' . $row_rsOrders['fldOrderTotal'] . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . "\r\n" . "\r\n";
 
$lastOrder = $row_rsOrders['fldOrderID'];
 
}
 
 
 
else {
$content_file .= $row_rsOrders['fldCustomerLastName'] . ',' . $row_rsOrders['fldCustomerFirstName'] . ',' . $row_rsOrders['fldOrderShipAddress1'] . ',' . $row_rsOrders['fldOrderShipAddress2'] . ',' .  '' . ',' .  '' . ',' .  'X' . ',' .  $row_rsOrders['fldOrderID'] . ',' .   $row_rsOrders['fldOrderDate'] . ',' .  '' . ',' . '' . ',' .  'P' . ',' .  $row_rsOrders['fldDetailItemID'] . ',' .  $row_rsOrders['fldDetailQuantity'] . ',' .  '' . ',' . $row_rsOrders['fldDetailPrice'] . ',' .  '0' . ',' .  $row_rsOrders['fldLineTotal'] . ',' . $row_rsOrders['fldLineTotal'] . ',' . '' . ',' . '' . ',' . 'Sales Orders from website' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' .'GST' . ',' . '' . ',' . $row_rsOrders['fldOrderTax'] . ',' . '' . ',' . $row_rsOrders['fldOrderShipping'] . ',' . $row_rsOrders['fldOrderShipping'] . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . 'O' . ',' . '' . ',' . '' . ',' . '' . ',' . $row_rsOrders['fldOrderDisc'] . ',' . '' . ',' . $row_rsOrders['fldOrderTotal'] . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . '\r\n' . '\r\n';
 
$lastOrder = $lastOrder + 1;
 
}
}
$output_file = 'Orders.csv';
@ob_end_clean();
@ini_set('zlib.output_compression', 'Off');
header('Pragma: public');
header('Last-Modified: '.gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: no-store, no-cache, must-revalidate');
header('Cache-Control: pre-check=0, post-check=0, max-age=0');
header('Content-Transfer-Encoding: none');
//This should work for IE & Opera
header('Content-Type: application/octetstream; name="' . $output_file . '"');
//This should work for the rest
header('Content-Type: application/octet-stream; name="' . $output_file . '"');
header('Content-Disposition: inline; filename="' . $output_file . '"');
echo $content_file;
exit();

Open in new window

0
 
Michael701Commented:
Well that's confusing, Let me take a guess,

You'd like a blank line between different orders, but all lines of one order should be grouped together.

Was there a difference between those huge $content_file .= lines?
$lastOrder = '';
// this has to be OUTSIDE the while loop
 
while($row_rsOrders = mysql_fetch_assoc($rsOrders)) 
{
// the lastOrder!='' will prevent the blank line on the first pass
  if (($row_rsOrders['fldOrderID'] != $lastOrder) and ($lastOrder !=''))
    $content_file .= "\r\n";
 
$content_file .= $row_rsOrders['fldCustomerLastName'] . ',' . $row_rsOrders['fldCustomerFirstName'] . ',' . $row_rsOrders['fldOrderShipAddress1'] . ',' . $row_rsOrders['fldOrderShipAddress2'] . ',' .  '' . ',' .  '' . ',' .  'X' . ',' .  $row_rsOrders['fldOrderID'] . ',' .   $row_rsOrders['fldOrderDate'] . ',' .  '' . ',' . '' . ',' .  'P' . ',' .  $row_rsOrders['fldDetailItemID'] . ',' .  $row_rsOrders['fldDetailQuantity'] . ',' .  '' . ',' . $row_rsOrders['fldDetailPrice'] . ',' .  '0' . ',' .  $row_rsOrders['fldLineTotal'] . ',' . $row_rsOrders['fldLineTotal'] . ',' . '' . ',' . '' . ',' . 'Sales Orders from website' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' .'GST' . ',' . '' . ',' . $row_rsOrders['fldOrderTax'] . ',' . '' . ',' . $row_rsOrders['fldOrderShipping'] . ',' . $row_rsOrders['fldOrderShipping'] . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . 'O' . ',' . '' . ',' . '' . ',' . '' . ',' . $row_rsOrders['fldOrderDisc'] . ',' . '' . ',' . $row_rsOrders['fldOrderTotal'] . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . ',' . '' . "\r\n";
 
$lastOrder = $row_rsOrders['fldOrderID'];
}

Open in new window

0
 
Pro-TechITAuthor Commented:
Fantastic!  Thanks for your help Michael
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now