CODEWITHSUNDEEP
mysqlphp
Laurens Kling
Laurens Kling

Reputation: 2261

Return true when query gives 1

I want to save a true/false in my MySQL database. I'm saving 1/0 in an INT column to do this. When I select it, I get the 1 or 0, but I want it to return true/false to my PHP code, without having to rewrite the database.

Can I use another column type? Should I save it differently?

Update: My question is about not wanting to rewrite the returned value. I'm getting a lot of results from my database. Many of those are true/false, but some are 0s because the price is 0, so I don't want to universally rewrite all 1s and 0s. I also don't want to manually rewrite 10 columns.

Upvotes: 0

Views: 1943

Answers (3)

0x00h
0x00h

Reputation: 790

You don't strictly need to do anything.

PHP does not, and can not, use strongly typed variables. So, if you receive an (int) 1 from your query results, you can simply use this 1 as a boolean without rewriting or changing anything. 

$intOne = (int) 1; //explicitly treat the variable as an integer
var_dump((bool) $intOne); //treat the variable as a boolean

When used in any boolean context, like if ($variable)... then any of these types will be considered to be false by PHP:

  • the boolean FALSE itself
  • the integer 0 (zero)
  • the float 0.0 (zero)
  • the empty string, and the string "0"
  • an array with zero elements
  • an object with zero member variables (PHP 4 only)
  • the special type NULL (including unset variables)
  • SimpleXML objects created from empty tags

... And, most importantly;

Every other value is considered TRUE (including any resource).

Source: PHP Manual > Booleans (english)

So while you can change the storage type of your column in mysql, this won't really change the way PHP handles the variable retrieved from your results at all.

Historically, I've always used a column of type TINYINT(1) to store boolean values in mysql, and as Tom Green points out, recent mysql versions provide a new BIT type, which might be appropriate. To the best of my knowledge, mysql does not currently have an actual boolean data type.

You could just as easily use a column of type VARCHAR(1), though, because PHP can and will use any value as a boolean, thanks to the glorious, majestic, and sometimes maddening, PHP Type Juggling.

If you're trying to use the values you're retrieving for boolean logic, just use the values you receive from mysql like booleans and it will work:

if ($valueFromResults) {
  //The value was something like true

} else {
  //The value was something like false

}

If you're trying to actually echo out the words "true" and "false", then you're probably best served by explicitly echoing the words out yourself, like this;

if ($valueFromResults) {
  echo "true";
} else {
  echo "false";
}

or, in my preferred shorthand;

echo ($valueFromResults) ? "true" : "false" ;

Update You mentioned in a comment that you want to pass the values through json_encode() and use them in javascript.

JavaScript treats any real value, like int 1, as true and any empty value, like int 0, or an empty string, as false. So if your json_encode() output gets used in actual JavaScript, the int values will still work as boolean values. So the integer values from your database should still work as they are.

Just check that your integer results are encoded as integers by PHP and not as strings - they should be encoded correctly by default - because "0" == true in javascript, but 0 == false.

Upvotes: 2

gunwin
gunwin

Reputation: 4832

For a boolean value (true/false), you should use the mySql type bit or tinyint(1).

$boolean = $mysql_data ? true : false;

Upvotes: 1

Tom Green
Tom Green

Reputation: 373

To follow up my comment, here's a more detailed response which also covers the PHP side, although this probably belongs on StackOverflow.

I've always just used tinyint, although you can use bool/boolean which are synonyms for tinyint(1)

However as of MySQL 5.0.3 you can use the bit type:

As of MySQL 5.0.3, the BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.

Next, assuming you have an active column, perhaps to store if a user is active, you could use PHP's automatic type conversion to handle this quite simply.

// Obviously you'd replace this with your database call
$results = [['active' => 1], ['active' => 0]];

foreach($results as $row) {
  if ($row['active'] == true) {
    echo "true\n";
  }
  else {
    echo "false\n";
  }
}

Upvotes: 3

Related Questions