putting commas into numbers

[gymshoe]

Ok,
This should be simple...I'm sure it's a cake in Perl, but in PHP / MySQL...I'm not sure hoow to do this..
I've looked for the last 10 minutes and can't find anything.

I have a database that's storing prices.
Right now they're stored as varchar.
When I display them, I'd like to be able to sort them up or down.
Strangely (or not so strangely) when they're sorting down (lowest price to highest) ... I have some values that are smaller at the bottom... I'm sure this is because i have the type as varchar rather than integer or decimal.

So... what I'd like to do is allow input of like 50000 and have it display as $50,000

The dollar sign I can just insert before displaying, but how do I do the commas?


James

[sheila]

I have some Python code that does this...maybe it will give you a clue.

Basically, I use the mod division plus division by 1000 to shorten the integer and then convert it with formatting to a string...like this:

# get rid of pennies
num = abs(num)*100

# track dollars and cents separately
dollars, cents = divmod(num, 100)
# this money is actually stored as a floating
# point value. Ugg.
cents = round(cents)

# format the "cents" part of the string
money = ".%02d" % cents

# now loop on the dollar conversion part
while dollars:
# modular division...get remainder and dividend
over, under = divmod(dollars, 1000)

# if there will be more numbers coming,
# insert a comma into the format string
if over: newstr = ",%03d" % under
# otherwise no comma in format string
else: newstr = "%d" % under
money = "%s%s" % (newstr, money)

# prepare for next loop iteration
dollars = over

[Bruce]

The dollar sign I can just insert before displaying, but how do I do the commas?The Perl expression to do this that I found is1 while $number =~ s/^(-?\d+)(\d{3})/$1,$2/o;PHP would be somewhat more complex:while (preg_match('/^(-?\d+)(\d{3})/', $number, $parts) {
$number ="{$parts[0]},{$parts[1]}";
}I haven't tested the above PHP, but it should be close enough.

[hobbes]

number_format (http://www.php.net/manual/en/function.number-format.php) is your friend in PHP. In MySQL there's always FORMAT (http://dev.mysql.com/doc/mysql/en/miscellaneous-functions.html); though as a VARCHAR, using number_format in PHP probably makes more sense.

[gymshoe]

thanks everybody!

I just found number_format right before I read your post hobbes! but thank you!

[dank]

Bruce and Sheila,

Wow, that's a serious programmer's answer! I'm with hobbes on this one.

Dan

[hobbes]

Thanks Dan, but my initial reply consisted of outsourcing the comma placement to Elbonia, but the function they implemented took 1 week to complete for a single number. :wink:

[sheila]

I think I will consider it a complement to have something I wrote considered "serious programming". :P

I wasn't aware of the PHP number_format function or the MySQL FORMAT function, so I learned something too! :)

[dank]

To me, it's a good example of how user friendly PHP is compared to alternative languages. It's why I say, without exaggeration, that I learned as much PHP in a day as I learned Perl in a year.

Dan

[PaulKroll]

I'd vote for using the MySQL FORMAT command if it applied to this case, just on the theory that it's taking time from the SQL server instead of the web server (they're separate here at FutureQuest), and that being MySQL... it was probably written better than the number_format command in PHP, and runs faster. But that may only be bitterness and an obsession with optimizing code. :)

[Slim]

To me, it's a good example of how user friendly PHP is compared to alternative languages. It's why I say, without exaggeration, that I learned as much PHP in a day as I learned Perl in a year.

To me, it's a good example of the difference in perceptions about Perl and PHP. After all, in Perl, you could use Number::Format

[hobbes]

I'd have to agree with Slim ... and Dan. PHP does have the advantage of having so much already bundled in, but I would hazzard that you may actually find more stuff out there for Perl and searching CPAN is pretty straightforward. For a novice, the bundled aspect has its advantages; overall though both languages should allow just about any programmer accomplish what they want, even if they provide multiple ways of shooting oneself on the foot.

[PaulKroll]

Religious Language War in 5... 4... 3... 2...

- Just remember, they're ALL BAD. Except ARexx. OH! And AMOS Basic! And er... what the heck was the name of the C-64/C-128 macro assembler... the good one... oh well.

[hobbes]

I'll take 6502 Assembly anyday. Call -151

[dank]

I never said PHP is better, just easier to learn. If you think having to search for external packages to get the same functionality is intuitive to someone not already well versed in the language, then we have very different ways of looking at the world.

Dan