How does one insert numbers with commas into an SQL database? I’m currently using PHP and MySQL.
If I include them in a standard INSERT, then SQL delimits the numbers by each comma. Example: 23,000,000 is THREE numbers to be inserted and then I get an error with the query (too many columns).
If I encode the variable containing the numbers with a single quote (see below) then it only inserts the group of numbers before the first comma. If info[1]= 23,000,000 then in MySQL it will be stored as 23.
$query = "insert into mytable values('$info[0]','$info[1]','$info[2])";
Yes, I could parse out the commas, put the numbers in the DB then display/output them with commas. But that seems like too much. There has to be somthing I’m missing. The table values are INT. I don’t want to convert them to VARCHAR or CHAR because I would like the math functions provided by INT values.
Seriously, I am :bawling: . Must’ve spent three solid hours debugging before realizing the REAL problem then searching solutions on the web.
Originally posted by redking Yes, I could parse out the commas, put the numbers in the DB then display/output them with commas. But that seems like too much.
Not to me, and I think thats what youre going to have to end up doing anyway
edit: Removed two ideas the clearly would not work because they would (at best) produce ‘23’,‘000’,‘000’ instead of 23,000,000
A third untested plan!
$query = "insert into mytable values('$info[0],$info[1],$info[2]')";
Lookout world, here comes a fourth!
$query = "insert into mytable values('$info[0]\\,$info[1]\\,$info[2]')";
I would test these things but I’m on my laptop which is sans PHP.
Also, unless you have a specific reason for including commas, might it be better to store the number as an integer 23000000 then add commas with number format (as everone seems to be suggesting :))? http://www.php.net/manual/en/function.number-format.php
You can also always remove the commas when inserting. Not very difficult, though it could be a bother to do that for each number you got. str_replace() or preg_replace() would work easily, so it’s up to you if you want to go down this road or take the one suggested already by jump and samsm (which I feel would be more convenient)
Beause of the way in which redking has the number broken down, I was thinking it may not be an integer but a unique data type that he wants to store as a string. For all we know, 23,000,000 and 2300,0,000 may both need to be valid entries.
