Problem displaying a price

chrislead

Hey there, I have a MSAccess database, which I use to display items on my site. I am also using a mysql database to store the items when they have been selected. When checking out, the items are queried from the mysql database. Hope thats clear.

I can successfully get information out of the database and displayed when checking out. But the price is the only thing that will not show up. Infact, it wont even go into the mysql db! I have eliminated limiting factors such as field type in the mysql database, i.e. tried int, varchar and decimal but it still does not show!

If anyone has got any ideas I would be most greatful.

...The default value that is displayed when getting the items from the Access db is .***; (e.g. 3.4900); and then I simply use a number format. I have tryed limiting the no. of decimal places in the mysql db too, but still nothing.

Thanks.

tsinka

Hi,

need more info 🙂

Please post the relevant code and the current table layout.

Thomas

Roger_Ramjet

ACCESS uses base+exponential as it's internal format for storing numbers by default, DOUBLE is the data type. This is how it manages to produce a fraction when you add 2 'integers'. This behaviour is not readily apparent in ACCESS because of the field display settings that are inherited all over the place. EXCEL shows it much more clearly when you make the column width too narrow for the decimal display (try typing a long string of digits into an Excel cell if you don't know what I mean). That is the MickeySoft Wonderland.

You should be using the CURRENCY data type in Access to store your prices, and probably are. This is a decimal data type with 4 decimal places. To import them into MySQL you will need to specify DECIMAL(8,4) on the column to hold them.

A quick fix is to coerce your prices into strings and truncate if neccessary. PHP dynamic typing will handle the decimal conversion for you when you want to insert them into MySQL.

chrislead

I unfortunately dont have access to my code at the moment, but will post it when I do. In the mean-time...

...I am using a simple loop to get certain fields from my Access db through odbc. I then display this info on my browsing page and format my price field appropriately. (without formating the number appears as .***). Using hidden fields I then post these values into a mysql db. All fields work appart from the price field. Hope thats a bit clearer for now.

I have tryed using the field type (8,4) and it still does nothing.

If you could give me a little more information regarding how to coerce prices into strings I would be greatful because I can see that that might be what I need.

Thanks.

tsinka

Hi,

add a line to your script that prints the SQL query you use to store the data in the MySQL database.

What does it print ?

Thomas

chrislead

Ok, the result is shown in the format:

.***

i.e. 4.9900

Hope it helps.

tsinka

Hi,

how does the complete INSERT statement look like you use with mysql_query ? Just add a line to the php script that prints the complete INSERT statement and post that.

Thomas

Roger_Ramjet

Originally posted by chrislead
I have tryed using the field type (8,4) and it still does nothing.

If you could give me a little more information regarding how to coerce prices into strings I would be greatful because I can see that that might be what I need.

Thanks.

Right, the best way is to get the data correct from source. So in ACCESS:

Price column must be data type = Currency
Properties (bottom left in table design):
Format = Standard (this will suppress the currency symbol)
Decimal places = 2
The query that extracts the data from Access should look something like this:

SELECT id, name, description, Cstr(price) AS string_price FROM items

Cstr() is an Access function that returns a variable as a string, in this case your price.

Now when you echo it on your page it should already be a string with 2 decimal places. If you have any trouble saving it to MySQL try this to force the data type:

$mysql_price = 0.00;
$mysql_price = $mysql_price + $row['string_price'];

Hope this helps.

chrislead

Wow!

Finally, it works. I used the string method and then realised one of my variables was wrong. Tut.

Anyway, it works great now, 3 weeks worth of trying, phew!

Thanks to all that posted, We got their eventually!!!!!!