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
– Michael Berkowski, Commented Jul 10, 2012 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
– Khurram Ijaz, Commented Jul 10, 2012 at 13:06
var_dump does show a string of length 1 array 'foo' => string '7' (length=1) — Andrew Brock
– Andrew Brock, Commented Jul 10, 2012 at 13:08
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
– Andrew, Commented Jul 10, 2012 at 13:23
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
– Andrew Brock, Commented Jul 10, 2012 at 13:55

Max Hudson · Accepted Answer · 2012-07-10 13:27:55Z

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, 2012 at 13:27

answered Jul 10, 2012 at 13:06

Max Hudson

10.2k15 gold badges61 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Andrew Brock Over a year ago

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

Max Hudson Over a year ago

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.

Andrew Brock Over a year ago

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 Over a year ago

didn't work, I would guess that by the time I get the string it is too late

Andrew Over a year ago

mysql_query('UPDATE tablename SET rowname = concat("'", rowname'); would throw syntax errors, you need to escape that quote.

|

Collectives™ on Stack Overflow

php sql query removes leading 0s

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related