Report Builder find carriage return and replace with ","
6 posts by 3 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: August 8, 2016 (RSS)
By Mikey - August 2, 2016 - edited: August 2, 2016
I'm trying to build a report in Report Builder from a section editor called "form_submission".
In the reports is a column called "results" which contain user details from forms they submit. Below is an example:
First Name: John Last Name: Doe Address: 123 Main St. Address Cont: Suite 100 # 22 City: New York State: NY Zipcode: 10000 Age Group: 30-39 Electronic Signature: John Doe I acknowledge and agree to the Terms of Use: 1
I need to convert the column "results" into individual columns using "," to separate the entries like so:
"First Name: John Last", "Name: Doe", "Address: 123 Main St.", "Address Cont: Suite 100 # 22", "City: New York", "State: NY", "Zipcode: 10000", "Age Group: 30-39", "Electronic Signature: John Doe", "I acknowledge and agree to the Terms of Use: 1"
Here is what I have created so far in Report Builder:
SELECT
*
FROM `<?php echo $TABLE_PREFIX ?>form_submissions`
REPLACE(results,\r\n,",")
WHERE true
LIMIT 1000
But it doesn't work, and throws the following error:
MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REPLACE(results,\r\n,",") WHERE true LIMIT 1000' at line 4
I've also attached a snapshot of the header row of the report showing all the other columns within this report.
Anyone have any suggestions, or can point me in the right direction to get this working?
Thanks, Zick
By Djulia - August 3, 2016
Hi,
That can perhaps help you.
http://stackoverflow.com/questions/9362213/i-need-to-find-and-replace-n-in-a-mysql-field
Djulia
By Daryl - August 5, 2016
Hi Zick,
You need to call the REPLACE function as you select the column, ie:
SELECT
num as 'Num',
REPLACE(results,\r\n,",") as 'Results'
FROM `<?php echo $TABLE_PREFIX ?>form_submissions`
WHERE trueLIMIT 1000
Please let me know it that works for you.
Thanks!
PHP Programmer - interactivetools.com
By Mikey - August 5, 2016
Hey Daryl,
I think I understand where you're going. I gave the solution you provided a try and I get the following message:
MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\r\n,",") as 'Results' FROM `cms_form_submissions` WHERE true LIMIT 1000' at line 3
I really don't know MySQL that well, so anything I had written is a complete stab in the dark and is based on suggestions I've found on the forum. So it's very likely what I had in the original post for the MySQL had issues to start with.
Thanks, Zick
By Mikey - August 5, 2016
Hey Daryl,
I was able to eliminate the syntax error with a slight change. by wrapping the \r\n in quotes '\r\n'
SELECT
num as 'Num',
REPLACE(results,'\r\n',',') as 'Results'
FROM `<?php echo $TABLE_PREFIX ?>form_submissions`
WHERE true
LIMIT 1000
and I got closer to the desired results of splitting the entire "Results" into columns by dropping \r and using '\n', '","'
SELECT
num as 'Num',
REPLACE(results, '\n', '","') as 'Results'
FROM `<?php echo $TABLE_PREFIX ?>form_submissions`
WHERE true
LIMIT 1000
The results I get isn't finished but it's a step closer.
Zick
By Daryl - August 8, 2016
Ahh yes, sorry about the typo.
Please let me know if you have any questions.
Thanks!
PHP Programmer - interactivetools.com