CODEWITHSUNDEEP
phpmysql
Andrew Brock
Andrew Brock

Reputation: 1364

php sql query removes leading 0s

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

Upvotes: 5

Views: 934

Answers (1)

Max Hudson
Max Hudson

Reputation: 10206

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)');

Upvotes: 6

Related Questions