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

Attachments:

Screen Shot 2016-08-02 at 3.42.53 AM.png 40K

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!

Daryl Maximo
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!

Daryl Maximo
PHP Programmer - interactivetools.com