Reading a Boolean MySQL column

[MichaelHooker]

After many hours of searching in books and on the net, I can't find the answer to this simple question. Maybe I'm using the wrong keywords to search under? "Boolean" just brings up stuff about Boolean searches, and the word doesn't exist in this forum according to the search facility!

I pull out rows of data from my MySQL table in the usual way, ending up with the row I want to work with in array variable $row. The data are the details relating to a photo, shown as a thumbnail (not stored in the db). There are two buttons, for pulling up a small and a large-sized version of the photo respectively, but in some cases no large-sized version exists - the photo isn't clear enough to cope. In those cases the "large" button is enabled only "if (file_exists($path_to_image_file)) {...}". If the file does not exist, the button is greyed out.

This works surprisingly well, but to my mind it's clunky and disk-intensive. The alternative solution I have in mind is a new boolean column in my details table, true if a large image exists, else false. What I can't find out is what exactly ends up in the php array "cell" corresponding to the boolean field, and how to process it.

Would it be as simple as:

if ($row[Boolean_Col]) {
  echo  '<input type=button etc etc
} else {
  echo  '<input disabled type=button etc etc
}

or do I have to look for the word "TRUE" or the letter "T" or something else?

Many thanks for the help.

Michael Hooker

 

[jpadie]

typically mysql will return a 1 or a 0 as a boolean value. I _think_ that the php connector library (or it could be the mysql C library) returns all variables as a string.

so you could compare

if ($row['field'] === '1'){ //true

but php also interprets variables and is loosely typed so you could also simply do

if ($row['field']) { //true

}

 

[MichaelHooker]

My, that was quick. Thank you!

It seems I guessed right, for the "simply" version at least.

May I ask a supplementary question though? What do you get back in the php array from a non-Boolean column which is specifically set to "NULL" - is it different from a column which is just plain empty? I'm sort of guessing that a Boolean column in the table can't be NULL or empty, if it's not set to TRUE it will return FALSE, but I may be wrong.

Thanks again.

Michael Hooker

 

[jpadie]

php receives all row/column data from the mysql client library, as strings. so null would translate to an empty string.

http://www.php.net/manual/en/function.mysql-fetch-assoc.php[/URL]]
Return Values

Returns an associative array of strings that corresponds to the fetched row, or FALSE if there are no more rows

as to the question of whether a binary data type can be null, I don't know the answer. Logically, a boolean cannot have a third state but I am not sure that mysql enforces this. you might get a definitive answer in the mysql forum but in the meantime, I see that the mysql manual reports that the bool datatype is a synonym for tinyint(1): zeros are considered false and non-zero is considered true. the manual does not elaborate on whether a null value is considered non-zero or otherwise. The manual also quotes

http://dev.mysql.com/doc/refman/6.0/en/numeric-type-overview.html[/URL]]
We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.

 

[MichaelHooker]

Thanks again.

so null would translate to an empty string

So in effect you can't tell the difference between a NULL and an empty column. Oh well, at least I won't waste time trying. I'm afraid the manual advice about an "associative array of strings" doesn't mean much to me, without knowing how it associates specific values to specific strings - but I note that the manual entry also says "Note: This function sets NULL fields to PHP NULL value."

as to the question of whether a binary data type can be null, I don't know the answer.

Well, now that I know it's worth trying the boolean column approach, I can stick the new column in my table and see what happens. I'm sure phpMyAdmin will tell me quite firmly if I can't set a NULL!

I'm not asking for advice on this, but I'm reminded of a commercial program I have that runs on Sqlite3. In one column, there is supposed to be a number, indicating height above sea level. But if the data is not available, the system records it as "0", which is of course a valid height, and any attempt to programmatically interpret the data is doomed to embarrassing failure! This is what has made me very wary of empty fields and NULLs.

Thanks again - I'm a lot further forward now.

Michael Hooker

 

[jpadie]

good spot on the NULL thing. if the manual is right on the subject then i was wrong and php will honour a null value from mysql. given that mysql does not have a true boolean function, i suspect you will be able to use a null value in the column too. the mysql forum will doubtless be able to help much better, however.

an associate array of strings means that the the field name will be the array key and the field value, will be the corresponding array value.

sqlite3 is a curious database. personally i'm a big fan but then again the fact that it is _so_ loosely typed means that, as a developer, you must be very careful in your data integrity routines.