Introduction
I have already described the CAST and CONVERT functions in my previous article. You can see that at:
CAST and CONVERT function in PHP
to be understand better the data conversion functions. Now MySQL provides another type of data conversion function named FORMAT.
Format Function
The MySQL FORMAT function converts the specified number to a character string and groups the digits separated by commas, rounded to the specified number of decimal digits. In other words, this function returns the number "N" in a format like[ ***,****.**] and the format function also rounds the number to the specified number of decimal places, which is provided in the format function as the second parameter. And at last the result is a string. If there is not a decimal point then the decimal place is defined as 0 (zero).
Syntax
Parameters in Format function
It takes two parameter; they are:
| Parameter |
Description |
| number |
Specifies a number, that you want to be formatted up to decimal palaces rounded up. |
| decimal |
It specifies a number, that indicates , how many places number will be round up. |
MySQL Example
The following sample of the Format function formats 13452.342 to 2 decimal places rounded up.
![mysql-format-function.jpg]()
Advantage
It makes large numbers easier to read.
You can set a format of a specific number, according to his/her need.
Example of MySQL Format function in PHP
<?php
$con=mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mysql", $con);
print "<h2>MySQL: Simple select statement</h2>";
$result = mysql_query("select * from emp_dtl where id=101");
echo "<table border='1'>
<tr>
<th>Empid</th>
<th>FirstName</th>
<th>LastName</th>
<th>Role</th>
<th>Salary</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['Firstname'] . "</td>";
echo "<td>" . $row['Lastname'] . "</td>";
echo "<td>" . $row['role'] . "</td>";
echo "<td>" . $row['salary'] . "</td>";
echo "</tr>";
}
echo "</table>";
print "<h2>MySQL: Format function in PHP</h2>";
$result = mysql_query("select salary,FORMAT(salary,2) from emp_dtl where id=101");
echo "<table border='1'>
<tr>
<th>Salary</th>
<th>Format</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['salary'] . "</td>";
echo "<td>" . $row['FORMAT(salary,2)'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($con);
?>
Output
![mysql-format-function-in-php.jpg]()