How to use CASE clause in a MySql SELECT in PHP

The CASE clause is a very useful one widely used when retrieving data from a SQL based database. Here are some examples on how to implement this when writing PHP code.

Examples using mysqli driver extension.

Example 1: Simple CASE statement.

//...

$query = "SELECT column_name,
                CASE
                    WHEN column_name = 'value1' THEN 'Result 1'
                    WHEN column_name = 'value2' THEN 'Result 2'
                    ELSE 'Default Result'
                END AS result
          FROM table_name";

$result = mysqli_query($connection, $query);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['column_name'] . ": " . $row['result'] . "<br>";
}

mysqli_close($connection);

//...

This example retrieves data from the "table_name" table and performs a simple CASE statement on the "column_name" column. It checks the values in the column and assigns corresponding results based on the conditions specified in the CASE clause.

Example 2: Searched CASE statement

//...

$query = "SELECT column_name,
                CASE
                    WHEN column_name = 'value1' THEN 'Result 1'
                    WHEN column_name = 'value2' THEN 'Result 2'
                    ELSE 'Default Result'
                END AS result
          FROM table_name
          WHERE condition = 'some_value'";

$result = mysqli_query($connection, $query);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['column_name'] . ": " . $row['result'] . "<br>";
}

mysqli_close($connection);

//...

This example is similar to the previous one, but it includes a WHERE clause to filter the results based on a specific condition.

Example 3: Using the CASE statement in the WHERE clause

//...

$value = 'some_value';

$query = "SELECT column_name
          FROM table_name
          WHERE CASE
                    WHEN column_name = 'value1' THEN condition1 = '$value'
                    WHEN column_name = 'value2' THEN condition2 = '$value'
                    ELSE condition3 = '$value'
                END";

$result = mysqli_query($connection, $query);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['column_name'] . "<br>";
}

mysqli_close($connection);

//...

In this example, the CASE statement is used within the WHERE clause to dynamically construct the conditions based on the value of the "column_name" column. The query will check different conditions based on the column's value and retrieve the corresponding rows.

Example 4: Using the CASE statement in the WHERE clause with joined tables

//...

$value = 'some_value';

$query = "SELECT t1.column_name
          FROM table1 t1
          JOIN table2 t2 ON t1.id = t2.id
          WHERE CASE
                    WHEN t1.column_name = 'value1' THEN t2.condition1 = '$value'
                    WHEN t1.column_name = 'value2' THEN t2.condition2 = '$value'
                    ELSE t2.condition3 = '$value'
                END";

$result = mysqli_query($connection, $query);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['column_name'] . "<br>";
}

mysqli_close($connection);

//...

In this example, we have two joined tables, "table1" and "table2", joined using the "id" column. The CASE statement is used within the WHERE clause to dynamically construct the conditions based on the value of the "column_name" column in "table1" and the corresponding conditions in "table2". The query will check different conditions based on the column's value and retrieve the corresponding rows from the joined tables.

Examples using PDO driver extension.

Example 5: Simple CASE statement using PDO

//...

$query = "SELECT column_name,
                CASE
                    WHEN column_name = 'value1' THEN 'Result 1'
                    WHEN column_name = 'value2' THEN 'Result 2'
                    ELSE 'Default Result'
                END AS result
          FROM table_name";

$result = $pdo->query($query);

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    echo $row['column_name'] . ": " . $row['result'] . "<br>";
}

//...

Example 6: Searched CASE statement using PDO

//...

$query = "SELECT column_name,
                CASE
                    WHEN column_name = 'value1' THEN 'Result 1'
                    WHEN column_name = 'value2' THEN 'Result 2'
                    ELSE 'Default Result'
                END AS result
          FROM table_name
          WHERE condition = :value";

$statement = $pdo->prepare($query);
$statement->bindParam(':value', $value);
$statement->execute();

while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    echo $row['column_name'] . ": " . $row['result'] . "<br>";
}

//...

Example 7: Using CASE statement in the WHERE clause with joined tables using PDO

//...

$value = 'some_value';

$query = "SELECT t1.column_name
          FROM table1 t1
          JOIN table2 t2 ON t1.id = t2.id
          WHERE CASE
                    WHEN t1.column_name = 'value1' THEN t2.condition1 = :value
                    WHEN t1.column_name = 'value2' THEN t2.condition2 = :value
                    ELSE t2.condition3 = :value
                END";

$statement = $pdo->prepare($query);
$statement->bindParam(':value', $value);
$statement->execute();

while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    echo $row['column_name'] . "<br>";
}

//...

In these examples, we use the PDO extension to establish a database connection and execute the queries. We use prepared statements with named placeholders (e.g., ":value") and bind the actual values using the bindParam method to prevent SQL injection.