Currency Format?
Moderator: General Moderators
Currency Format?
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?
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?
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- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
I'm not sure if this works for you since thousands don't get separated by commas here, but take a look:
-- Scorphus
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>