SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL Math Functions

Formatting math data returned by MySQL table

We can format numerical data returned from MySQL table by using FORMAT function. Formatting can be done by specifying number of decimal places required from data retuned by using a select query from a table. We can also use the format function to format data while updating or inserting to MySQL table. Let us see one example of uses of Format function while displaying the data using select query. Before that here is the syntax

FORMAT(X,D)

Where X is the data and D is the number of places after the decimal required.

For example in a table we are storing number of rating given by visitors. Here we are strong the total rating and the number of visitors submitted the rating. These two fields are required to get the average rating the article got. We will simply divide total score by total number of ratings given to get the average rating. Here the result of this ratio we will format for our requirement.

SELECT FORMAT((score/nov),2) AS avg_value FROM `table_name` WHERE id=2

Here score is the field name where we are storing total score given by the visitors. Same way nov field stores the number of votes submitted by visitors.

We can format our data while updating or inserting to a table. In the same table above we will try to update one more field avg_score by using values of score and nov fields.

UPDATE table_name SET avg_score=FORMAT(score/nov,3) where id=2

Here we have formatted the decimal place to three places. Inside the query avg_score, score and nov are field names and they are integer and float type fields.

You can read MySQL date format here.

Be the first to post comment on this article :


Google+

Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked



Join Our Email List
Email:  
For Email Newsletters you can trust

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer