Jump to content

money type...


Recommended Posts

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!

Link to comment
Share on other sites

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");


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!



Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.



Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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...



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...

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.