Missing 000 while updating MySQL but not when inserting

Question

When I add a new product sold with product code 00072 it saves nice but if I update the product sold and edit the product code to 00073 etc it gets saved as 73 and the 000 are missing.

Tried with INT VARCHAR CHAR and all the same Tried with unsigned zerofill and it just added a lot of 000000000000

The code I am using to update is:

<?php include("police.php"); ?>  
<?php 
$old = mysqli_real_escape_string($database,$_POST['old']);
$new = mysqli_real_escape_string($database,$_POST['new']);
$updating = mysqli_query($database, "UPDATE sales SET code = $new WHERE id = $old");  
?>

What can be wrong?

Currently using VARCHAR 30 latin swedish, when adding new product sale it works fine but not when updating ...

[varchar and you're using `SET code = $new WHERE id = $old`? check for errors; it's obvious](http://php.net/manual/en/mysqli.error.php) — Funk Forty Niner, Aug 10 '15 at 20:27
yes, tried with CHAR too but nothing. Tried with INT and all codes changed to 2147483647 there is a big mess here — nicanica, Aug 10 '15 at 20:29
`int` won't work with `00073` the leading zeros won't register. quote your variables. — Funk Forty Niner, Aug 10 '15 at 20:30

score 0 · Accepted Answer · edited Aug 10 '15 at 20:36

0

Use single quotes to surround the values. Values sent to the database without quotes surrounding them may be interpreted as numbers instead of text.

$updating = mysqli_query($database, "UPDATE sales SET code = '$new' WHERE id = '$old'");

edited Aug 10 '15 at 20:36

Jay Blanchard

34,243
16
77
119

answered Aug 10 '15 at 20:31

Eugen

1,356
12
15

1

Why should the OP "try this"? A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO. – Jay Blanchard Aug 10 '15 at 20:32
You are the man !!!!!!!!!!!!!!!!!!!!!!!!! – nicanica Aug 10 '15 at 20:34
@JayBlanchard He haves final code, i don't have it and for trying needs to write one. And, instead of criticizing me, try to help :-/ – Eugen Aug 10 '15 at 20:34
I was missing the ' ' – nicanica Aug 10 '15 at 20:34
@nicanica you are welcome :-) – Eugen Aug 10 '15 at 20:35
@JayBlanchard ok, i try it in the future... – Eugen Aug 10 '15 at 20:38
*When will they learn Ralph?* @Fred -ii- – Jay Blanchard Aug 10 '15 at 20:39
*Only when we show/tell 'em to Sam* - @JayBlanchard – Funk Forty Niner Aug 10 '15 at 20:41
*"See that explanation @ Eugen? That would be considered a good explanation. – Jay Blanchard"* - that was a good comment, *so I saved it for ya Sam* - @JayBlanchard – Funk Forty Niner Aug 10 '15 at 20:42
Good edit and explanation guys. You saved my week – nicanica Aug 10 '15 at 23:40

Missing 000 while updating MySQL but not when inserting

1 Answers1