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