phpmysqlcodeigniterselectactiverecord

Redact the column in a SELECT clause depending on a condition using CodeIgniter's active record

My query:

$CI->db->select('UserID, Email, Alias, Status, Age');
$CI->db->from('Users');
$CI->db->where('Status', 'Available');
$query = $CI->db->get();
$result = $query->result();
//next comes a function that sends $result to the client app

Now, it works, but for the records with Age < 18, I would like to get the Alias field empty, so that this value is not transmitted to the user.

How can I achieve that with a MySQL query?

It could be done of course with a PHP foreach loop, but I would prefer to avoid this due to operational time cost (MySQL query would be probably way faster).

Solution

  • You can fix this issue using Mysql Condition in SQL:

    Query will be Like this:

    SELECT UserID, Email, IF(Age<18, Alias, "") as Alias, Status, Age FROM Users Where Status='Available';

    You can implement the Query in CI as Bellow:

    $CI->db->select('UserID, Email, IF(Age<18, Alias, "") as Alias, Status, Age');
$CI->db->from('Users');
$CI->db->where('Status', 'Available');
$query = $CI->db->get();
$result = $query->result();