MYSQL decimal point

Posted on 2008-06-20
Last Modified: 2013-12-12
Hello experts.

I've come up with a simple 5-star rating system. Everything works fine but the calcualted answer is too long. ie. 2.4444 but I'd like it to be 2.44.

Do I be make this adjustment in MYSQL or PHP and how would i do it?

MYSQL statement:

SELECT ((1star+2star*2+3star*3+4star*4+5star*5)/(1star+2star+3star+4star+5star))
FROM rating
WHERE prod_id = colname

PHP to print answer is attached:

<?php echo $row_rsRating['((1star+2star*2+3star*3+4star*4+5star*5)/(1star+2star+3star+4star+5star))']; ?>

Open in new window

Question by:allanch08
  • 2
  • 2
LVL 48

Accepted Solution

hernst42 earned 265 total points
ID: 21835225
You can do it in PHP with:

echo number_format($row_rsRating['((1star+2star*2+3star*3+4star*4+5star*5)/(1star+2star+3star+4star+5star))'],2);

Author Comment

ID: 21835254
thanks for quick reply, works a treat. as a matter of interest how would I do it in mysql?
LVL 19

Assisted Solution

LordOfPorts earned 235 total points
ID: 21835257
You can give an alias to the SQL value returned in order to simplify it:

SELECT ((1star+2star*2+3star*3+4star*4+5star*5)/(1star+2star+3star+4star+5star)) AS FinalRating
FROM rating
WHERE prod_id = colname

Then in PHP output it in the following manner:

<?php echo sprintf("%1\$.2f", $row_rsRating['FinalRating']); ?>

LVL 48

Assisted Solution

hernst42 earned 265 total points
ID: 21835265

Author Comment

ID: 21835279
thanks for help everyone

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

756 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