Use MySQL Case Function in PHP

Introduction

In PHP the Case function is used to return a value that's determined by the condition you specify. Basically the CASE function is used when you want to implement a complex conditional construction then you can use a MySQL case statement.

Syntax of the simple CASE function

CASE
input_expression
WHEN expression1 THEN result expression 1
WHEN expression 2 THEN result expression 2
.............................................................
.............................................................
so on............
ELSE else result expression
END

About the CASE function

When you use this function with PHP, MySQL compresses the input expression you code in the CASE clause with the expression you code in the WHEN clause.

Example of CASE function in PHP

<?php

$con= mysql_connect("localhost","sharad","gupta");

if (!$con)

  {

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

  }

 

mysql_select_db("Employees", $con);

$qry= "SELECT id,invoice_number,CASE id

                 WHEN 1 THEN 'Net due in 5 days'

                 WHEN 2 THEN 'Net due in 10 days'

                 WHEN 3 THEN 'Net due in 30 days'

                 WHEN 5 THEN 'Net due in 50 days'

                 ELSE 'Illegal'

                 END AS 'Status'

                 from invoices";

 

$result=mysql_query($qry)or die(mysql_error());

echo"<tableborder='1'>

<tr>

<th>ID</th>

<th>InvoiceNumber</th>

<th>Status</th>

</tr>";

 

while($row = mysql_fetch_array($result))

  {

echo "<tr>";

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

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

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

  echo "</tr>";

  }

echo "</table>";

mysql_close($con);

?>


Note:
This example uses a simple case function. In this example the input expression is the id column of the invoices table and when any expression is the valid value for this column then MySQL finds an expression in the when clause, which is equal to the input expression. It then returns the expression specified in the matching THEN clause. If none of the expressions in the when clause is equal to the input expression then the function returns the value specified in the ELSE clause.


Output:
 

case-function-in-php.jpg

Up Next
    Ebook Download
    View all
    Learn
    View all