SQL rounding and truncation, needs a thorough explanation

Question

Hello. I'm a novice when it comes to SQL and PHP, and I'm trying to round an average price result and take off the extra zeroes that currently appear.

Currently my result turns up as: $3.005000

My code currently reads as follows:

$result = mysql_query("SELECT AVG(price) FROM milk WHERE price > 0 ");
$row = mysql_fetch_row ($result);

I have found several examples of SQL rounding and truncation but unfortunately the tutorials I've seen provide me with no useful information on where or how I am supposed to implement these changes.

This leaves me making guesses on where to make changes -- none of which have worked out so far (obviously).

If someone could provide me with an example of how to round and truncate my results, which includes where exactly I need to make these changes in my current configuration, that would be most helpful and I would be very thankful! I'm really sorry if my n00bishness makes it more difficult to explain the solution.

Thanks!

Answer 1

Formatting of the data should be done in the script making the query, not in the query itself. For example, in PHP you can write the following using sprintf:

$formatted_price = sprintf("%01.2f", $unformatted_price);

(Example complements of the PHP manual).

Also, generally, price values are stored as decimal types or scaled integers, not floating-point types, since floating-point values are not exact.

Answer 2

MySQL has a ROUND() function.

So just round your average in your SQL query:

$result = mysql_query("SELECT ROUND(AVG(price),2) FROM milk WHERE price > 0 ");

If you end up with formatting issues, you can use PHP's number_format() function during output.