Geek asks and answers

Php sql query removes leading 0s

I have a code that is essentially

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

where type is foo CHAR(2)and id = 1 is 07

But the return value is 7, which causes problems when using this as an index for an associative array.

PhpMyAdmin shows the correct value 07

I get the same result when using mysql_fetch_object too

From the comments: the result of var_dump ($ result) is

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

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

EDIT 2: I just found an even bigger problem: phone numbers starting with 0 are also affected. There is no easy way (e.g. str_pad suggested below) to fix this problem.

3: - Windows 7 xampp 1.7.7 PHP 5.3.8 - InnoDB latin1_swedish_ci COMPACT

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

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

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

str_pad:

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

PHP:

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

, : '07

, ( ):

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

More articles:


All Articles