2

I want to convert a TINYINT(1) value to string. Let's say if subscription paid is equal to 1 (or true) then the outcome will be "yes" when I retrieve the value from my MySQL database to my .php file. If subscription paid = 0 (or false) the the outcome will be "no".

Is there a way to convert a TINYINT(1) value to a string containing my words of choice?

I am sorry if I am being indirect or if more information about the issue is required

underscore
  • 6,495
  • 6
  • 39
  • 78
user3510657
  • 77
  • 1
  • 8
  • 1
    There is, but why bother? Just convert it after the value has reached PHP, which is easier, more economical, and does not cause you to put part of your business logic into an SQL query (bad for maintenance). – Jon Apr 30 '14 at 15:56

3 Answers3

3

Given that MySQL does not provide any decent mechanism to prevent inserting -1 or 33 into a TINYINT column, I'd simply test against zero/non-zero:

SELECT CASE
    WHEN paid<>0 THEN 'Yes'
    WHEN paid=0 THEN 'No'
    ELSE 'Unknown'
END is_paid
FROM suscription

The question is also tagged as PHP. I think it's cleaner to use native types as much as possible:

if (is_null($row['paid']) {
    $paid = null;
}else{
    $paid = $row['paid']!=0;
}

Getting the right string when displaying should be straightforward.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
2

You can use an IF:

SELECT IF(`column` = 1, 'yes', 'no') FROM `table_name`

Or:

SELECT IF(`column` = 0, 'no', 'yes') FROM `table_name`

Or you can use < or > etc. based on the data.

AbraCadaver
  • 78,200
  • 7
  • 66
  • 87
1

You can do this in PHP:

$number = 1; // or 0 - basically your MySQL variable
$word = $number ? "yes" : "no";

Alternatively, do it in your MySQL query:

SELECT IF(`column_name` = 1, 'yes', 'no') FROM ...
Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592