BIT field in MySQL, using it in PHP 1

[kb2001]

Forgive me if this has been covered, my searches gave me no answer.

I have a database storing info about pictures. I'm building a website using PHP that queries this DB and returns pics along with some info about the pics. The problem starts with the picture aspect. Some pictures are 800x600, some are 600x800. This presents a problem for neatness in my display popup.

For lack of knowledge of a better way, I have created another field in my DB. It is a field of type BIT that hold whether or not a picture is vertical (TRUE=lateral, FALSE=Vertical.

My query works to pull info, and the results are storedmfine in variables, but I am unable to create a working comparison for the bit field. Here is the relevant code

$res = mysqli_query($mysqli,$sql);  //this works fine ans the query returns the proper data

	if ($res) {
	   $newarray = mysqli_fetch_array($res, MYSQLI_ASSOC);
	   $pic_id = $newarray['pic_id'];  //primary key mediumint
	   $year = $newarray['year'];  // smallint
	   $event = $newarray['event']; // varchar
	   $audio = $newarray['audio'];  //bit field- not in use yet
	   $comments = $newarray['comments']; //varchar(255)
	   $dir1 = $newarray['dir1']; //varchar(20) top level directory
	   $subdir = $newarray['subdir'];  //varchar(20) sub-directory
	   $filename = $newarray['filename']; //filename with no extension
	   $pic_align = $newarray['pic_align']; //bit field that is giving me problems
	   

	   [b]if ($pic_align==TRUE) {     //This comaprison is giving me trouble[/b]
	      echo "\n<table>\n\t<tr>\n\t\t<td colspan=2><img src=\"./slides/".$dir1."/".$subdir."/".$filename.".jpg\"></td>";
	      echo "\n\t</tr>\n\t<tr>\n\t\t<td>".$year."</td>\n\t\t<td>".$event."</td>";
	      echo "\n\t</tr>\n\t<tr>\n\t\t<td colspan=2>".$comments."</td>\n\t</tr>";
	   } else {
	      echo "\n<table>\n\t<tr>\n\t\t<td rowspan=4><img src=\"./slides/".$dir1."/".$subdir."/".$filename.".jpg\"></td>";
	      echo "\n\t\t<td>YEAR: ".$year."</td>\n\t</tr>";
	      echo "\n\t<tr>\n\t\t<td>EVENT: ".$event."</td>\n\t</tr>";
	      echo "\n\t<tr>\n\t\t<td>DESCRIPTION:</td>\n\t</tr>";
	      echo "\n\t<tr>\n\t\t<td>".$comments."</td>\n\t</tr>";
	   }
	   echo "\n</table>";
	} else {
	   echo("Connect failed: ". mysqli_connect_error());
	   exit();
	}


	mysqli_free_result($res);
	mysqli_close($mysqli);

The relevant comparison is "if ($pic_align==true)". I have tried $pic_align==true, =="true", =="1", ==1, and have had no success. echo $pic_align returns a solid diamond with a question mark in it when the value is false, nothing when true. "is_string($pic_align)" returns "1", but the string is not "true" or "false"

Any ideas on how to use this field in a PHP comparison? Better yet, any ideas for a better solution than the one I've come up with for determining the aspect of a picture? This site is young in development, and I am more concerned with getting it working than I am with display at this point, but this one is important to know. Using bit fields will come up later when I use my audio field as well.

 

[kb2001]

Almost forgot.

I'm using:

MySQL 5.0.22
Apache 2.0.58
PHP 5.1.4

mysqli functions are being used for my queries.

 

[jpadie]

try

$pic_align = (bool) $newarray['pic_align'];

and then in your comparison do

if ($pic_align !==FALSE)

//or
if ($pic_align)

//or
if ($pic_align === TRUE)

but rather than storing it as a tinyint why not save yourself the grief and store it as a 1-char long varchar. then put a v or an h in as necessay.

failing that, you could always determine the orientation on the fly using the gd library. bit of a processing overhead though.

 

[kb2001]

jpadie-

Thanks for the reply. I already went ahead and made another column, pic_align2 CHAR(1), so I could move forward. I'd still like to use a bit field, though (it's more preferable with 20,000+ photos). The (bool) addition you recommended didn't work. Maybe I'm missing a library or something?

Do you have any thoughts on that? I have another bit field I'll use eventually, and I'd rather not have to make it another CHAR field.

Thanks

 

[jpadie]

the use of (bool) casts the value of the field to a boolean. It cannot "not work". if it does not give the results that you expect then the reality is that the field does not contain the data that you expect.

try running this script and posting the results. it will only output 10 fields and will tell us the values and variable types that mysqli is providing you

<?php
$c=0;
$res = mysqli_query($mysqli,$sql);  //this works fine ans the query returns the proper data
while ($row = mysqli_fetch_assoc($res)){
	echo "<table>";
	foreach ($row as $key=>$val){
		$type=gettype($val);
		echo <<<HTML
<tr>
	<td>$key</td>
	<td>$val</td>
	<td>$type</td>
</tr>

HTML;
	}
	echo "</table><br/>";
	$c++;
	if ($c > 9) break;
}
?>

 

[kb2001]

jpadie-

Thanks for your help with this. I'm at work now, but will try this when I get home and post the results back.

I checked the type of that field yesterday using is_* functions and found it to be a string. The values returned escape me right now, but it was a strange character for true, and I believe nothing for false.

I will let you know what it returns.

Thanks again for your help with this.

 

[jpadie]

BIT type fields are ignored below 5.03. they are treated instead as tinyint(1). this means that when writing data to the database you need to pass the value of 1 (for true) and 0 (for false) to the bit field. anything else ... i don't know what would happen.

have a look at the field in myphpadmin or similar. you might have to write a script to reset the correct tags in each of the rows.

something simple like this would do the job. it returns true if the picture is portrait or false for a vertical.

function isPortrait($file){
 list($width, $height) = getimagesize($file);
 return ($height >= $width);
}

 

[kb2001]

I didn't see your last post until now, but I ran the first example and here a couple of the result sets (one for each value of pic_align)

pic_id 26 string 
year 1952 string 
event Madison, Conn string 
audio  string 
comments  NULL 
dir1 box1 string 
subdir 1C string 
filename BX1_1_C_03 string 
pic_align  string 

pic_id 27 string 
year 1952 string 
event Madison, Conn string 
audio  string 
comments  NULL 
dir1 box1 string 
subdir 1C string 
filename BX1_1_C_04 string 
pic_align 
 string

It returns nothing for a false value, and an unknown character for a true value. Interesting to note also, it sees all values as strings, when pic_id and year are numeric types, and audio and pic_align are BIT. I think you may have it pinned down with the datatype of BIT being ignored. I'll dig into that a bit when I get home and see what I can learn.

I also like the idea of using a function to determine the aspect. I'll give that a try when I get home as well. You'd mentioned "gd" tools. Is that required for the getimagesize function?

Thanks for your help with this, jpadie. It is very much appreciated.

 

[jpadie]

oddly enough, getimagesize() does not need the GD library. i had thought it did but no.

i think the issue is two-fold: first you need to write a string or a digit to the field (rather than an actual boolean), and second that the php mysql binding is returning everything as a string: this is probably normal behaviour: i've never tested it myself.

 

[kb2001]

jpadie-

Just got the time to try this tonight. It works like a charm. I deleted my pic_align columns now and am just using the getImagesize function to discover the aspect. Works perfectly with a little concatenation to get the path/file down.

Also, I changed my audio field from BIT to TINYINT and it now stores a 0 or 1. I put a test echo statement in there

if ($audio) {
  echo "audio=yes";
} else {
  echo "audio=no";
}

It returned the info correctly. I didn't even need the (bool) to change the datatype because a zero for $audio returns false in the above if statement. The type, however, still is a string as far as php is concerned, I checked and found out.

Thanks for helping me with this. It's moved me forward in my site, I learned quite a bit, and the help is much appreciated. Sorry for the late reply, work about destroyed my brain yesterday and I left this site alone last night, and I couldn't get back until after work tonight.