when my query works but when trying to save provider_id I get a differnt number in the database.
The type of the field is INT. It does not work (different numbers) unless I try to change the type of field to a VARCHAR
/* prepare query */
$query = 'INSERT INTO `users`(`first_name`,
`last_name`,
`gender`,
`username`,
`profile_picture`,
`provider`,
`provider_id`,
`provider_username`,
`provider_profile`,
`last_login`,
`created_date`,
`ip_address`)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), INET_ATON(?))';
/* Prepare an insert statement */
$stmt = $mysqli->prepare($query);
if($stmt){
$stmt->bind_param("ssssssisss", $user['first_name'],
$user['last_name'],
$user['gender'],
$user['username'],
$user['link'],
$user['provider'],
$user['id'],
$user['username'],
$user['link'],
$_SERVER['REMOTE_ADDR']);
Correct me if i'm wrong I though it was a better idea to save as an INT
I get strange negative number:
-502711531
The core problem is that there is loss of range during implicit conversions.
When i is used as the mysqli binding hint it effectively does (int)value. In a 32-bit system the Session ID exceeds the maximum size of the PHP integer and overflows: (int)"100007810775829" -> -502711531.
When s is used, then mysqli does not do this conversion, but passes the value off directly to MySQL (as a string). In this case MySQL silently stores the maximum value it can for the field, which is 2147483647 for an INT column.
Thus, using s with BIGINT field would "work" because:
However, I recommend using a CHAR type instead. This is because the Session ID should be treated as an opaque identifier and not as a number - no math operations apply to it. In addition, simply using a CHAR may avoid silent implicit conversions in the future, such as when dealing with drivers which may incorrectly convert a BIGINT to an integer or float PHP value and thus (like this issue) result in loss of range or precision.