Use MySQL IF IFNULL and COALESCE Function in PHP

MySQL IF Function

The MySQL IF function is used to test a condition and return a value if the condition is true or another value if the condition is false. In other words, the if function takes three arguments and if the first expression of the given condition is TRUE then it returns the second expression (the true expression) and if the given condition is FALSE then it returns the third expression (the false expression).

Syntax

IF (condition (means expression), if_true_expression, if_false_expression );

Parameters in IF function

It takes three parameters; they are:
 

Parameter	Description
condition	It specifies a expression.
if_true_expression,	Return, when condition is true.
if_false_expression,	Return, when condition is false.

Data of table



Use of MySQL IF Function



Example of MySQL If 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: Use of IF function</h2>";

$result = mysql_query("SELECT monger_name, IF(monger_country='france','Available','Notavailable') AS isCity_France FROM vendordtl");

 

echo "<table border='1'>

<tr>

<th>VendorName</th>

<th>IsCityFrance</th>

</tr>";

while($row = mysql_fetch_array($result))

  {

  echo "<tr>";

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

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

  echo "</tr>";

  }

  echo "</table>";

?>

Note: The preceding example lets you test an expression and return one value if the expression is true and another value if the expression is false. So the MySQL if function returns a string value of "Available" if the monger_country column value is equal to a value of "france", otherwise it returns "Notavailable".

Output



MySQL IFNULL and COALESCE  Function

The IFNULL and COALESCE functions lets you replace a non-null value for a null value. Basically these functions are similar, but the COALESCE function is more resilient because it lets you specify a list of values, then it returns the first non-null value in the list.

IFNULL Function

The MySQL IFNULL function takes two expressions and if the first expression is not null then it returns the first expression, otherwise it returns the second expression. In other words, the MySQL IFNULL function lets you replace a non-null value with a null value.

Syntax

IFNULL (test_expression, replacement_value );

Parameters in IFNULL function

It takes three parameters; they are:
 

Parameter	Description
test_expression	It specifies a test expression.
replacement_value	Specifies replacement value.

Data of table



Use of MySQL IFNULL Function



Example of MySQL Ifnull 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: Use of IF function</h2>";

$result = mysql_query("select payment_date,IFNULL(payment_date,'NewDate') as NewDate from vendordtl");

 

echo "<table border='1'>

<tr>

<th>PaymentDatee</th>

<th>NewDate</th>

</tr>";

while($row = mysql_fetch_array($result))

  {

  echo "<tr>";

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

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

  echo "</tr>";

  }

  echo "</table>";

?>

Note: In the preceding example the IFNULL function returns the first expression if it is not null, otherwise it returns the replacement value you specifie. So the MySQL IFNULL function returns the value of the payment_date column if that column does not contain a null value. Otherwise it will return a string "NewDate".

Output

COALESCE  Function

 It is the same as the IFNULL function. The COALESCE function returns the first non-null expression in the list. If all expressions evaluates to null then it will return null.

Syntax

COALESCE (expression1, expression2, expression3,..................);

Use of MySQL COALESCE Function