John Davidson

Message:

I have a query that inserts or replaces data in table and it works fine on localhost, but on real host it behaves strange.

The query:

REPLACE INTO sessions (

    user_id, authorization_token, authorization_token_expires, refresh_token, refresh_token_expires

) VALUES (

    :user_id, :authorization_token, :authorization_token_expires, :refresh_token, :refresh_token_expires

);

The problem is when I insert a new row into the table it inserts a row with user_id = 0 and updates it with every insert/replace query. Since this works normal on local, I suppose that something wrong with server/DBMS settigns.

EDIT

Result of SHOW CREATE TABLE sessions

CREATE TABLE `sessions` (

 `user_id` int(11) NOT NULL,

 `authorization_token` varchar(255) NOT NULL,

 `authorization_token_expires` varchar(10) NOT NULL,

 `refresh_token` varchar(255) NOT NULL,

 `refresh_token_expires` varchar(10) NOT NULL,

 PRIMARY KEY (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

EDIT 2

It seems like problem is with pdo->lastInsertId();. I have a function that inserts user in my users table and returns lastInsertId. If I var_dump() this function result looks like string(1) "1". If I transfer result of function to function that inserts row in my sessions table I get strange result I described above. But if I transfer simple string like "1" to function that inserts sessions I get correct behavior. Definetly have no idea what's going on :/

---

My Answer:
It seems like the issue might be related to how the lastInsertId() function is being used in your code.

When you use lastInsertId() after inserting a row into the users table, it might be returning the user_id as a string instead of an integer. This could be causing the unexpected behavior when you try to use this value in the REPLACE INTO query for the sessions table.

To fix this issue, you can try converting the user_id value to an integer before using it in the REPLACE INTO query. You can do this by using intval() function or type casting the value to an integer.

For example:
$user_id = intval($user_id);

Or
$user_id = (int) $user_id;

By ensuring that the user_id value is an integer before using it in the query, you should be able to resolve the unexpected data issue in the sessions table.