Page 1 of 1

Currency Format?

Posted: Thu Jul 15, 2004 3:24 pm
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?

Posted: Thu Jul 15, 2004 3:27 pm
by feyd
[php_man]number_format[/php_man]

Posted: Thu Jul 15, 2004 3:41 pm
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

Posted: Thu Jul 15, 2004 3:44 pm
by feyd
why must it be in mysql? Why not just format it when you retrieve the values?

Posted: Thu Jul 15, 2004 3:46 pm
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.

Posted: Thu Jul 15, 2004 3:50 pm
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

Posted: Mon Jul 19, 2004 7:57 am
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.

Posted: Mon Jul 19, 2004 8:16 am
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

Posted: Mon Jul 19, 2004 10:43 am
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 =)