Avatar of BrighteyesDesign
Flag for Afghanistan asked on

Display correct prices from database

I am displaying price values from my database and using the following code to display them.

FORMAT(properties.price,0) as price

So a database value of 11000 shows as 11,000. The problem is that now hourly rates need to show so if the database for instance has a value of 5.8 it needs to display as that whereas currently is shows as 6.

Any ideas how I change the format to achieve this?

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
Kim Walker

Change the second argument in your Format statement to the number of decimals you'd like. If you'd prefer that 5.8 be displayed as 5.80, change the statement to
FORMAT(properties.price,2) as price

Open in new window


Thanks for that, almost there...

Just one thing, if you look at the screenshot...ideally, it should read £4.90 - £18,000 rather than £4.90 - £18,000.00 Is that possible?

Kim Walker

Obviously, if you know that the higher number is ALWAYS in full pounds, you'd use 0 as the second argument for the format statement for that number. But if that's not the case, it would require some sort of if/then clause to recognize when to include cents and when not to. Unfortunately this is beyond the scope of my knowledge of SQL. Perhaps another expert will comment further.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

No worries, thanks for getting this far.

The example in the screenshot is just an example and kind of false as it mixes an hourly rate and salary. It would always be one or the other.

So  ....£4.90 - £5.60    or     £30,000 - £40,000

It's pretty safe to say that there won't be any hourly rates above £100 pound though so I reckon an if statement saying something like "if over 100 don't add .00"? No idea how to this of course! but as you say, if there's any other experts out there that do?

It would be really helpful if you show us how the price is being stored in the database. For now we only know about how you want it to be. So tell us how it IS right now to give a formatting solution.

Sure, it was 'INT' but all numbers were not whole, I couldn't get to grips with using decimals so it's currently just stored as tinytext

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ray Paseur

I believe you can select the same column more than once, using the MySQL functions you choose to format the information.  Sorry I do not have time to work on this right now, but the general idea would be something like this:

  FORMAT(properties.price,0) as price0
, FORMAT(properties.price,1) as price1
FROM properties...

Also, it would be wise to carry decimal values as data type DECIMAL.  Just a thought... ~Ray

It does indeed mcnute, just one thing, Dreamweaver is flagging up an error with the first bit of code? When i delete '.' the error marker no longer shows so it's possibly something to do with that? error
Cheers Ray, I see what you're saying there but (there's always a but eh? ) there's not always a range so the first price is not always in one format and the second in another. most of the time there's not even a range...like here...


Sorry i missed out a comma. So do it like so.

explode('.', $row_jobs['price']);

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Ray Paseur

...not always a range so the first price is not always in one format and the second in another...
Please post the CREATE TABLE statement along with a brief explanation of the values to be expected in the columns, thanks.  It may be appropriate to change the table definition, but we will not really know until we can see the existing definition and the explanation of what the columns should contain.
Ray Paseur

No CREATE TABLE statement, eh?  Ever wonder why you're not getting all the value you could from the dialog here at EE?

You might want to make a Google search for this exact phrase: Should I Normalize My Database.  I know right where you're going (I've seen many novice programmers fall into this black pit) and you're headed into deep trouble.