FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Best way to check if BINARY mysql field is zero?





Peterssidan
What is the best way to check if all the bytes in a mysql BINARY field is 0?

The way I come up with was to check it in php using bin2hex and trim, which feels like a hack.
Code:
$result = mysql_query('SELECT bin_field FROM table');
if ($row = mysql_fetch_array($result))
{
   if (trim(bin2hex($row['bin_field']), '0') === '')
   {
      // Do something
   }
}


Is there a better way to do this from within mysql itself?
badai
i'm not sure what you mean by "all the bytes". yes, binary is fixed length, just like char, but whatever mysql client you use, it will always show value without "meaningless" zero or empty character in case of char. so if you have binary field with 4 field wide, the data stored is 0000, but any mysql client will always just show 0.

to compare just use if ($row['bin_field']) == 0)

i don't see why that wouldn't work.
Peterssidan
I realized I don't need to use bin2hex like I used above but the problem is that the string from mysql contains the raw binary data. If bin_field is a BINARY(10) I can do:
Code:
if (trim($row['bin_field'], "\0") === '')

or
Code:
if ($row['bin_field'] === "\0\0\0\0\0\0\0\0\0\0")


But still, is there a better way?
sonam
Actually if I good see from your script you want to see is it your string empty not zero. In that case you can give a try with empty function.
http://php.net/manual/en/function.empty.php

Code:
if (empty(trim(bin2hex($row['bin_field']))) {
   // the rest of your code
}


Also you can use is not empty function:

Code:
if (!empty(trim(bin2hex($row['bin_field']))) {
   // the rest of your code
}


Sonam
Peterssidan
BINARY has a fixed length so it can't be empty.

What I was hoping for was a way to check this from within the mysql query. I have tried
Code:
SELECT bin_field=0 AS is_zero FROM table
,
Code:
SELECT BIT_COUNT(bin_field)=0 AS is_zero FROM table
and a few other things but it doesn't give the result I want.
manfer
For legth 3

Code:

$result = mysql_query("SELECT BINARY '000' = bin_field FROM table");
// $result = mysql_query("SELECT '303030' = HEX(bin_field) FROM table"); is an alternative solution
if ($row = mysql_fetch_array($result))
{
      // Do something
}


Should work. And only when ALL bytes are char 0 which is what I suppose you want to check.

If on the other hand what you want to check is when the binary field contains and HEX value with all 0s.
Code:

$result = mysql_query("SELECT '000000' = HEX(bin_field) FROM table");
if ($row = mysql_fetch_array($result))
{
      // Do something
}


Both work only and just only respectively when the binary field contains HEX303030 which is '000', or HEX000000 which is the value of the BINARY field when you insert a blank ''.

This means none of those functions will accept as valid any other value that contains any other character like space, 0 and blanks, 0 and spaces, ...

'00 '
'0 0'
' 00'
'0'
'00'

will not be valid.

The selects I show above return 1 or 0 always -or a 3rd value, NULL, if you allow NULL value on the binary field- so you don't have to do any further check in PHP. I just used your example to check just the first row -I suppose you already know you are just checking first row-
Peterssidan
manfer wrote:
If on the other hand what you want to check is when the binary field contains and HEX value with all 0s.
Code:
$result = mysql_query("SELECT '000000' = HEX(bin_field) FROM table");
if ($row = mysql_fetch_array($result))
{
      // Do something
}

Yes, this will work and do what I want, except that we also need to check $row[0] in PHP to know if it is true or false.

I also found a few other ways to do it:
Code:
SELECT bin_field=CAST('\0' AS BINARY(10)) FROM table
SELECT bin_field=REPEAT('\0', 10) FROM table
SELECT TRIM('\0' FROM bin_field)='' FROM table

These have the benefit that we don't need to write out all the zeros, which can be ugly if the BINARY field is like 32 bytes long. The last one is especially good because we don't need to mention the length of the BINARY field at all. I'm not sure which one is more efficient though. I guess this could also be done using regular expressions but I'm not so good at it.

manfer wrote:
I just used your example to check just the first row -I suppose you already know you are just checking first row-

Yes, I know. It's just a stupid example.
sonam
Peterssidan wrote:
BINARY has a fixed length so it can't be empty.

What I was hoping for was a way to check this from within the mysql query. I have tried
Code:
SELECT bin_field=0 AS is_zero FROM table
,
Code:
SELECT BIT_COUNT(bin_field)=0 AS is_zero FROM table
and a few other things but it doesn't give the result I want.


Oppsssss.... Embarassed

Sonam
manfer
Peterssidan wrote:
I guess this could also be done using regular expressions but I'm not so good at it.


Code:

SELECT bin_field RLIKE '^[NUL]*$' FROM table
SELECT HEX(bin_field) RLIKE '^0*$' FROM table
Peterssidan
Code:
SELECT HEX(bin_field) RLIKE '^0*$' FROM table

Nice and small. Smile

Code:
SELECT bin_field RLIKE '^[NUL]*$' FROM table

This one doesn't work. It seems like it returns 1 whenever the first byte is 0. My guess is that RLIKE treats 0 as the end of the string, which makes it unsuitable for binary data.
Related topics
xbox 360
A great website poll....
hello
How to set up a wiki
Which Linux distribution is the best?
See this artificial beach @ Japan
PHP versions
NAVYFIELD a MMO naval tactics game
Who is PHP and MySQL local server program best
NF
can we increase mysql field size more than 255 cahrs
The last foolish PHP/MySQL Question of the Year....
mySQL and serverload
Software development algorithms
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.