Currency Format?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Currency Format?

Post by liljester »

I need to format a field as currency when i select it... or heck, just a comma seperated number would be great...

the mysql documentation says that FORMAT(X, D) will do this... but it doesnt, it will knock the trailing decimals off if i want it to, but it doesnt stick in the comma as a thousands seperator...

anyone have any ideas?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

[php_man]number_format[/php_man]
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post by liljester »

feyd, must be mysql, im touching a ton of rows, and i dont want to pull out each row to let php format them and then update the db again
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

why must it be in mysql? Why not just format it when you retrieve the values?
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post by liljester »

its possible im inserting alot of rows, all of which are getting the data from other tables in the db, so php never sees the rows.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post by liljester »

here is the query as mysql sees it, its a query i generate with php, but php never sees any data from the tables i use for the new rows.

Code: Select all

INSERT INTO news( colony_id, sector_id, news_type, news, news_tick, news_date ) 
SELECT u.colony_id, u.sector_id, "Espionage", 
CASE WHEN (
q.status =3
)
THEN CONCAT( "Your Advisors indicate that your sector ", s.quadrant, ":", s.zone, ":", s.sector, " has lost $", ROUND( q.amount * c.bank ) , " to a spy operation!" ) 
WHEN (
q.status =2
)
THEN CONCAT( "In sector ", s.quadrant, ":", s.zone, ":", s.sector, ", a spy was detected trying to Transfer Funds from your bank, your Inelligence agency was unable to capture the assailant." ) 
WHEN (
q.status =1
)
THEN CONCAT( "In sector ", s.quadrant, ":", s.zone, ":", s.sector, ", ", u.name_first, " ", u.name_last, " was detected trying to Transfer Funds from your bank! Your Inelligence agency was unable learn who sent the spy." ) 
ELSE CONCAT( "In sector ", s.quadrant, ":", s.zone, ":", s.sector, ", ", u.name_first, " ", u.name_last, " was detected trying to Transfer Funds from your bank! Your Inelligence agency was learned that the spy was sent from ", spy.colony, "." ) 
END , "339", NOW( ) 
FROM queue_espionage q
INNER JOIN units u ON q.unit_id = u.unit_id
INNER JOIN colony c ON u.colony_id = c.colony_id
INNER JOIN sectors s ON u.sector_id = s.sector_id
INNER JOIN colony spy ON u.spy_home_colony = spy.colony_id
WHERE q.ticks_remain =0
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post by liljester »

As it turns out, the FORMAT() works just as it should =) I must have been looking at rows inserted before i put the function into the query.
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

I'm not sure if this works for you since thousands don't get separated by commas here, but take a look:

Code: Select all

mysql> create table test (salary decimal(5,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(15.50);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(150);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(17.99);
Query OK, 1 row affected (0.00 sec)

mysql> select concat('U$ ', salary) from test;
+-----------------------+
| concat('U$ ', salary) |
+-----------------------+
| U$ 15.50              |
| U$ 150.00             |
| U$ 17.99              |
+-----------------------+
3 rows in set (0.00 sec)

mysql>
-- Scorphus
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post by liljester »

the field in the DB is INT, Im not interested in the cents. FORMAT() worked as it should have, i think i was looking at old rows before i put in the format function... =) oops =)
Post Reply