The UPDATE query of Mysql setting a value to a constant always...?

Question

The Update customer query in this code setting phno to a constant 2147483647 always instead of setting to the value submitted... i tried echoeing $phone its correct.. but its not working when im executing query....

<?php
    include 'database.php' ;
    $id=$_POST["customer"];
    $name = $_POST["name"];
    $address = $_POST["address"];
    $phone = $_POST["phno"];
    $sql = "UPDATE `customer`  SET `phno`=$phone, `name`='$name',`address`='$address' WHERE actno=$id";
    if (!mysqli_query($con,$sql))
      {
      die('Error: ' . mysqli_error($con));
      }
    echo "successful";
    mysqli_close($con);
?>

What is the structure of your database? More specifically, what type is `phno` and what is the format of `$phone`? — BLaZuRE, Aug 03 '13 at 13:23
@Akhil It could be longer than 20, i.e. +1 (123) 555-0123 ext 1234 — BLaZuRE, Aug 03 '13 at 13:27
I hope it is just a common sense to increase 20 to whatever you wish to . — Akhil, Aug 03 '13 at 13:27
@Akhil You think it's common sense, but it seems like the field type is an integer, not a varchar, which also seems common sense. Beginners will be beginners and just go with it. — BLaZuRE, Aug 03 '13 at 13:28
I dont think we need to give that level of spoon feeding.. .. Just my view.. — Akhil, Aug 03 '13 at 13:30
hmm i know its very small thing... i gave phno as integer... and $phone is text but atleast it has to change values when i change from my form... — Krishna Kittu, Aug 03 '13 at 13:33
very strange.. although i tried givin a number phno=213412 like that its no working... — Krishna Kittu, Aug 03 '13 at 13:33
and when i execute the same query from phpmyadmin.. its working... — Krishna Kittu, Aug 03 '13 at 13:34
@KrishnaKittu So have you tried doing `echo $sql;` after your `$sql` assignment to make sure it's properly formatted? Have you entered *that* manually? What is the size of your integer? You probably want a varchar anyway, as referenced above. — BLaZuRE, Aug 03 '13 at 13:36
Yes the size i gave in database for phno is no sufficient.. thanx for response @BLaZuRE :) — Krishna Kittu, Aug 03 '13 at 13:38

score 1 · Accepted Answer · edited May 23 '17 at 12:11

You set phno as INTEGER, didn't you? The maximum value of INTEGER is 2,147,483,647, so any number larger than 2,147,483,647 is out-of-range, and will be inserted as 2,147,483,647.

Change the datatype of phno to BIGINT or VARCHAR.

Also, your query is vulnerable to SQL injections, see the link below for more details.

See also:

score 0 · Answer 2 · answered Aug 03 '13 at 13:28

0

I think you are trying to substitute $phone in the string, but it wont work that way, either you split the string or use {}.

answered Aug 03 '13 at 13:28

thegeek

232
2
8

1

You're only halfway right, though I don't think you realize how. You are allowed to use a double-quoted string with a variable inside, and PHP will interpret that variable (like `"$myVar"`. However, for single-quoted strings, you need to use concatenation (like `'Hi ' . $myVar`). For the part you're right about, if `$phone` is a string, then it should be in quotes for MySQL to know it's a string *if there is a space in it*. – BLaZuRE Aug 03 '13 at 13:33

The UPDATE query of Mysql setting a value to a constant always...?

2 Answers2