I've hit one more bump in the road of migrating from the old mysql_*() functions to the new PDO class:

I have a the following table:

CREATE TABLE `test` (

`Id` tinyint(4) unsigned zerofill NOT NULL,

`UserName` varchar(4) NOT NULL,

`TestDecimal` decimal(6,0) unsigned zerofill DEFAULT NULL,

PRIMARY KEY (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Note the zerofill'ed Id and TestDecimal fields.

If I run the following code, using the old mysql_*() functions:

$SqlQuery = "SELECT * FROM test";

$Sql_Result = mysql_query($SqlQuery);

var_dump(mysql_fetch_array($Sql_Result));

I get the following output, with the correctly zerofilled Id column:

array (size=6)

0 => string '0001' (length=4)

'Id' => string '0001' (length=4)

1 => string 'alex' (length=4)

'UserName' => string 'alex' (length=4)

2 => string '000002' (length=6)

'TestDecimal' => string '000002' (length=6)

However, if I do the same using PDO, like so:

$SqlQuery = "SELECT * FROM test";

$SqlResult = $MysqlPDO->prepare($SqlQuery);

$SqlResult->execute();

var_dump($SqlResult->fetch(PDO::FETCH_BOTH));

I get this output, with the incorrectly non-zerofilled Id column:

array (size=6)

'Id' => int 1

0 => int 1

'UserName' => string 'alex' (length=4)

1 => string 'alex' (length=4)

'TestDecimal' => string '000002' (length=6)

2 => string '000002' (length=6)

It seems like the PDO class is looking at the column type and returning a matching variable type (integer in this case) in PHP.

After some searching I found out about the PDO::ATTR_STRINGIFY_FETCHES attribute which can be set to force all MYSQL results to be return as strings, while this seems to work (I get a string instead of an int), it still doesn't return the leading zeros:

array (size=6)

'Id' => string '1' (length=1)

0 => string '1' (length=1)

'UserName' => string 'alex' (length=4)

1 => string 'alex' (length=4)

'TestDecimal' => string '000002' (length=6)

2 => string '000002' (length=6)

It seems to work correctly with the decimal(6,0) zerofill field, but not with the tinyint(4) zerofill field...

Is there any way to make this work, or will I have to go over my codebase and find out what breaks with this change (I already identified a couple of things which don't work anymore...)?

解决方案

you may use LPAD?

try this: SELECT *, LPAD( Id, 3, '0') AS zero_Fill_Id FROM test

should change 3 according to int size: maybe 4 for this situation?

Update:

I don't think change int to decimal to be good practice, why I'll not go deeper at this, you can search on that subject.

I think you use mysqlnd driver, what I've found about it (check if enabled How to know if MySQLnd is the active driver?):

Advantages of using mysqlnd for PDO

mysqlnd returns native data types when

using Server-side Prepared Statements,

for example an INT column is returned

as an integer variable not as a

string. That means fewer data

conversions internally.

In this case there is PDO::ATTR_STRINGIFY_FETCHES which in your case should be set to true, also you can give try to PDO::ATTR_EMULATE_PREPARES attribute farther see: PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?

...

$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true);

Hope this helps in any case or anyone :))