PHP MySQL PDO: how to preserve leading zeros of zerofill int columns

Question

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.

This guy solved it changing to decimal http://stackoverflow.com/questions/6825106/php-mysql-zerofill-is-lost-when-using-mysqli-prepare — Mihai, Jul 29 '14 at 07:59
Probably not relevant, but it itches me: what is the value of `$ResultType` (in `fetch($ResultType)`)? — RandomSeed, Jul 29 '14 at 08:06
@RandomSeed: `$ResultType = PDO::FETCH_BOTH;`, I copy/pasted the code from a custom function I created and forgot to edit this out, thanks for noticing, I don't think it makes any difference though... I'll modify the question — Alex, Jul 29 '14 at 08:14
@sectus: I had the issue with PHP 5.4.26 and tried upgrading to 5.5.15, no change... — Alex, Jul 29 '14 at 08:15
@sectus: Updated my question with create table query, also added decimal(6,0) field which seems to work correctly, but I don't know what impact making that change would have (I have a lot of tables with a lot of *int zerofill fields!). Using MySQL 5.6.16 now, will try updating to 5.6.19 — Alex, Jul 29 '14 at 09:27
@sectus: nevermind, it's not working, I was looking at the wrong output... Sorry! — Alex, Jul 29 '14 at 09:41
Padding zeroes are a visual effect only and should not appear in any business model or business logic. (Numbers should not be compared in any way to their string length. A binary number with string length of 10 might be actually smaller than a decimal with length of 3.) As for a computer `0001` and `1` equals in type **and** content (`===`). — Daniel W., Jul 29 '14 at 09:55
@sectus: Sorry for the confusion, I updated to MySQL 5.6.19, and first thought this fixed it, however I found out I was looking at the wrong out put then. So I still have the problem, as described in my question. — Alex, Jul 29 '14 at 11:31
Can you provide the shortest code necessary to reproduce problem? Create table, data, whole php script. — sectus, Jul 30 '14 at 02:33
@sectus: Are you saying you have the `Id` field zerofilled in both outputs? — Alex, Jul 30 '14 at 08:46
@sectus: OK so why do I have different output than you? Perhaps PHP or MySQL configuration? Could you post your `php.ini` and `my.cnf` files? Thanks! — Alex, Jul 30 '14 at 11:14
i tried to clone your problem on my webserver. with the same table i also get the zerofilled outpout of `id`. only difference i have found so far in my code: `var_dump($SqlResult->fetchAll());` — low_rents, Jul 31 '14 at 18:02
Does this code works: `var_dump((new mysqli('localhost', $db_username, $db_password, $db_name))->query($SqlQuery)->fetch_all());` ? — sectus, Aug 04 '14 at 01:39

score 7 · Answer 1 · edited May 23 '17 at 12:25

7

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 :))

edited May 23 '17 at 12:25

Community

1
1

answered Jul 29 '14 at 09:52

George G

7,443
12
45
59

While this fixed the concrete example in my question, it would require me to over all my code and fix queries everywhere. If I have to do that I'd rather modify the code to work with the real integer values returned from MySQL. Thanks for answering though! – Alex Jul 29 '14 at 12:00
2

Personally, I think LPAD's the way to go - but I don't really understand the point of zerofill – Strawberry Jul 30 '14 at 07:36
1

@Alex You would only need to modify the database handle, not each query so I would expect the stringify fetches option to be the easiest one as you simply set the attribute when you are creating the $MysqlPDO database handler. You will probably also want to change error handling which is a common use case for setAttribute. http://php.net/manual/en/pdo.setattribute.php – edmondscommerce Aug 06 '14 at 09:09
@edmondscommerce: Sorry for taking so long to respond, however in my question I already pointed out that even when setting `PDO::ATTR_STRINGIFY_FETCHES` to `TRUE`, it is returning strings (instead of integers), but these strings are still not zerofilled, so my problem remains. – Alex Aug 19 '14 at 09:48
OK looks like you might have simply hit an issue with PDO that you are going to have to work around. – edmondscommerce Aug 19 '14 at 14:26

Alvin K. · Answer 2 · 2014-08-05T09:39:00.487

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.

PHP MySQL PDO: how to preserve leading zeros of zerofill int columns

2 Answers2

Linked