Mysql not inserting binary data properly

Question

I am working on data compression and for some reason i need only 8 bits. I am converting number by decbin() and then inserting it in the mysql, the mysql column data type BIT width is 8 bit. I used mysql_query("INSERT INTO n (reading) VALUES (b'".$value."')") and tried this one toomysql_query("INSERT INTO n (reading) VALUES (".$value.")"). Before inserting the value is fine but after insertion its not the same value, it changes the value for example before insertion it echo the value 116 then I echo its binary value 1110100 and it insert the value in mysql column is 00110000.

function delta($reading){
    global $flag;
    $delta = $flag - $reading;
    saveDelta(decbin($delta));       
}

here is the other function where it saves the value

function saveDelta($dif) {
  mysql_query("INSERT INTO n (reading) VALUES (".$dif.")");    
}

Could you show some more code? For example, how you (re)read data from the DB. For what I see, you have no mean to retrieve the values in the order they where entered at first... — Sylvain Leroux
– Sylvain Leroux, Commented Aug 1, 2013 at 13:03

Sylvain Leroux · Accepted Answer · 2013-08-01 20:21:01Z

2

The syntax "INSERT INTO n (reading) VALUES (b'".$value."')" should work provided that $value is properly encoded as a string of '0' and '1'.

EDIT: I noticed you didn't provide any "sequence number" while inserting your data. But, please remember that without using a proper ORDER BY clause, you cannot retrieve your bytes the order they where entered at first. Maybe you think you read "116" but MySQL return an other row from the table?

Here are some usage examples, First using the BIT type:

CREATE TABLE b (value BIT(8));
INSERT INTO b VALUES (0),(1), (255);
INSERT INTO b VALUES (b'00000000'),(b'00000001'), (b'11111111');

Please note that when retrieving BIT columns you will obtain signed result (i.e.: storing 255 will read -1).

You could retrieve your data either as signed 10-base integers or binary form (with optional padding):

SELECT value FROM b;
SELECT BIN(value) FROM b;
SELECT LPAD(BIN(value), 8, '0') FROM b;

As of myself, I would prefer the TINYINT UNSIGNED. This is an 8 bit type which support the same syntax for values (either <10-base digit> or b'xxxxxxxx') -- but will accept the UNSIGNED specifier:

CREATE TABLE t (value TINYINT UNSIGNED);
INSERT INTO t VALUES (0),(1),(255);
INSERT INTO t VALUES (b'00000000'),(b'00000001'), (b'11111111');

You could retrieve your data either as unsigned 10-base integers or binary form (with optional padding):

SELECT value FROM t;
SELECT BIN(value) FROM t;
SELECT LPAD(BIN(value), 8, '0') FROM t;

See http://sqlfiddle.com/#!2/4ff44/6 to experiment with both of them.

edited Aug 1, 2013 at 20:21

answered Aug 1, 2013 at 12:41

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Waqas Over a year ago

i made the table CREATE TABLE b (value BIT(8)); inserted values like that INSERT INTO b VALUES (b'00000000'); and like INSERT INTO b VALUES (00000000); and when i try to <br/> SELECT LPAD(BIN(reading)) FROM n; it gives blank result and when i try SELECT BIN(reading) FROM n; or SELECT reading FROM n; it gives me result id #7, id #8, id #9 .....

Waqas Over a year ago

do you know why its doing this ?

Collectives™ on Stack Overflow

Mysql not inserting binary data properly

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related