Question HTML textfields using commas in value for entry to MySQL table

IceQueen0607

Commendable
Nov 27, 2019
205
30
1,640
8
If I have a textfield with the contents 1.2.3.4.5 it's fine.
If I have the same textfield with contents 1,2,3,4,5 only 1 is retained, the rest is truncated.
How to be able to enter commas into a text field without truncation?
 

Ralston18

Titan
Moderator
The concept involved is referred to as a delimiter.

The software being used must know what character is being used to separate data values.

Rows and fields (columns) within that row/record.

Your sample textfield that works is probably from a source using CSV. Likely , 1.2.3.4.5 , and interpreted as a single data value. Note the commas.

CSV Comma Separated Value) is one method of separating data fields. So if a comma is used in the data the software will interpret that comma the next data value. Other characters can be used as delimiters but there are rules and restrictions.

So if the data is 1,2,3,4,5 that will interpreted as five different values. Depending on software any values after the first comma may be pushed into other fields or simply be truncated.

Another way of managing incoming data values is fixed format.

Reference (and you can easily find other such links):

https://towardsdatascience.com/parsing-fixed-width-text-files-with-pandas-f1db8f737276

If you are going to be working with lots of data then learning how to parse data of any sort is an important skill to have.

= = = =

There is a dependency on the software being used to import, process, and export the data. Often user configurable - you get to select (within given rules) what delimiter is to be used.

What software are you using?

Do a google search on "how to delimit data" then apply other search criteria and filters as necessary to narrow down the search results that match your software and specific requirements.

Most likely, you can insert quotes around the data if the data values contains commas. However, that has to be planned out in advance via the design process. Again, there are different ways of doing such things.


What software are you using? What is the source and format of the data you are trying to process? What do you have control over?

For example, if you have no control over the data being provided then you must manage the parsing of the data to accommodate data fields that contain commas as part of your processing requirements.

Fortunately, Excel, Access, etc. provide many functions to help solve such problems.
 

IceQueen0607

Commendable
Nov 27, 2019
205
30
1,640
8
Hey Ralston, thanks for the detailed explanation.

No, this is not incoming data from csv file.

I use Coldfusion as the server side code for managing a website using CFML and HTML. I have several tables that support comments. When entering comments I have to remember not to use commas in my text as it breaks the code.

For example I might enter (minus the quotes) "Player on SEBC unsps, tech foul for fosters #32".

As you say "Player on SEBC unsps" and "tech foul for Fosters #32" are treated as two separate values and consequently break the code. This is a HTML issue. At this point it has nothing to do with CFML or SQL.

In older languages I was able to easily overcome this, but with HTML I am still looking for a way around it.

Entering the string "Coach became agitated when he thought a player carried the ball, got a tech for his trouble" (with the quotes) didn't work. And yes, I know that sentence is not grammatically correct. The comma should be a full stop. I just added it as an example.

Input Page1.cfm
Code:
<cfform method="post" name="form1" action="page2.cfm"
<input type="text" readonly="readonly" style="border:none" name="game" value"#idgames#">
  <input type="text" name="comment" size="100" maxlength="512" class="pagetext">
  <input type="button" type="submit" value="Add comment">
</cfform>
The update page2.cfm
Code:
<cfif #len(FORM.comment)# gt 0>
  <cfquery name="addcomment" datasource="#Application.datasource#">
    update games set comment='#FORM.comment#' where idgames=#FORM.game#
  </cfquery>
</cfif>
<cflocation URL="page1.cfm?result=1">
 
Last edited:

Ralston18

Titan
Moderator
Okay - well out of my comfort zone now.

Also not concerned about grammatical correctness - notes are notes...

The problem is how to deal with unwanted comma's that appear in supporting comments.

So I did a bit of research (aka "Googling") with respect to ColdFusion and commas.

Search criteria being "How to use a comma in a .cfm file" for lack of anything more immediately precise. :)

Found this link:

https://stackoverflow.com/questions/4891262/coldfusion-form-array-with-comma-in-variable

And, then this link:

https://www.tek-tips.com/viewthread.cfm?qid=1245397

What occurred to me is that what you might be able to do is add a little bit of your own code that parses (when and as necessary ) to identify unwanted commas.

Swap in/replace some other character in place of the comma.

Very sure that you are well ahead of me in making something like that work via ColdFusion.
 

IceQueen0607

Commendable
Nov 27, 2019
205
30
1,640
8
Writing code to parse a string and replace characters is easy. Already done that.

The problem is (a). I want the commas in the string and to be written to the database and (b). The problem is the way that HTML interprests the strings. When it sees commas it splits the strings into separate variables.
 

Ralston18

Titan
Moderator
So data (string) with commas goes into the data base. That part is okay.

And then when that data is read by HTML the commas cause HTML to separate the string.

The problem is then how to tell HTML not to separate the string when commas are included.

Found some ideas

Pseudo-commas:

https://css-tricks.com/css-pseudo-commas/

Regex:

https://stackoverflow.com/questions/51757437/regex-to-capture-everything-between-two-strings-but-avoid-capturing-commas

However, the next link is much more in line with what I was thinking about - how to use double-quotes, etc..

Overall you will need a function of some sort to read the data base string containing commas, parse/replace the commas with perhaps double-quoted commas, then pass the end result to HTML.

And HTML will no longer see/interpret the commas as separators.

https://stackoverflow.com/questions/11456850/split-a-string-by-commas-but-ignore-commas-within-double-quotes-using-javascript

Lastly:

https://www.codegrepper.com/code-examples/whatever/javascript+parse+comma+separated+string

Simply click the answers and queries to view javascript snippets (examples).

= = = =

By the way: I googled "how to prevent HTML from parsing commas" as my search criteria.

Many, many, links....

Very likely that there was some resulting link that may be more applicable and directly useful than the links above.

Take a look and revise the search criteria as necessary with respect to your requirements.
 

IceQueen0607

Commendable
Nov 27, 2019
205
30
1,640
8
Couldn't find anything relevant on those searches, but it does give me an idea...

Note: I can manually enter commas in to table with INSERT or UPDATE. MySQL isn't bothered by the commas. The problem is using commas in text and textarea fields in HTML.

But what I can do, and I have to remember to do this every time, is use %2C as one of the posts suggested, and then in my processing script replace all %2C with a comma. It isn't elegant, and it will look terrible when being entered, but at least that will get me out of trouble until I find something better.

One, Two, Three, Four
will be entered as
One%2C Two%2C Three%2C Four

EDIT: ... Of course, when I load the page that maintains comments I will have to reverse parse the commas and set them to %2C. Shouldn't have too much impact on the efficiency of the script as when I convert them back to commas, the comments are only updated when they have been changed.

Oh what fun :)
 
Last edited:

Ralston18

Titan
Moderator
"Good, clean fun" as a long ago friend of mine used to say with respect to such matters.

If you can automate the replacement of commas with "%2C" do so. Let the code do the work.

When there is little or no control over incoming data all you can do is to automate "Cleanup in aisle X" as best you can.

Besides it all could be worse if character's other than comma's were also used.

Doubt that anyone watches the processing script. Much akin to the proverbial "sausage making".
 

IceQueen0607

Commendable
Nov 27, 2019
205
30
1,640
8
Found out something else too... This is only an issue with lists (Listlen, listgetat etc). If I have a page withonly one text field then I can go crazy with commas all I want.
 

Ralston18

Titan
Moderator
Again out of my comfort zone and very sure that you are well ahead of me with respect to ColdFusion, etc..

How is a page ( webpage, record?) delimited?

Curious about listgetat and so forth....

Googled and found the following links:

https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-l/listgetat.html

https://www.assocsrv.ca/CFDOCS/CFML_Language_Reference/3_ColdFusion_Functions/lr3_150.htm

Noted: second link, "The first position in a list is denoted by the number 1, not 0. "

Could be an issue if positions matter and/or are counted somewhere.

The next link is a bit more interesting:

https://macromedia.coldfusion.advanced-techniques.narkive.com/JDcltJ4U/listlen-and-listgetat

From the link:

"Due to some truly inspirational decision-making on the part of the Allaire
dev team, years ago, CF list functions cannot be made to pay attention to
empty elements. "

Unlikely that you can avoid lists at this point. Not sure.....

Fortunately there a numerous String functions available and some combination thereof may solve the problem.

At some point you may just have to concede to what works.

May be a bit klutzy or otherwise inelegant but if it works then so be it.
 
Reactions: IceQueen0607

IceQueen0607

Commendable
Nov 27, 2019
205
30
1,640
8
The idea of replacing commas with anything else doesn't work. Once the data is written to the database, and then displayed on the page and subsequently submitted again it will break the code again.

Values like check boxes and radio buttons only submit a value if they are selected. As is the case with strings. Numeric values will submit a number even if the value is zero.

If a string contains "one,two, three" then 3 elements are passed not one. (As in the listlen would be 3).

Anyway, there seems to be no easy solution. Nothing I've tried works. Perhaps another reason why Coldfusion websites are not that prolific.

I think we can drop this thread now.
 

ASK THE COMMUNITY