Asked  6 Months ago    Answers:  5   Viewed   53 times

I'm using a bit(1) field to store boolean values and writing into the table using PDO prepared statements.

This is the test table:

CREATE TABLE IF NOT EXISTS `test` (
  `SomeText` varchar(255) NOT NULL,
  `TestBool` bit(1) NOT NULL DEFAULT b'0'
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

This is the test code:

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

Running that code gives me a row with value 1 under TestBool. And the same thing using bindValue() and bindParm(). I also tried named placeholders (instead of ?) with the same result.

Then I tried:

$statement = $pdo->prepare('INSERT INTO `test` (SomeText,TestBool) VALUES ("TEST",0)') ;
$statement->execute() ;

Which worked properly (TestBool has value 0). Punching in the SQL directly into MySQL also works.

Note that inserting 1 always works.

So why would placeholders fail to insert the value 0? (and how do I actually do it?)

 Answers

54

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.

Thursday, June 10, 2021
 
sohum
answered 6 Months ago
32

Try $q->rowCount(). Prepared statements will return the number of affected rows via that method.

Friday, July 23, 2021
 
dkcwd
answered 5 Months ago
93

What about this?

SELECT @cnt := count(*) FROM my_table;
UPDATE my_table SET random = FLOOR(@cnt * RAND()) + 1;

Demo: http://sqlfiddle.com/#!2/a896d/4

Thursday, August 19, 2021
 
kjhughes
answered 4 Months ago
32

Yes, because when you call PDO::prepare, the server must create a query plan and meta information for that query, then there is additional overhead to bind the specified parameters when you use PDO::execute. So, to save this overhead and improve performance, you can use PDO::query for queries without parameters.

However, depending on the scale and size of your application, and your server / host configuration (shared / private), you may or may not see any performance increase at all.

Sunday, August 22, 2021
 
nomie
answered 4 Months ago
85

Is this what you are looking for in terms of only entering the information once?

DECLARE @IDList TABLE
(
    ID INT
)

INSERT INTO @IDList ( ID )
VALUES
     (ID1)
    ,(ID2)
    ,(ID3)
    ,(ID4)

PRINT 'Before'
SELECT  GROUPID, ModifiedBy, ModifiedDate
FROM TableXYZ AS T
    INNER JOIN @IDList AS L
        ON T.GroupID = L.ID

BEGIN TRAN


Insert into TableXYZ
 (GROUPID)
SELECT ID
FROM @IDList


UPDATE TableXYZ
SET existingdays = 15
    ,ModifiedBy = @userID
    ,ModifiedDate = @today
FROM TableXYZ AS T
    INNER JOIN @IDList AS L
        ON T.GroupID = L.ID


Set  @RowCount =  @@ROWCOUNT 


PRINT 'After '

SELECT  GROUPID, ModifiedBy, ModifiedDate
FROM TableXYZ AS T
    INNER JOIN @IDList AS L
        ON T.GroupID = L.ID
Thursday, November 4, 2021
 
SorcyCat
answered 1 Month ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share