CODEWITHSUNDEEP
phpmysqlconditional-statements
eozzy
eozzy

Reputation: 68680

default value does not stick in mysql

I have this field IsTrial in MySQL of type bit(1) with NULL set to No and default value of 0

enter image description here

Now this condition:

if(!empty($subscription['IsTrial']) && (bool)$subscription['IsTrial'] == TRUE ) 
{ 
    echo ' (Trial)'; 
}

.. it ALWAYS turns out true, because mysql doesn't actually set the default value of 0, it keeps the field BLANK?? To make the above condition work, I have to set the default value in the MYSQL from 0 to null, but I do not want to do that.

I'm really confused whats going on here, why would the default value 0 be not set and the field remains blank?

To reiterate, the issue is, mysql does NOT save the field with default value 0 when no value is manually specified, it keeps the field BLANK which causing the conditions to fail.

Upvotes: 4

Views: 712

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562330

The field is not blank. It has the binary value 0.

You didn't define the default value to 0, i.e. the character with ASCII code 48. You defined the default value to the binary value 0, which is the nul character in ASCII. When you try to print ASCII nul as a string, there is no visible representation. Likewise a value 1 is binary value 1, or Control-A, which is not a printing character either.

mysql> create table t (isTrial bit(1) not null default 0);

mysql> insert into t () values ();
mysql> insert into t (isTrial) values (DEFAULT);
mysql> insert into t (isTrial) values (0);
mysql> insert into t (isTrial) values (1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t;
+---------+
| isTrial |
+---------+
|         |
|         |
|         |
|        |
+---------+

mysql> pager cat -v
PAGER set to 'cat -v'
mysql> select * from t;
+---------+
| isTrial |
+---------+
|         |
|         |
|         |
| ^A       |
+---------+

But PHP does what most users probably want, which is to map these BIT values to the string values "0" and "1" respectively. Here's some test code (using PHP 5.3.15, MySQL 5.5.30):

$stmt = $dbh->prepare("select isTrial from t");
$result = $stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    var_dump($row);
    if(!empty($row['isTrial']) && (bool)$row['isTrial'] == TRUE ) 
    { 
        echo "isTrial is true\n";
    } else {
        echo "isTrial is false\n";
    }
    echo "\n";
}

Output:

$ php bit.php
array(1) {
  ["isTrial"]=>
  string(1) "0"
}
isTrial is false

array(1) {
  ["isTrial"]=>
  string(1) "0"
}
isTrial is false

array(1) {
  ["isTrial"]=>
  string(1) "0"
}
isTrial is false

array(1) {
  ["isTrial"]=>
  string(1) "1"
}
isTrial is true

Re your comment:

Using BIT(1) should work fine for PHP, but it causes confusion when we look at the data directly in the query tool. If you want to make the data more clear, you can use TINYINT. Another option would be CHAR(1) CHARACTER SET ASCII. Both require 1 byte for storage.

The only advantage of BIT(1) is that it rejects any value besides 0 or 1. But the storage requirement for this data type rounds up to 1 byte anyway.

Upvotes: 6

Related Questions