money type...

[zenlord]

Hi, I'm trying to use the money type with PHP and PostgreSQL.

 

The following code works, but I cannot set decimals: a comma is treated as a list seperator, throwing an error there are more variables than there are columns and a . is neglected.

 

		$billdate = $_POST['date'];
	$billuser = $_POST['user'];
	$billfile = $_POST['dossier'];
	$billamount = (string) $_POST['bedrag'];
	$billamount = (string) $billamount."::text::money";
	$billcat = $_POST['categorie'];

	// Insert nieuwe nota in dbase
	$fields = array('date' => $billdate, 'user_id' => $billuser, 'dos_nr' => $billfile, 'amount' => $billamount, 'category' => $billcat);

 

ANyone? THX!

[Mchl]

1. Why do you want to store money as a string?

[btherl]

Try putting single quotes around the money value (I'm taking a wild guess here)

[F1Fan]

Where is your insert/update statement? Are you properly escaping your text before inserting? It doesn't appear so if commas are giving you errors.

[zenlord]

Yes, I'm sorry: I forgot to add the pg_insert(), so here you go:

pg_insert(CNX_DOS,"dossiers_bills", $fields) or die("billNieuw: Nieuwe nota aanmaken mislukt");
pg_close(CNX_DOS);

 

I tried adding single quotes, double quotes and casting as another type (string, object), but nothing worked. It has something to do with escaping, but I thought pg_insert did that automatically?

 

Anyways - this is the error postgresql gives me when I try to insert a money value of 445,25 into my dbase:

STATEMENT:  INSERT INTO dossiers_bills (date,user_id,dos_nr,amount,category) VALUES ('2010-04-24','1','1031632',445,25::text::money,'seek');

Everything else is quoted automatically by pg_insert() - only the $billamount not.

 

Out of the answers I'm getting, I get the feeling I've made a very basic error and I'm still overlooking it. AAARGH! THX already for your replies!

 

Vincent

[Mchl]

try:

$billamount = (string) "'$billamount::text::money'";

[zenlord]

That's interesting, because I tried it, it still doesn't work (I get the die()-statement), but there are no more errors in the main.log...

 

I also tried

$billamount = (string) "'$billamount'::text::money";

 

Because I have succeeded by entering the SQL-statement inside phppgadmin directly. Still no luck with this function, though...

THX for your help!

[Mchl]

Try displaing actual error message:

pg_insert(CNX_DOS,"dossiers_bills", $fields) or die(pg_last_error());
pg_close(CNX_DOS);

 

[zenlord]

THX for pointing me in the direction of the error-functions. I tried several: last_error, last_notice, result_error.

 

Neither returned an error or any message whatsoever. I get the feeling that the single quotes solved the original problem, because the original problem could be seen in the logs.

 

I've checked my postgresql config and the error level is at its default value: 'notice'. I'll try to lower that and see what it returns.

 

Vincent

[zenlord]

Well, I tried all sorts of error levels, but I have not succeeded in generating an error regarding this pg_insert. I would like to upgrade my server, but the upgrade involves a lot of packages and I don't want to risk to break this server. The server uses PostgreSQL 8.3.8 and php 5.2.6 - fairly recent, but so is the 'money'-datatype in postgreSQL and the pg_insert() is experimental as per the php online documentation...

 

So I changed the data type from 'money' to 'numeric(9,2)' as pointed out in another recent thread in this subforum, and now I can insert my rows like I'd want to.

 

I have only 1 small problem: numeric has '.' as decimal separator. In our locale we use ','. changing this with regular expressions is not that hard, but I wonder if I can change a setting to make postgreSQL use ',' as a decimal separator. Anyone? THX!

[zenlord]

I switched the data type from 'money' to 'numeric(9,2)' and got something that works 90% of the time:

 

For input I now use

$billamount = trim(str_replace(array("$","€"),"",$_POST['bedrag']));
$billamount = number_format($billamount,2,".","");

 

For output, I use

SELECT dos_nr, category, date, TO_CHAR(amount,\'L FM9G999G990D00\') AS amount2, status

 

This works, unless my users input a number with '.' as a decimal separator. I'm looking into a regular expression to remove those, but have not found it yet. Once I get that one, my current problem is ***SOLVED*** - although it has nothing to do with the data type 'money' anymore...

 

Vincent

PS: stumbled across number_format() and also found out that there is a currency_format(), which might have been of use when using the 'money'-data type...

[zenlord]

Only replying to give other people that encounter the same after this date some more info:

 

1. In my previous post I described a problem that I still have: in my locale, currencies are written as '€ 1.000,00', as as opposed to USD '$ 1,000.00'. If I trust my users never to input an amount with '.' as a grouping symbol, then the above function works. As I have no intentions of trusting *any* user, I have written the following regular expression to single out cases that will pose problems:

if (preg_match('/\.[0-9][0-9][0-9]/',$billamount)){echo "troubles";}

This works, but replacing the '.' with the following has not worked for me (but I will not give up on this one soon):

preg_replace('/\.([0-9][0-9][0-9])/', '$1', $billamount)

 

2. In my previous post I talked about 'currency_format()' where this should of course be 'money_format()'. I briefly experimented with this function in conjunction with the 'money'-data type, but I encountered the same problem, i.e. a function that errors out without logging why. So I gave up on that one and returned to my solution sub 1.

[Mchl]

1. Try asking in our PHP Regex section

2. Both these functions require number formatted as 1000.00 (no thousands separator and dot as decimal separator), and they're used for displaying formatted numbers, not for storing into database.

[zenlord]

1. I kinda figured it out - see below

		// $billamount needs to be prepared for various situations:
	// 1. if currency symbols are inputted
	$billamount = trim(str_replace(array("$","€"),"",$_POST['bedrag']));
	// 2. if grouping characters (',' or '.') are used in the number to group thousands
	$billamount = preg_replace('/(\.|\,)+([0-9][0-9][0-9])/', '$2', $billamount);
	// 3. if the decimal separator is a ','
	$billamount = str_replace(",",".",$billamount);
	// 4. if no decimals are inputted
//(1)		$billamount = number_format($billamount,2,".","");

 

2. I'm by far not a PHP guru - I just need it to work. The above method is not ideal, it would have been far more easy if the 'money'-data type worked for me. The function 'number_format()' works in the above configuration and without exceptions , even when the inputted number is less than zero and approximating 0 (which has proven to be a problem with the 'number_format()'-function according to the comments on the php.net-website).

 

I want to thank you for reading my posts and taking the time to reply to it, but I think I have found a solution to my initial problem...

 

EDIT: (1) After thinking a second time about the function, I realised that I don't need the 'number_format()'-function anymore, that's why I commented it out in the above php-block

[Axeia]

Just look on the bright side, the documentation for the 'money' type reads:

 

"Note:  The money type is deprecated. Use numeric or decimal instead, in combination with the to_char  function. "

So you are now future proof

 

For presenting the output back to the user PHP comes with a money_format function of its own.