MySQl leading zeros get stored but wrong display

Question

I am trying many typed of fields in MySQL to store numbers only, tried with INT, BIGINT with leading zeros, CHAR and VARCHAR to store INVOICE NUMBERS

I need the invoice numbers to be start with 0000000001, I stored it manually in PHPmyadmin

Now I want to display it and I dont get the leading zeros ....

Here is the database

field "folio" CHAR 15 stored I have manually did 0000000001 it displays fine on phpmyadmin

but here is the problem

<?php $maxprod=mysqli_query($datacenter, 
"SELECT * FROM ventas 
WHERE documento = 'boleta' 
ORDER BY id DESC LIMIT 1");
while($lastcode=mysqli_fetch_assoc($maxprod)){?>


<input type="text" value="<?php echo $lastcode['folio']+1?>">
   <?php }?>

the result of the query is 1 just 1 it does not display all other zeros

Any idea why?

Possible duplicate of [Adding a leading zero to some values in column](https://stackoverflow.com/questions/11165104/adding-a-leading-zero-to-some-values-in-column) — Robert, Apr 19 '18 at 16:20
Don't store integers as strings just to get leading zeros on the display side. Use `sprintf('%010d', $value)` or similar. — Alex Howansky, Apr 19 '18 at 16:22
you'll need to add PHP [str_pad](http://php.net/manual/en/function.str-pad.php) in order to preserve the leading zeros. Something like `` — CIRCLE, Apr 19 '18 at 16:24
@AlexHowansky Hm, I interpreted "Use `sprintf('%010d', $value)` or similar" as a code suggestion. Maybe it was just me... — Don't Panic, Apr 19 '18 at 16:24
@Don'tPanic https://media.giphy.com/media/Ow59c0pwTPruU/giphy.gif — Alex Howansky, Apr 19 '18 at 16:35

Don't Panic · Answer 1 · 2018-04-19T16:25:58.227

0

echo $lastcode['folio'] should show you a result with the leading zeroes, but not $lastcode['folio']+1.

As soon as you do that +1, the result is no longer a string. The $lastcode['folio'] variable is converted to a number in order to do the arithmetic operation on it.

The leading zeroes are just formatting and don't need to be stored with the number. If you need an autoincrementing number, just use an autoincrement integer in MySQL, and format the number with leading zeroes when you print it out.

edited Apr 19 '18 at 16:25

answered Apr 19 '18 at 16:20

Don't Panic

41,125
10
61
80

any suggestion? because I need this to autoincrement the last invoice – NICALANICA Apr 19 '18 at 16:23
the display works fine, any sugestion on what type of field should I use? Example CHAR o INT or BIGINT or VARCHAR ? – NICALANICA Apr 19 '18 at 16:32
If it's a number, INT makes sense to me. – Don't Panic Apr 19 '18 at 16:33

score 0 · Accepted Answer · answered Apr 19 '18 at 16:23

0

PHP automatically converts string into number if you are performing any numerical operation on it. But you can keep the order number in integer form and pad it with zeroes when necessary:

str_pad($lastcode['folio']+1, 15, "0", STR_PAD_LEFT);

answered Apr 19 '18 at 16:23

gabonator

391
1
9

the display works fine, any sugestion on what type of field should I use? Example CHAR o INT or BIGINT or VARCHAR ? – NICALANICA Apr 19 '18 at 16:32
What is the expected highest value or order number? from [this documentation](http://www.mysqltutorial.org/mysql-int/) it seems, BIGINT should be fine – gabonator Apr 19 '18 at 16:36

score -2 · Answer 3 · edited Mar 10 '20 at 11:35

-2

This will retrieve the number as a string to display it as text.
Later you can manipulate it on your coding environment.

SELECT CAST(document_number AS CHAR) FROM ...

edited Mar 10 '20 at 11:35

j3ff

5,719
8
38
51

answered Mar 10 '20 at 11:05

JFMoya

93
6

MySQl leading zeros get stored but wrong display

3 Answers3