|
| |
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 filed name where we are storing total score given by the visitors. Same way nov filed 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.
| |
|
|
|
