Converting number using php or sql

Question

I have spent a lot of google time on this and im not sure about the best way I would need to go about it

I have a small web program that adds number into a database with query that runs on selections, to clear certain information out, these query only succeed if the numbers in a certain column are 13 digits long, why I don't know.

So my question is what is the best way to make the digits in the column in sql autoconvert to 13 using either sql or php so if i where to type 1 it would end up in the sql column as 0000000000001 instead of 1

The query I have php running is very simple I don't quite understand why it has to have 13

The code is as follow

<?php include_once "ewcfg9.php" ?>
<?php include_once "adodb5/adodb.inc.php" ?>
<?php include_once "phpfn9.php" ?>
<?php include_once "Productsinfo.php" ?>
<?php include_once "userfn9.php" ?>
<?php
$upc= $_GET['upc'];
sleep(2);
header('Location: Productsedit2.php?upc='.$upc.'');
$serverName = "localhost";
$usr="sl";
$pwd="o.o";
$db="SimpleLabel";

$connectionInfo = array("UID" => $usr, "PWD" => $pwd, "Database" => $db);

$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn === false)

{

print_r( sqlsrv_errors());

}

$sql="Delete from labelprint where upc=$upc";

$query = sqlsrv_query($conn, $sql) or die(sqlsrv_errors());
$result=sqlsrv_fetch_array($query);
print_r($result); 
sqlsrv_close( $conn);

?>

This works but only if the upc=13 digits so can anyone think if there is a way i can make sql make everything 13 digits or make php autofill the field with 13 digits?

What is the type of the column in the DB? Also, you need to be escaping the parameter from $_GET (or better yet, use prepared statements). — Corbin
– Corbin, Commented Aug 28, 2012 at 14:03
the type is varchar(13) also the way it is the way it is, is it runs the query quickly and pass the variable onto the edit page where the information can be edited — quatre432
– quatre432, Commented Aug 28, 2012 at 14:06

Corbin · Accepted Answer · 2012-08-28 15:52:23Z

2

Since the column is a string type, it must be treated as such. (An option would be to use a numeric column and then either pad it in PHP-land, or use zero fill [I think SQL Server has zero fill].)

You should really be using prepared statements, but at the least you need to escape any string you put into an SQL. You might want to look into SQL injection.

Anyway, I would use sprintf:

$padded = sprintf("%013d", (int) $upc);

Then this:

$sql="Delete from labelprint where upc=$upc";

would become

$sql="Delete from labelprint where upc='$padded'";

The single quotes are necessary because it's a string, not a number. The int cast in the sprintf call is so that it's guaranteed that the $padded output will be safe to put into an SQL statement.

You could also use a combination of str_repeat and strlen to pad the string.

edited Aug 28, 2012 at 15:52

answered Aug 28, 2012 at 14:13

Corbin

33.6k7 gold badges73 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Gabe Spradlin Over a year ago

I second the notion of looking into SQL injection. I personally use PDO for all my PHP to DB interactions. Even when they are solely on my LAN because I never know when I might want to push a tool/script up to a public server and I only want to maintain one DAO.

quatre432 Over a year ago

corbin on using your example the query works but does something very odd if there is a item in the db that has 13 digits like it is suppose to and one that does not like for example 1, the 1 will not remove itself but the other will, but if the 13 digit is not in there then the 1 will remove using the query very odd

quatre432 Over a year ago

gabe tried your edit the query now no longer does anything im looking at the syntac to see if i missed something

quatre432 Over a year ago

also something else werid, i went into the db and manually added the 0's in the front of the 1 making it equal of total of 13 and it still would not remove with the query, but the 2 items i scanned in with the gun will very confusing

Corbin Over a year ago

@quatre432 if the field is a varchar, it has no concept of the data being numeric. You will have to make sure it exactly matches. '0001' != '1' for non integral types. I would probably consider storing the numeric data as numeric. The only problem with that though might be if UPCs ever have non-numeric characters in them.

|

BenOfTheNorth · Accepted Answer · 2012-08-28 14:19:34Z

0

If you are using a string instead of an int for the field, you could try this:

$upc = str_pad($upc, 13, "0", STR_PAD_LEFT);

This will padd a string so it is up to 13 chars, using 0 as the 'filler'.

More info: http://php.net/manual/en/function.str-pad.php

answered Aug 28, 2012 at 14:19

BenOfTheNorth

2,8621 gold badge23 silver badges47 bronze badges

1 Comment

quatre432 Over a year ago

Thanks for your response your method does something similar to crobins, it works except for if the upc=1 it wont remove it

Fluffeh · Accepted Answer · 2012-08-28 14:29:05Z

0

If the labelprint field is an int (in which case, why are you storing it as a varchar) you could cast it as follows:

$sql="Delete from labelprint where cast(upc as unsigned)=$upc";

This won't work though if you have funny data in it. eg 0000000000013 will cast as 13 but 000000AB00013 will not cast as 13.

Edit: SQL-Server Int might be 4 Bytes, but that gives you a range of -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) (which is clearly not 4 numbers)

The change of datatype also makes the answer above pretty redundant.

edited Aug 28, 2012 at 14:29

answered Aug 28, 2012 at 14:11

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

8 Comments

quatre432 Over a year ago

im sorry i typoed im storing them as a nvarchar(13) but i will change to a int and try this

Fluffeh Over a year ago

@quatre432 varchar or nvarchar, if you mean it to be an int why are you storing it in a string? Store data the way you mean to use it - even if that means changing it to the right data type before you insert it into the database :)

quatre432 Over a year ago

ok i will try both of your suggestions give me a few minutes to change my code

quatre432 Over a year ago

Um going to sound dumb if i change the design of the coulmn to a int, i cannot use more that 4, the size of the column need to allow 13, why i was using nvarchar(13)

Fluffeh Over a year ago

@quatre432 Change the datatype to a bigint and you shold be sweet.

|

Collectives™ on Stack Overflow

Converting number using php or sql

3 Answers 3

9 Comments

1 Comment

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related