data changes between updatesql and database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
timoteo
Forum Contributor
Posts: 125
Joined: Sat Jan 08, 2011 6:46 am

data changes between updatesql and database

Post by timoteo »

Hi this one seems too simple but I can't figure it out:

Code: Select all


$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
 $facebook = $_GET['facebookuser'];

  $ver = "/^[\d]*$/";
  
if (
    (isset($_GET["MM_update"]))
    && ($_GET["MM_update"] == "facebook")
    && ($_GET['facebookuser'] !="")
    && (preg_match($ver, $facebook))
   
   
   ){
if ((isset($_GET["MM_update"])) && ($_GET["MM_update"] == "facebook")) {
  $updateSQL1 = "UPDATE businessdetails SET facebookid='$facebook' WHERE userid='$uid' AND recoentryid = '$ent'";
  mysql_select_db($database_recommendingpeople, $recommendingpeople);
  $Result2 = mysql_query($updateSQL1, $recommendingpeople) or die(mysql_error());

}
}
if I echo out $updateSQL1 I get
UPDATE businessdetails SET facebookid='100000074108584' WHERE userid='3' AND recoentryid = '1'
However the number inputted into my database is 2147483647

What's going on? Why won't it update the correct number - I've tried it all ways I can and it always inputs the second number - I have no idea where that number comes from - I just can't get $facebook into my db. Grateful for any help on this - I thought this should be an easy one for me and I would like to know why it isn't.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: data changes between updatesql and database

Post by Weirdan »

your database has the facebookid field defined as signed int. The range of 32bit signed int is from -2147483648 to 2147483647 - and since you're trying to store number that is larger than maximum integer storable in that field the value gets truncated. You need to change field definition to something that allows larger numbers. For external data where you have no control over the exact format I'd recommend one of the string formats - varchar(32) would likely be enough.
timoteo
Forum Contributor
Posts: 125
Joined: Sat Jan 08, 2011 6:46 am

Re: data changes between updatesql and database

Post by timoteo »

Excellent, thankyou so much. I would never have thought of that one - my head hurts from all the head scratching this afternoon - you saved my evening. I'll toast to you. Cheers :drunk:
Post Reply