MySQL Format Function in PHP

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

Format (number, decimal)

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.

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