php sql query removes leading 0s

Question

I have code which is essentially

$query = mysql_query('SELECT `foo` FROM `Bar` WHERE id=1', $conn)
$result = mysql_fetch_assoc($query)
$val = $map[$result['foo']];

where the type of foo is CHAR(2), and the value for id=1 is 07

But the value returned is just 7, which causes problems when using this as an index to an associative array.

PhpMyAdmin shows the correct value of 07

I get the same result when using mysql_fetch_object too

From comments: result of var_dump($result) is

array
  'foo' => string '7' (length=1)

and var_dump($map) is array '07' => string 'bar' (length=3)

EDIT 2: I have just found an even bigger issue with this: Phone numbers starting with a 0 are also affected. There is no easy way (like str_pad suggested below) to fix this issue

EDIT 3: The server is Windows 7 with xampp 1.7.7 and PHP 5.3.8 The database is InnoDB with latin1_swedish_ci and COMPACT row format

CREATE TABLE `Bar` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `foo` char(2) DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

EDIT 4: SELECT CONCAT("0", foo) FROM Bar WHERE id = 55 returns 07

When you `var_dump($result);` you still see the string value, correct? — Michael Berkowski, Jul 10 '12 at 13:05
something to do with maths leading zeros before number are not value unless you can change the id type to string and store 000005 mayb — Khurram Ijaz, Jul 10 '12 at 13:06
var_dump does show a string of length 1 array 'foo' => string '7' (length=1) — Andrew Brock, Jul 10 '12 at 13:08
@Andrew Brock If your result contains `'foo' => string '7'` then this value is stored in the database. Any values returned from a database are retrieved as strings in PHP. There happens no type conversion and thus the zero wont get stripped. — feeela, Jul 10 '12 at 13:11
@feeela the value I send to the database is 07, and phpmyadmin shows this correctly, and can also show just 7 if I send it without the leading 0. — Andrew Brock, Jul 10 '12 at 13:12
@feeela, that is incorrect. they are converted to 'numbers' if they have valid 'number' values — Max Hudson, Jul 10 '12 at 13:13
@maxhud Maybe we're referring to different documentations: `array mysql_fetch_assoc ( resource $result )` ["Returns an associative array of strings that corresponds to the fetched row"](http://php.net/mysql_fetch_assoc). — feeela, Jul 10 '12 at 13:16
@Andrew Brock Have you tried any of the other `fetch_*` methods? — feeela, Jul 10 '12 at 13:17
@feeela I have also tried mysql_fetch_object with the same result — Andrew Brock, Jul 10 '12 at 13:19
`mysql_fetch_assoc()` will always, always, return strings. Even if the original was an integer or a float. PHP is loosely typed but that doesn't change the fact that this is how `mysql_fetch_assoc()` operates. (Except for NULLs; those are set to PHP's null.) — Andrew, Jul 10 '12 at 13:23
What happens when you `echo (string) $map[$result['foo']];` ? — Kermit, Jul 10 '12 at 13:24
Please show us what MySQL says are the column definitions for these two columns; you say it's `CHAR(2)` but it's starting to appear it may be integers rather than chars. — Andrew, Jul 10 '12 at 13:26
@maxhud That is false in case of database results. The variable assignment in this case isn't performed by PHP but by the underlying C functions. http://gcov.php.net/PHP_5_3/lcov_html/mysql/php_mysql.c.gcov.php — feeela, Jul 10 '12 at 13:27
@njk i get an error because the key '7' does not exist, i have added the output from a var_dump of map to the question — Andrew Brock, Jul 10 '12 at 13:30
@AndrewBrock I've just written simple test script and it works as you want. Maybe there're some problems with OS, PHP version. I use windows 7, PHP 5.3.5, Xampp 1.7.4 — Leri, Jul 10 '12 at 13:32
@PLB PHP 5.3.8, Windows 7, xampp 1.7.7. The database type is InnoDB, latin1_swedish_ci with Compact row format — Andrew Brock, Jul 10 '12 at 13:39
Well, I exported it through phpmyadmin, deleted the table and then imported it, and it works now. It would seem that something whacky happened in mysql — Andrew Brock, Jul 10 '12 at 13:55
Andrew, you should add your own answer and accept it then. Otherwise, this could lead to confusion for future readers. — Marcus Adams, Jul 10 '12 at 14:55

Max Hudson · Accepted Answer · 2012-07-10T13:27:55.707

6

sprintf('%02d', $row['number']);

Alternatively you can also use str_pad:

str_pad($row['number'], 2, '0', STR_PAD_LEFT);

This may also stop PHP's automatic type conversion:

$var = (string) $row['number'];

You could also add a single quote before the first zero, like this: '07

You can update all the values in your table by doing this (so you don't have to change each entry):

mysql_query('UPDATE tablename SET rowname = concat("\'", rowname)');

edited Jul 10 '12 at 13:27

answered Jul 10 '12 at 13:06

Max Hudson

9,961
14
57
107

1

Thanks, but the value may be any 2 characters, or possibly 1 character, but never 1 digit. I was hoping to avoid something like str_pad if i could find the real answer – Andrew Brock Jul 10 '12 at 13:10
alright, let me see if i can find something. you might be stuck with that as PHP automatically converts a db result to a number and ignores 0s prepended to the value of the numbers. – Max Hudson Jul 10 '12 at 13:14
I would have accepted that as the answer without posting here, but phpmyadmin shows it correctly, which is also written in php. I have not gone through the code to see exactly what it is doing, but i assume there is some flag, like PAD_CHAR_TO_FULL_LENGTH, but for numbers – Andrew Brock Jul 10 '12 at 13:17
didn't work, I would guess that by the time I get the string it is too late – Andrew Brock Jul 10 '12 at 13:21
`mysql_query('UPDATE tablename SET rowname = concat("'", rowname');` would throw syntax errors, you need to escape that quote. – Andrew Jul 10 '12 at 13:29
Well, it didn't work as I would have expected: var_dump = `'CONCAT("'", `state`, "'")' => string ''7'' (length=3)`, which would indicate an issue with mysql rather than php – Andrew Brock Jul 10 '12 at 13:36
did you change the table and now names? http://stackoverflow.com/questions/680801/prepending-a-string-to-a-column-value-in-mysql this should definietly be working. if it doesn't, you can try manually prepending table values with "'"s – Max Hudson Jul 10 '12 at 13:39
sorry, yes, the column name I have been testing on is state – Andrew Brock Jul 10 '12 at 13:42
`SELECT CONCAT("0", foo) FROM Bar WHERE id = 55` returns 07. Defiantly an issue with the SQL side of things – Andrew Brock Jul 10 '12 at 13:49
Thanks for your help. I exported it through phpmyadmin, deleted the table and then imported it, and it works now. It would seem that something whacky happened in mysql – Andrew Brock Jul 10 '12 at 13:55

php sql query removes leading 0s

1 Answers1