Solved

Display correct prices from database

Posted on 2013-01-02
12
326 Views
Last Modified: 2013-01-07
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?
0
Comment
Question by:BrighteyesDesign
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 21

Expert Comment

by:Kim Walker
ID: 38737070
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

0
 

Author Comment

by:BrighteyesDesign
ID: 38737228
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?

result
0
 
LVL 21

Expert Comment

by:Kim Walker
ID: 38737266
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.
0
 

Author Comment

by:BrighteyesDesign
ID: 38737337
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?
0
 
LVL 11

Expert Comment

by:mcnute
ID: 38739419
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.
0
 

Author Comment

by:BrighteyesDesign
ID: 38739432
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

db
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 11

Accepted Solution

by:
mcnute earned 500 total points
ID: 38739455
Another question: Do you have some sort of validation when inserting these prices in your database? Do you give some sort of restriction to that field entry or is these field free to be filled with whatever format?

If so, I stronly suggest you to dictate a format to have consistant data in your database. That said, it can even avoid you the trouble of formatting at all. But since you have tow potential types of prices 4.90 and 18,000.00 and it is of type string you could do the following:

$intpounds = explode('.' $row['price']);

if (strlen($intpounds[0]) > 3 ) {   // if its more than hundereds display everything before the dot.
    echo $intpounds[0];
} else { // else display the full price just out of the database 
   echo $row['price'];
}

Open in new window


Hope that helps!
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38739663
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:

SELECT
  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
0
 

Author Comment

by:BrighteyesDesign
ID: 38739713
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...

results
0
 
LVL 11

Expert Comment

by:mcnute
ID: 38739732
Sorry i missed out a comma. So do it like so.

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

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38742446
...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.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38750503
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.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now