CodeIgniter Forums
convert sql - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12)
+--- Thread: convert sql (/showthread.php?tid=80152)

convert sql - Tajar_Dobro - 09-20-2021

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();
}

RE: convert sql - nfaiz - 09-20-2021

(09-20-2021, 04:19 AM)Tajar_Dobro Wrote: 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

RE: convert sql - ikesela - 09-20-2021

yes , you can.

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


RE: convert sql - Tajar_Dobro - 09-20-2021

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?


RE: convert sql - paulkd - 09-21-2021

(09-20-2021, 11:53 PM)Tajar_Dobro Wrote: 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>';
}


RE: convert sql - includebeer - 09-21-2021

(09-21-2021, 12:51 AM)paulkd Wrote: 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.