Keeping leading zeros column Mysql

Question

I have the following table called pr_collaborator:

--------------------------------
  id  |   name  |   last_name  |
--------------------------------
000015|  John   |    Smith     |
002154|  Maria  |    Sanchez   |
123456|  Fabian |    Sierra    |
SE0012|  Sarah  |    Taylor    |
SE0015|  Conny  |    Huertas   |
--------------------------------

I just made this query:

SELECT * FROM pr_collaborator;

And this is the result:

--------------------------------
  id  |   name  |   last_name  |
--------------------------------
  15  |   John  |    Smith     |
  2154|  Maria  |    Sanchez   |
123456|  Fabian |    Sierra    |
SE0012|  Sarah  |    Taylor    |
SE0015|  Conny  |    Huertas   |
--------------------------------

I want to keep the leading zeros, the id field is VARCHAR(10)

I was trying the following but it does not work;

SELECT CAST(id as CHAR) AS id, name, last_name FROM pr_collaborator;
SELECT CONVERT(id, VARCHAR) AS id, name, last_name FROM pr_collaborator;

I don't want to use LPAD function because I don't know how many characters have the values.

Update

This is the pr_collaborator structure:

CREATE TABLE `pr_collaborator` (
  `id` varchar(10) COLLATE utf8_spanish_ci NOT NULL,
  `name` varchar(80) CHARACTER SET latin1 NOT NULL,
  `last_name` varchar(80) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

This is the query in PHP code:

$sql = "SELECT id, name, last_name FROM pr_collaborator";

$result = $this->db->fetchAll($sql, Phalcon\DB::FETCH_ASSOC);

$this->jsonReturnSuccess($result);

Maybe it could be a problem with PHP and no with my MySQL

I hope that you can help me!.

Nevermind mine duplicated about ZEROFILL didn't notice the VARCHAR datatype atfirst, i guess i need coffee — Raymond Nijland, Aug 16 '19 at 15:38
@RaymondNijland **If** the data type is VARCHAR there is no need for any padding. The zeroes should be there. — forpas, Aug 16 '19 at 15:44
@Fabian Sierra If the data type is VARCHAR then this query should keep the zeroes. Your problem can't be reproduced — forpas, Aug 16 '19 at 15:46
*"If the data type is VARCHAR there is no need for any padding. The zeroes should be there. "* @forpas ok now i really know i need coffee really BAD.. — Raymond Nijland, Aug 16 '19 at 15:46
@RaymondNijland I do not understand, if the type is VARCHAR the result has to give with leading zeros — Fabian Sierra, Aug 16 '19 at 15:49
Fabian @forpas was the one making the comment and he is right that your problem can't be reproduced .. Also see https://www.db-fiddle.com/f/aCvymSvmHSWZ9NZhQL2THc/0 — Raymond Nijland, Aug 16 '19 at 15:50
@Strawberry I update my question with the result of `SHOW CREATE TABLE pr_collaborator` — Fabian Sierra, Aug 16 '19 at 15:50
@FabianSierra this has to be a problem with the tool you are using to execute the query. — forpas, Aug 16 '19 at 15:53
@forpas I execute the query in Mysql Workbench and it works!, but when I put the query in my php code the result give without leading zeros. What do you think? — Fabian Sierra, Aug 16 '19 at 15:55
*" I execute the query in Mysql Workbench and it works!, but when I put the query in my php code the result give without leading zeros. What do you think?"* Post the PHP code if the PHP code is the problem. — Raymond Nijland, Aug 16 '19 at 15:55
@FabianSierra *when I put the query in my php code the result give without leading zeros* this is what you should mention in your question. This is a PHP and not SQL problem. — forpas, Aug 16 '19 at 15:56
@forpas I think you are right, something happens in PHP that remove the leading zeros. — Fabian Sierra, Aug 16 '19 at 16:01
i see Phalcon in the code i assume you are using [Phalcon a PHP framework delivered as a C-extension](https://phalconphp.com/en/)? — Raymond Nijland, Aug 16 '19 at 16:04

score 0 · Answer 1 · answered Aug 16 '19 at 15:42

0

I cannot replicate this finding:

INSERT INTO my_table VALUES (8,NOW(),'00010102');
Query OK, 1 row affected (0.03 sec)

SELECT status FROM my_table WHERE uuid = 8;
+----------+
| status   |
+----------+
| 00010102 |
+----------+

answered Aug 16 '19 at 15:42

Strawberry

33,750
13
40
57

score 0 · Accepted Answer · answered Aug 20 '19 at 19:26

I don't know if it is a good answer, but I had to do this:

I had to put quotes in the column value using QUOTE() function because PHP becomes my values as a integers:

$sql = "SELECT QUOTE(id) AS id, name, last_name FROM pr_collaborator";

$result = $this->db->fetchAll($sql, Phalcon\DB::FETCH_ASSOC);

$this->jsonReturnSuccess($result);

As you can see I can keep leading zeros in the query result because of quotes.

array(
     'id' : '000015',
     'name': 'Jhon',
     'last_name': 'Smith'
),
array(
     'id' : '002154',
     'name': 'Maria',
     'last_name': 'Sanchez'
),
array(
     'id' : '123456',
     'name': 'Fabian',
     'last_name': 'Sierra'
),
array(
     'id' : 'SE0012',
     'name': 'Sarah',
     'last_name': 'Taylor'
),
array(
     'id' : 'SE0015',
     'name': 'Conny',
     'last_name': 'Huertas'
)

Keeping leading zeros column Mysql

2 Answers2