convert sql

Hi guys how can I convert the value of integer to text, example if it is 0 to display not paid and 1 to display paid
On model
//get chart
public function invoice_chart()
{
$res = $this->db->query("select payment_status as status, count(*) as total from invoice group by status");
return $res->result_array();
}

Hi guys how can I convert the value of integer to text, example if it is 0 to display not paid and 1 to display paid
On model
//get chart
public function invoice_chart()
{
$res = $this->db->query("select payment_status as status, count(*) as total from invoice group by status");
return $res->result_array();
}

SQL Case

yes , you can.

Code:

Select if(payment_status=0,'Not Paid','Paid ') AS status

ikeselayes , you can.

Code:

Select if(payment_status=0,'Not Paid','Paid ') AS status

tnanksss,
but what about if i have more than 2 status?

ikeselayes , you can.

Code:

Select if(payment_status=0,'Not Paid','Paid ') AS status

tnanksss,
but what about if i have more than 2 status?

Hi,

You could also display the statuses using PHP instead of SQL.

Code:

function getPaymentStatus($status)
{
  $statuses = [
    0 => 'Not Paid',
    1 => 'Paid',
    2 => 'Third Status'
  ];
  return isset($statuses[$status]) ? $statuses[$status] : "Unknown Status: {$status}";
}

foreach ($result as $row) {
  echo getPaymentStatus($row['status']) . '<br>';
}

You could also display the statuses using PHP instead of SQL.

Code:

function getPaymentStatus($status)
{
  $statuses = [
    0 => 'Not Paid',
    1 => 'Paid',
    2 => 'Third Status'
  ];
  return isset($statuses[$status]) ? $statuses[$status] : "Unknown Status: {$status}";
}

foreach ($result as $row) {
  echo getPaymentStatus($row['status']) . '<br>';
}

+1 for this solution. In my opinion, it’s always better to do formatting outside of SQL query. This way you can change the text later without breaking your app and you can also support multiple languages.

CodeIgniter 4 tutorials (EN/FR) - https://includebeer.com
/*** NO support in private message - Use the forum! ***/