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...)?
Demo code.
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.
source: How to get numeric types from MySQL using PDO?
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 :))
I'd write small routine to patch the PDO output to suit the requirements, and try to make the least amout of changes to the coding.
$results = pdoFix($SqlResult->fetchAll(PDO::FETCH_BOTH))
function pdoFix($results) {
foreach ($results as &$row) { // note the "&"
$row[0] = sprintf("%'04s",$row[0]); // zerofill '0'
$row['id'] = sprintf("%'04s",$row['id']); // zerofill 'id'
}
unset($row); // break the reference with the last element
return $results;
}
Note: The other answers are just as good, pick one that you are most comfortable with.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With