Pdostatement (Mysql): Inserting Value 0 into a Bit(1) Field Results in 1 Written in Table

PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table

BIT column is a binary type in mysql (though it's documented as numeric type - that's not precisely true) and I advise to avoid it due to problems with client libraries (which PDO issue proves). You will spare yourself a lot of trouble if you modify type of column to TINYINT(1)

TINYINT(1) will of course consume full byte of storage for every row, but according to mysql docs BIT(1) will do as well.

from:
http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html

bit storage requirement is: approximately (M+7)/8 bytes which suggests that BIT(M) column is also byte-aligned.

Also I found this: https://bugs.php.net/bug.php?id=50757

So you could check if following code works as you expect:

$pdo = new PDO("connection string etc") ;
$statement = $pdo->prepare('INSERT INTO `test` (SomeText,TestBool) VALUES (:someText,:testBool)') ;
$statement->bindValue(':someText', "TEST");
$statement->bindValue(':testBool', 0, PDO::PARAM_INT);
$statement->execute();

You may also try with different type hints than PARAM_INT, still even if you make it work I advice to change to TINYINT.

Insert BIT value in MySQL using PDO Prepared Statement

Not near a terminal to check, but I believe you have to type bind it to INT and send it in as an INT, not as "b010101" (or whatever):

$sql='INSERT INTO test(id,data) VALUES(:id,:bit)';
$stmt=db::db()->prepare($sql);
$stmt->bindValue('id', null, PDO::PARAM_NULL);
$stmt->bindValue('bit', (int)$value, PDO::PARAM_INT);
$stmt->execute();

Quick check on Google brought up this similar previous answer.

PHP PDO bindParam() and MySQL BIT

PDO has a bit of a bug where any parameter passed to a query, even when specifically given as PDO::PARAM_INT is treated as a string and enclosed with quotes. READ THIS

The only way to tackle it is to try the following:

$show_contact = (int)$show_contact;
$query->bindValue(':scontact', $show_contact, PDO::PARAM_INT);

How do you use PDO prepared statements with a BIT(1) column?

PDO::ATTR_EMULATE_PREPARES causes PDO to interact with BIT columns slightly differently. If it is set to false, you just insert the value as normal and MySQL will do any conversion necessary behind the scenes:

$stmt = $dbh->prepare("UPDATE tablename SET
    bit_col = ? ");
$stmt->bindValue(1, $_POST['bit_col']);

However, if PDO is emulating the prepared statements, you need to put a b in there somehow to indicate that it is a BIT type. If you put the b in the bound parameter, PDO will escape the single quotes and you will end up with something like 'b\'0\'' being sent to MySQL, which obviously won't work. The b therefore needs to be in the query, not in the bound parameter. Doing this with named parameters produces the "number of bound variables does not match number of tokens" error above because PDO does not recognize strings with a b followed by a : as a named parameter. However PDO does recognize it as a parameter when you use question mark parameter markers, like this:

$stmt = $dbh->prepare("UPDATE tablename SET
    bit_col = b? ");
$stmt->bindValue(1, $_POST['bit_col']);

Since we need to end up with something like b'1' being sent to MySQL, using PDO::PARAM_INT when binding the value will cause the query to fail because it will become UPDATE tablename SET bit_col = b1 (without the quotes around the number) so you must leave the data type out or use PDO::PARAM_STR.

Also note that if emulating prepared statements is disabled, this query will fail with a syntax error, so unfortunately the queries need to be done completely differently depending on whether or not you are emulating prepares or not.

MySQL INSERT inserting BIT to 1 instead of 0

You need to explicitly declare this parameter as Boolean when passing it to PDO.

From the PDO execute documentation :

input_parameters

An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

This is not what you want.

So you would need to change your logic to avoid using the execute(array()) construct and bind each parameter individually, using bindValue(). For boolean :

$insertstmt->bindValue(':col6', false, PDO::PARAM_BOOL);

Since booleans are just TINYINT(1) in MySQL, PDO::PARAM_INT should work fine as well :

$insertstmt->bindValue(':col6', 0, PDO::PARAM_INT);

---

Finally : if this boolean value will always be false, then you may pass it directly to the query, like :

$insertstmt->execute(array($var1, $var2, $var3, $var4, $var5, false));

---

As wisely commented by spencer7593, another option is to do the type casting from within the SQL. For example one could convert the string to a bit value with :

INSERT INTO table1 (column1, column2, column3, column4, column5, column6) 
VALUES (?, ?, ?, ?, ?, IF( ? ='0', b'0', b'1') )

sorting table data based on another table by sum of a value

Yes, you can do it, but you have to add this value as a new column in your select result like this:

select a.id,a.other,
        (select count(*) 
         FROM table2 AS B 
         WHERE b.id=a.id) AS c
FROM table1  AS a
ORDER BY c;

The C value would be the count (or sum) of some values in other table

---

Related Topics