MySQL Cast and Convert Function in PHP

CAST Function

The MySQL cast function is used to mutate a value of one type (data type) to another type. In other words, the CAST() function takes a value of one type and produces a value of another type.

Syntax

cast (exp As Type)

The "Type" can have the following values:

• Binary
• Char
• Date
• DateTime
• Decimal
• Signed [integer]
• Time
• Unsigned [integer]

Create a table in MySQL

The following will create a table in MySQL:



Select table data from MySQL

The following uses MySQL to select table data:



Use cast function in MySQL

The following shows how to use a cast function in MySQL:



Example of CAST Function with PHP

The following is a sample use of the CAST Function with PHP; in the example, the CAST function converts a "DateTime" data type to a "DATE" data type.

<?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 mcnemployee");

echo "<table border='1'>

<tr>

<th>Firstname</th>

<th>Lastname</th>

<th>EmpId</th>

<th>EmpJoinDate</th>

</tr>";

while($row = mysql_fetch_array($result))

  {

  echo "<tr>";

  echo "<td>" . $row['emp_id'] . "</td>";

  echo "<td>" . $row['Fname'] . "</td>";

  echo "<td>" . $row['Lname'] . "</td>";

  echo "<td>" . $row['emp_JoinDate'] . "</td>";

  echo "</tr>";

  }

  echo "</table>";

 

  print "<h2>MySQL: With Cast Function</h2>";

$result = mysql_query("select CAST(emp_JoinDate as Date) As JoinDate from mcnemployee;");

echo "<table border='1'>

<tr>

<th>EmpJoinDate</th>

</tr>";+-

while($row = mysql_fetch_array($result))

  {

  echo "<td>" . $row['JoinDate'] . "</td>";

  echo "</tr>";

  }

  echo "</table>";

?>

Output

CONVERT Function

The MySQL convert function is used to mutate a value of one type (data type) to another type. In other words, the CAST() function takes a value of one type and produces a value of another type. Basically the cost and convert functions do the same thing but there is a little difference between the cast and convert functions.

Difference between cast and convert function

CONVERT  allows a greater breadth of flexibility when converting between date and time values and fractional numbers.

CAST is also required when converting between decimal and numeric values to preserve the number of decimal places in the original expression.

Syntax

convert (exp,Type)

The "Type" can have the following values:

• Binary
• Char
• Date
• DateTime
• Decimal
• Signed [integer]
• Time
• Unsigned [integer]

Use Convert function in MySQL

Example of CONVERT Function with PHP

<?php

$con=mysql_connect("localhost","root","");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

mysql_select_db("mysql", $con);

  print "<h2>MySQL: Convert Function</h2>";

$result = mysql_query("select CAST(emp_JoinDate as Date) As JoinDate from mcnemployee;");

echo "<table border='1'>

<tr>

<th>EmpJoinDate</th>

</tr>";

while($row = mysql_fetch_array($result))

  {

  echo "<td>" . $row['JoinDate'] . "</td>";

  echo "</tr>";

  }

  echo "</table>";

?>

Output