Add value "001" to mysql database

Question

I am trying to add the value "006" to an MySql row, but it shows up as "6"! any fixes on my code ?

$id = "006";

mysql_query("INSERT INTO Table (id) VALUES ($id) ");

Thanks,

yeah, i think you forgot quotes on values, so it cast to an int. — Somy A, Apr 07 '13 at 21:27
@SomyA : how would I fix this, i really can't see the error. — Yosef Naser, Apr 07 '13 at 21:31
the errors is missing single quotes on values. The values should be "VALUES ('$id')", not "VALUES ($id)" — Somy A, Apr 07 '13 at 21:44
OP: if one of the answers solved your problem, it would be awesome if you'd tick it — d'alar'cop, Apr 08 '13 at 13:03
So there is a simple way to do this with when you create the table... just specify ZEROFILL see this post for more details.. http://stackoverflow.com/questions/13845866/mysql-adding-leading-numbers-to-existing-ids-in-a-column — John Ruddell, Jul 24 '14 at 14:14

score 2 · Answer 1 · answered Apr 07 '13 at 21:23

2

If id is a numeric column such as an integer, 006 is the same as 6 so there isn't a way to explicitly save 006 since its exactly the same as 6.

But if you want to retrieve that integer as a 3-digit, zero-padded string, you could use the LPAD function to pad it with 0's on the left side:

SELECT LPAD(id, 3, '0') as id FROM table;

answered Apr 07 '13 at 21:23

Stuart M

11,458
6
45
59

So, how would i insert that value with the LPAD function ? – Yosef Naser Apr 07 '13 at 21:27
1

You would just continue using the same integer `id` column you have now, assuming your `id` column is currently an integer. You would just be _retrieving_ the data as a zero-padded string. – Stuart M Apr 07 '13 at 21:28
If this is the only solution, then i have to change tons of codes :( – Yosef Naser Apr 07 '13 at 21:33

score 1 · Answer 2 · answered Apr 07 '13 at 21:26

1

Although this is not a safe way:

$id = "006";

mysql_query("INSERT INTO Table (id) VALUES ('$id') ");

I think you forgot some quotes on values, so its cast to int.

answered Apr 07 '13 at 21:26

Somy A

1,682
15
18

score 0 · Answer 3 · answered Apr 07 '13 at 21:23

0

Your field needs to be a VARCHAR for that to work as you desire. You may also pad leading zeroes when you run your queries as an alternative.

answered Apr 07 '13 at 21:23

d'alar'cop

2,357
1
14
18

score 0 · Answer 4 · answered Apr 07 '13 at 21:24

I suppose that's just incorrect value (for the int field type) and cannot be stored at all.
Database intended to store data, means data without any additional formatting, which can be added at select time. That's how this thing works in general.

So, I'd suggest to store a regular int, and pad it with whatever number of zeros you wish at select time

score 0 · Answer 5 · answered Feb 28 '18 at 07:21

0

Try to use query in mysql.

ALTER TABLE table MODIFY COLUMN id INT(3) ZEROFILL;

this may solve your question.

answered Feb 28 '18 at 07:21

Jason.Jan

61
1
8

Add value "001" to mysql database

5 Answers5

Linked