I have a request from a customer to place items concatted by a group_concat onto separate lines of a report. I've tried the obvious separator choices, like '\n', '\r\n', '' and none of these are working.
Does anyone have a solution that works with MyDBR?
Here is the current code snippet I'm using:
set @q = CONCAT("select
o.transaction_id AS `Transaction ID`,
o.invoice_number AS `Order Invoice Number`,
iop.order_id AS `Order Number`,
concat(u.last_name, ', ', u.first_name) AS `Name`,
group_concat(strip_html_tags(p.name) separator '\r\n') AS `Products`,
o.paid_total AS `Price Paid`,
iop.refund_amount AS `Refund Amount`,
DATE_FORMAT(from_unixtime(o.timestamp), '%Y-%m-%d') AS `Date Ordered`
from ", CustName, "_c2.orders `o`
join ", CustName, "_c2.users `u` ON (u.id = o.user_id)
join ", CustName, "_c2.i_orders_products `iop` ON (iop.order_id = o.id)
join ", CustName, "_c2.products `p` ON (p.id = iop.product_id)
where ((o.paid_total > 0) and (iop.retail_price > 0) and (from_unixtime(o.timestamp) between '", StartDate, " 00:00:00' and '", EndDate, " 23:59:59.999'))
group by o.id
order by o.timestamp");
PREPARE stmt FROM @q; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Any pointers would be greatly appreciated.
