Text fields with comma values for entry into MySQL table
Text fields with comma values for entry into MySQL table
When using a textfield, if the content is 1.2.3.4.5 it works properly. But with the same textfield containing 1,2,3,4,5 only the first one is kept while the rest gets cut off. To allow commas without truncation, adjust the settings or format the input accordingly.
The idea being discussed is called a delimiter.
The program needs to understand the character used to separate data entries.
Each row and its columns within that row/record.
The sample text field that functions well is probably from a source using CSV. It seems like
, 1.2.3.4.5 ,
and treated as a single data point. Pay attention to the commas.
CSV Comma Separated Value is one way to divide data fields. When a comma appears in the data, the software will treat that as the start of the next value. Other symbols can serve as delimiters, but there are specific rules and limits.
For instance, if the data is 1,2,3,4,5 it will be seen as five distinct values. Depending on the software, any values following the first comma might go into another field or simply be cut off.
Another method for handling incoming data is using a fixed format.
Reference (and you can easily find other similar links):
https://towardsdatascience.com/parsing-f...db8f737276
If you plan to work with a large amount of data, learning how to parse various data types is a valuable skill.
= = = =
There is a reliance on the software for importing, processing, and exporting data. It is often user-adjustable—you choose (within the allowed constraints) what delimiter to use.
Which software are you employing?
Conduct a search online for "how to delimit data" and refine your results using additional criteria and filters to match your software and needs.
It is likely you should enclose the data in quotes if it includes commas. But this must be planned during the design phase. There are multiple approaches to this.
What software are you using? What is the origin and format of your data? What aspects can you influence?
For example, if you cannot control the incoming data, you must handle its parsing to fit fields that include commas as part of your processing needs.
Luckily, tools like Excel and Access offer numerous functions to assist with these challenges.
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">
Well, this has pushed me outside my usual comfort area.
I wasn’t worried about the grammar, just the notes.
The main issue was figuring out how to handle extra commas in supporting comments.
I looked into it a bit—searching for "how to use a comma in a .cfm file" and other relevant links.
I found a couple of useful resources: one on Stack Overflow about using commas in ColdFusion forms and another from Tek Tips discussing similar problems.
I thought maybe adding some custom code could help detect and replace unwanted commas automatically.
I’m pretty confident I’m ahead of you in figuring this out with ColdFusion.
Creating code to analyze and modify strings is straightforward. Already accomplished that. The challenge lies in (a) handling commas in the string and storing them in the database, and (b) addressing how HTML interprets these strings, as commas can cause splitting of variables.
So data strings with commas are stored in the database. That part is fine. When HTML reads that data, commas cause it to split the string. The challenge is figuring out how to prevent HTML from treating those commas as separators. I found some suggestions: pseudo-commas, regex patterns, and methods using double quotes. Overall, you’d need a function to handle the database string with commas, replace them appropriately, and then send the corrected result to HTML. HTML should no longer interpret commas as breaks. There are also examples of JavaScript parsing comma-separated strings.
Couldn't locate anything relevant on those searches, though it does provide a hint...
Note: I can manually add commas to the table when inserting or updating records. MySQL ignores commas in text and fields, which isn't ideal. The workaround is to use %2C in my scripts and later convert them back. It isn't very smooth, but it keeps me from immediate issues until a better solution is found.
One, Two, Three, Four
will appear as
One, Two, Three, Four
EDIT: ... When I reload the page with comments, I'll need to reparse those commas and replace them. This shouldn't significantly slow the script, as changes are only reflected when updating comments.
Oh what fun
Good, clean fun" was a phrase my old friend often used when talking about such tasks. If you can make the code replace commas with "%2C", let it handle the rest.
When data control is limited, you're left with just automating "Cleanup in aisle X" to the best of your ability.
It's also possible things could get worse if other characters besides commas were involved.
I doubt anyone monitors the processing script. It's quite similar to the saying "sausage making".
Discovered another detail as well... This problem mainly affects lists (Listlen, listgetat, etc). If a page contains just one text field, I can easily get carried away with commas.
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-...getat.html
https://www.assocsrv.ca/CFDOCS/CFML_Lang...r3_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-t...-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.